Htykuut

So I have this code in vba which exports data from excel to access. This works fine running the first time and then running the second time "Runtime error 462: The remote server does not exist or is unavailable" shows up.

However if i was to restart excel, then it seems to works fine again the first time. I've tried googling solutions however most of them say set any objects to Nothing and empty variables and it still doesn't work.

Sub AccImport()

Dim acc As New Access.Application

Dim myValue As Variant

myValue = InputBox("Enter table name for access export")

acc.OpenCurrentDatabase "C:UsersUser 1DocumentsDatabase21.accdb"

acc.DoCmd.TransferSpreadsheet _

        TransferType:=acImport, _

        SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _

        TableName:=myValue, _

        Filename:=Application.ActiveWorkbook.FullName, _

        HasFieldNames:=True, _

        Range:="Sheet2$A1:AL104"

        CurrentDb.TableDefs(myValue).Fields("F4").Properties!ColumnWidth = 2500

        CurrentDb.TableDefs(myValue).Fields("F7").Properties!ColumnWidth = 2500



acc.CloseCurrentDatabase

acc.Quit

Set acc = Nothing

myValue = Empty

MsgBox " The data has been exported"



Application.DisplayAlerts = False

Sheets("Sheet2").Select

ActiveWindow.SelectedSheets.Delete

Range("A1").Select

Application.DisplayAlerts = True

End Sub

The below snippet of code is where this error seems to show up when running the second time round. Any help would be much appreciated. Thanks

CurrentDb.TableDefs(myValue).Fields("F4").Properties!ColumnWidth = 2500

I've gone for a code tidy-up approach which should sort the problem.
Examinig each of the objects, from the code break line, in the Immediate Window would also help (e.g. what does "?Currentdb.Name" return)

1) VBA supports New as part of an object declaration, but... (best you research this bit. From memory it's always slower 'cause vba has to re-check if the object has been instantiated.)

Dim acc As Access.Application: Set acc = New Access.Application

2a) Qualify your calls to CurrentDb

acc.CurrentDb.TableDefs(myValue).Fields("F4").Properties!ColumnWidth = 2500

acc.CurrentDb.TableDefs(myValue).Fields("F7").Properties!ColumnWidth = 2500

2b) Even better, explicitly reference each of the objects in the tree [this will help with begugging]

' add Reference to "Microsoft DAO 3.6 Object Library"

Dim db As DAO.Database

Dim tdf As DAO.TableDef



Set db = acc.CurrentDb

db.TableDefs.Refresh    ' CurrentDb should do this, but can't hurt

Set tdf = db.TableDefs(myValue)

With tdf

    .Fields("F4").Properties!ColumnWidth = 2500

    .Fields("F7").Properties!ColumnWidth = 2500

End With



Set tdf = Nothing

Set db = Nothing

I hope that this helps,
GraemeR