无法从Excel工作表更新Access数据库(Can't update Access database from Excel sheet)

无法从Excel工作表更新Access数据库(Can't update Access database from Excel sheet)

我是Excel VBA的新手，希望根据以下代码从Excel工作表更新Access数据库。 我运行代码时遇到错误：

运行时错误'3001'：参数类型错误，可接受的范围或彼此冲突

我认为问题在于myRecordset.Open命令，但我只是完全陷入困境，无法弄清楚如何修复它。

任何建议将不胜感激。

Private Sub CommandButton1_Click() Update MS Access database Dim oConn As Object Dim myRecordset As Object Dim sConn As String sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents\Standard Form for Rate Requests\Database41.accdb" Set oConn = CreateObject("ADODB.Connection") oConn.Open sConn Set myRecordset = CreateObject("ADODB.RecordSet") myRecordset.Open "MainTable", oConn, adOpenForwardOnly, adLockPessimistic, adCmdTable With myRecordset .AddNew .Fields("ID").value = Worksheets("Sheet1").Range("A5").value .Fields("Order Number").value = Worksheets("Sheet1").Range("A5").value .Fields("Requester").value = Worksheets("Sheet1").Range("B2").value .Fields("Request Type").value = Worksheets("Sheet1").Range("B5").value .Fields("Transport Mode").value = Worksheets("Sheet1").Range("C5").value .Fields("Origin").value = Worksheets("Sheet1").Range("B16").value .Fields("Destination").value = Worksheets("Sheet1").Range("I16").value .Fields("Collection Date").value = Worksheets("Sheet1").Range("D5").value .Fields("Delivery Date").value = Worksheets("Sheet1").Range("E5").value .Fields("Note").value = Worksheets("Sheet1").Range("J12").value .Update .Close End With Set myRecordset = Nothing Set oConn = Nothing End Sub

I'm new to Excel VBA and looking to update Access database from Excel sheet based on the below code. I'm getting an error when I run the code:

run-time error '3001': Arguments are of the wrong type, are of acceptable range, or are in conflict with one another

I think the issue is with myRecordset.Open command but I'm just totally stuck and cannot figure out how to fix it.

Any advice would be much appreciated.

Private Sub CommandButton1_Click() Update MS Access database Dim oConn As Object Dim myRecordset As Object Dim sConn As String sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents\Standard Form for Rate Requests\Database41.accdb" Set oConn = CreateObject("ADODB.Connection") oConn.Open sConn Set myRecordset = CreateObject("ADODB.RecordSet") myRecordset.Open "MainTable", oConn, adOpenForwardOnly, adLockPessimistic, adCmdTable With myRecordset .AddNew .Fields("ID").value = Worksheets("Sheet1").Range("A5").value .Fields("Order Number").value = Worksheets("Sheet1").Range("A5").value .Fields("Requester").value = Worksheets("Sheet1").Range("B2").value .Fields("Request Type").value = Worksheets("Sheet1").Range("B5").value .Fields("Transport Mode").value = Worksheets("Sheet1").Range("C5").value .Fields("Origin").value = Worksheets("Sheet1").Range("B16").value .Fields("Destination").value = Worksheets("Sheet1").Range("I16").value .Fields("Collection Date").value = Worksheets("Sheet1").Range("D5").value .Fields("Delivery Date").value = Worksheets("Sheet1").Range("E5").value .Fields("Note").value = Worksheets("Sheet1").Range("J12").value .Update .Close End With Set myRecordset = Nothing Set oConn = Nothing End Sub

最满意答案

您是否设置了对Microsoft Activex数据对象库的引用？ 1.从工具菜单转到VBE并选择参考.. 2.然后从列表中选择“Microsoft Activex Data Objects Library”。

Did you set a reference to Microsoft Activex Data Objects Library? 1. Go to VBE and Select References.. from Tools Menu. 2. Then select ” Microsoft Activex Data Objects Library” from the list.