系统错误地将数据从Excel传输到ACCESS

我有一个问题,一些代码从EXCEL传输数据到ACCESS.以下是代码。

 Sub Export_Data()
    Dim cnn As ADODB.Connection 'dim the ADO collection class
    Dim rst As ADODB.Recordset 'dim the ADO recordset class
    Dim dbPath
    Dim x As Long, i As Long
    Dim nextrow As Long

    'add error handling
    'On Error GoTo errHandler:

    'Variables for file path and last row of data
    dbPath = ActiveSheet.Range("T2").Value
    nextrow = Cells(Rows.Count, 1).End(xlUp).Row

    'Initialise the collection class variable
    Set cnn = New ADODB.Connection

    'Check for data
    If Sheet3.Range("B4").Value = "" Then
    MsgBox " Add the data that you want tot send to MS Access"
    Exit Sub
    End If

    'Connection class is equipped with a —method— named Open
    '—-4 aguments—- ConnectionString, UserID, Password, Options
    'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    'two primary providers used in ADO SQLOLEDB —-Microsoft.JET.OLEDB.4.0 —-Microsoft.ACE.OLEDB.12.0
    'OLE stands for Object Linking and Embedding, Database

    'ADO library is equipped with a class named Recordset
    Set rst = New ADODB.Recordset 'assign memory to the recordset

    'ConnectionString Open '—-5 aguments—-
    'Source, ActiveConnection, CursorType, LockType, Options
    rst.Open Source:="Transactions", ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
    Options:=adCmdTable

    'you now have the recordset object
    'add the values to it
    For x = 4 To nextrow
    rst.AddNew
    For i = 2 = 1 To 21
    rst(Cells(1, i).Value) = Cells(x, i).Value
    Next i
    rst.Update
    Next x

    'close the recordset
    rst.Close
    ' Close the connection
    cnn.Close
    'clear memory
    Set rst = Nothing
    Set cnn = Nothing

    'communicate with the user
    MsgBox " The data has been successfully sent to the access database"

    'Update the sheet
    Application.ScreenUpdating = True

    'Clear the data
    Sheet3.Range("A4:G10000").ClearContents
    On Error GoTo 0
    Exit Sub

    'clear memory
    Set rst = Nothing
    Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
 End Sub

我得到这个错误……

SystemError &H80040E4D -2147217843。

出了什么问题? 我只能说,它似乎发现难以从EXCEL中提取数据或将其放入ACCESS中。

解决方案:

我在使用一个启用了宏的Excel文件时,遇到了同样的错误。

SystemError &H80040E4D -2147217843

对我来说,执行VBA文件的用户没有访问数据库的权限。

你可以检查你的用户在服务器(和数据库)上的权限。这对我的情况有帮助。

最好的

j

给TA打赏
共{{data.count}}人
人已打赏
未分类

手机语言改变时,主屏幕快捷方式的应用标题不会改变。

2022-9-9 17:36:20

未分类

未捕获的引用错误:在HTMLButtonElement.onclick处没有定义变量。

2022-9-9 17:47:16

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索