第二次在 EXCEL 中运行 VBA 代码时"Run-time error 462: The remote server machine does not exist or is unavailab

Question

我在VBA EXCEL中有一个宏，它打开一个访问文件并运行4个查询，关闭访问文件，最后将一个表从ACCESS加载到EXCEL中。 我的问题是，当我第二次运行它时，我收到运行时错误"462"。 我读到它与变量有关，我需要完全限定变量。你能建议我如何分配我的变量吗？

Sub open_Access()
Application.ScreenUpdating = False
Set xcess = New Access.Application
xcess.Visible = True
folderpath = ActiveWorkbook.Path
objectname2 = folderpath & ""
fileName = "Merge17.accdb"
objectname = objectname2 & fileName
xcess.OpenCurrentDatabase (objectname)
If Not IsNull(DLookup("Name", "MSysObjects", "Name='FinalTable' And Type In (1,4,6)")) Then
'Table Exists
DoCmd.DeleteObject acTable, "FinalTable"
Else
'Table not exists
End If
xcess.DoCmd.OpenQuery "start1a"
xcess.DoCmd.OpenQuery "start1b"
xcess.DoCmd.OpenQuery "start2"
xcess.DoCmd.OpenQuery "start3"
xcess.Quit
Dim TextFileConn As ADODB.Connection
Dim TextFileData As ADODB.Recordset
Dim TextFileField As ADODB.Field
Set TextFileConn = CreateObject("ADODB.Connection")
Set TextFileData = CreateObject("ADODB.Recordset")
TextFileConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objectname
TextFileConn.Open
With TextFileData
.ActiveConnection = TextFileConn
.Source = "FinalTable"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With
Worksheets.Add
ActiveSheet.Name = "DATA from ACCESS"
For Each TextFileField In TextFileData.Fields
ActiveCell.Value = TextFileField.Name
ActiveCell.Offset(0, 1).Select
Next TextFileField
Range("A1").Select
Range("A2").CopyFromRecordset TextFileData
Range("A1").CurrentRegion.EntireColumn.AutoFit
TextFileData.Close
TextFileConn.Close
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox ("Loading Data Complete! Time elasped: " & SecondsElapsed & " seconds")
End Sub

那么我必须如何更改我的变量呢？ 谢谢。

Answer 1

也许您可以尝试将 DLookup 的条件参数中的"Name='更改为"[Name]='。编译器可能认为Name是未定义的变量，而不是域中的引用变量。

我这样说是因为我有类似的代码，但对于DCount函数：If DCount("PropertyID", "tbl_Properties", "[PropertyID] Like '*" & IDSearch & "*'") <> 0 Then而且Microsoft的例子也显示了类似的代码。