VBA错误记录集，.open不起作用

Question

在With语句中，我在“.Open”行上得到一个错误。

我试图确保所有引用都被正确定义。还有什么可能导致问题？ 感谢您的专业知识。

Option Explicit 



Sub ConnectTODB2() 
'early binding 

Dim CustomerTEST As ADODB.Connection 
Dim rs As ADODB.Recordset 
Dim sqlText As String 
Dim ConStrAccess As String 

Set CustomerTEST = New ADODB.Connection 
ConStrAccess = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data   Source=C:\Users\YacubDavid\Desktop\CIC SOD TOOL 2011 GBW.mdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" 



    CustomerTEST.Open ConStrAccess 

    sqlText = "select * FROM 50 GBW People;" 
    Set rs = New ADODB.Recordset 

    rs.Open sqlText, CustomerTEST, adOpenForwardOnly, adLockReadOnly, adCmdText 



'Worksheets.Add 
'Range("A2").CopyFromRecordset rs 




    rs.Close 
    CustomerTEST.Close 
    Set CustomerTEST = Nothing 

末次

Answer 1

更新12/20：把[]周围表名。另一种选择是使用以下方法：

Option Explicit 

Sub ConnectTODB() 
'early binding 

Dim CustomerTEST As ADODB.Connection 
Dim rs As ADODB.Recordset 
Dim r As Range 
Dim ConStrAccess As String 
Dim sqlText   As String 

Set CustomerTEST = New ADODB.Connection 
ConStrAccess = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
         "Data Source=C:\Users\YacubDavid\Desktop\xyz SOD TOOL 20x1 abc.mdb;" & _ 
         "Jet OLEDB:Engine Type=5;" & _ 
         "Persist Security Info=False;" 
CustomerTEST.Open ConStrAccess 

sqlText = "select * from [50 GBW People];" 
Set rs = New ADODB.Recordset 
rs.Open sqlText, CustomerTEST, adOpenForwardOnly, adLockReadOnly, adCmdText 

'Worksheets.Add 
'Range("A2").CopyFromRecordset rs 

rs.Close 
CustomerTEST.Close 
Set CustomerTEST = Nothing 

End Sub