run time error with excel & access vba

Question

Hi -- I am trying to run this piece of code but I keep getting a run-time error 462  (The remote server machine does not exist or is unavailable).

The code runs perfect the first time I use it, but If I try to run it a second time I get the error noted above.  Can anyone help me?  The code below simply links a worksheet to a table in access, and then it runs two queries tha I have setup in the database.

Sub RunAccess()

Dim objaccess As Access.Application

Dim wsWorking As Worksheet

Dim connectstring As String

Set wsWorking = ThisWorkbook.Worksheets("Working")

Set objaccess = New Access.Application

objaccess.Application.Visible = False

objaccess.OpenCurrentDatabase ThisWorkbook.Path & "\Tracking.mdb"

connectstring = ThisWorkbook.Path & "" & ThisWorkbook.Name

DoCmd.DeleteObject acTable, "WorkingData"

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "WorkingData", connectstring, True, "WorkingData"

DoCmd.OpenQuery "Append_Data"

DoCmd.OpenQuery "Update_Data"

objaccess.CloseCurrentDatabase

objaccess.Application.Quit acQuitSaveNone

Set objaccess = Nothing

End Sub

Answer 1

I'm only guessing that it's an issue when you are opening the database again.  You don't say where the error occurs.   I do something similar and this is the code I use

   On Error Resume Next

   Set AccApp = GetObject(, "Access.Application")

   On Error GoTo 0

   If AccApp Is Nothing Then

      Set AccApp = CreateObject("Access.Application")

      AccApp.Visible = True

   End If

   On Error Resume Next

   AccApp.OpenCurrentDatabase ("SourceFileName")

   On Error GoTo 0

Maybe this will help you.