vba ADO 执行 - 编译错误：未定义用户定义的类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4225931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ADO Execution - compile error: User-defined type not defined
提问by Carlos
I have the following ado connection from excel to access but it does not work, am getting the error above. Any ideas?
我有以下从 excel 到 access 的 ado 连接，但它不起作用，出现上述错误。有任何想法吗？
Sub ADO_to_access()
Dim database As New ADODB.Connection // ERROR HERE
Dim connectionstring As String
Dim NewSet As Recordset
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Set objaccess = Nothing
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Users\Carlos\Desktop\VBA - CW - Database.mdb;"
database.Open connectionstring
' ************* MEN
Set NewSet = New ADODB.Recordset
NewSet.Open "Mens_Dept_Data", database, adOpenKeyset, adLockOptimistic, adCmdTable
x = 6
Do While Len(Range("P" & x).Formula) > 0
With NewSet
.AddNew
.Fields("Irina").Value = CurrentSheet.Range("P" & x).Value
.Fields("Thomas").Value = CurrentSheet.Range("Q" & x).Value
.Fields("Hymanie").Value = CurrentSheet.Range("R" & x).Value
.Update
End With
x = x + 1
Loop
NewSet.Close
database.Close
End Sub
回答by Patrick Honorez
Did you reference the adodb library ? (From VBE, select Tools, References)
您是否参考了 adodb 库？（从 VBE，选择工具，参考）
回答by Wodzu
I belive that the problem might be in those types:
我相信问题可能出在这些类型上：
adOpenKeyset, adLockOptimistic, adCmdTable
Try to define them as follows:
尝试将它们定义如下：
var adOpenForwardOnly = 0, adOpenKeyset = 1, adOpenDynamic = 2, adOpenStatic = 3; //CursorType Values
var adLockReadOnly = 1, adLockPessimistic = 2, adLockOptimistic = 3, adLockBatchOptimistic = 4; //LockTypeEnum Values
var adStateClosed = 0, adStateOpen = 1, adStateConnecting = 2, adStateExecuting = 4; //ObjectStateEnum Values
var adUseServer = 2, adUseClient = 3 //CursorLocationEnum Values
var adCmdTable = 2 //CommandTypeEnum Values
After Edit:
编辑后：
Sorry this is for the JScrip dialect, but I am sure that you can chang it to VBScript. :)
抱歉，这是针对 JScrip 方言的，但我相信您可以将其更改为 VBScript。:)
