ADO 执行 - 编译错误：未定义用户定义类型

发布于 2024-10-03 08:14:17 字数 936 浏览 3 评论 0原文

我有以下从 Excel 到访问的 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("Jackie").Value = CurrentSheet.Range("R" & x).Value

.Update
End With
x = x + 1
Loop

NewSet.Close
database.Close

End Sub

原文

I have the following ado connection from excel to access but it does not work, am getting the error above. Any ideas?

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("Jackie").Value = CurrentSheet.Range("R" & x).Value

.Update
End With
x = x + 1
Loop

NewSet.Close
database.Close

End Sub

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一杯敬自由 2024-10-10 08:14:17

您引用了 adodb 库吗？（在 VBE 中，选择“工具”、“参考”）

回复收藏 0 原文

本宫微胖 2024-10-10 08:14:17

我相信问题可能出在这些类型上：

adOpenKeyset, adLockOptimistic, adCmdTable

尝试按如下方式定义它们：

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

编辑后：

抱歉，这是针对 JScrip 方言的，但我确信您可以将其更改为 VBScript。 :)

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. :)

回复收藏 0 原文

~没有更多了~