我是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 SubI'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.
更多推荐
发布评论