Excel VBA使用ADO将数据附加到Access表 [英] Excel VBA to append data to Access Table with ADO

问题描述

尝试使用Excel VBA将数据从Excel附加到已经存在的Access表时，我遇到了一些严重麻烦.

I am having some serious trouble trying to append data to an already existing Access table from Excel using Excel VBA.

我在Excel(活动工作表)的工作表中有数据，并且每次我从excel触发此宏时，我都希望转移A4-BL4行的内容并将其附加到Access中名为Table1的表中.

I have data in a worksheet in Excel(active worksheet) and I would like to transfer the contents of the row A4-BL4 and append it to a Table in Access called Table1 each time I trigger this macro from excel.

我正在尝试使用ADO和一个记录集来做到这一点，但我真的不知道我在做什么:)

I am trying to do this with ADO and a recordset, but dont really know what I am doing :)

我不想删除访问表中的任何内容，但基本上只是将新行插入其中而不覆盖或删除任何内容.

I do not want to delete any of the contents in the access table, but basically just insert the new row into it without overriding or deleting anything.

以下是我的代码，它当前给我一个错误:类型错误的参数，超出可接受范围或彼此冲突" 在行上:

My code is the following, it currently gives me an error "Arguements of the wrong type, are out of acceptable range,or are in conflict with one another" on the line :

 rs.Open qry, cn, adOpenKeyset

我几乎可以肯定我的做法是错误的，感谢您的帮助. 我对使用Access很陌生，因此我的代码可能会带来很好的笑声:)

I am almost certain I am going the wrong way about doing this, I appreciate any help thank you. I am very new to working with Access so my code will probably provide a good laugh :)

这绝对是至关重要的，这必须从excel中触发而不是访问

Sub EXPORT()

Application.ScreenUpdating = False


Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = New ADODB.Connection

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\yilmadu001\Desktop\Database.accdb;"


cn.Open (strcon)

qry = "SELECT * FROM Table1"
rs.Open qry, cn, adOpenKeyset



 rs.AddNew
 rs.Fields("Date") = Range("A4").Value
 rs.Fields("Order Number") = Range("B4").Value
 rs.Fields("Product Code") = Range("C4").Value
 rs.Fields("E-Number") = Range("D4").Value
 rs.Fields("size") = Range("E4").Value
 rs.Fields("type") = Range("F4").Value
 rs.Fields("type2") = Range("G4").Value

 rs.Fields("Voltage") = Range("H4").Value
 rs.Fields("Compound") = Range("I4").Value
 rs.Fields("Number") = Range("J4").Value
 rs.Fields("Tag Number") = Range("K4").Value
 rs.Fields("Quantity") = Range("L4").Value
 rs.Fields("zero") = Range("M4").Value
 rs.Fields("net") = Range("N4").Value

 rs.Fields("Actual net") = Range("O4").Value
 rs.Fields("min") = Range("P4").Value
 rs.Fields("Actual min") = Range("Q4").Value
 rs.Fields("max1") = Range("R4").Value
 rs.Fields("max2") = Range("S4").Value
 rs.Fields("Top") = Range("T4").Value
 rs.Fields("2") = Range("U4").Value

 rs.Fields("side") = Range("V4").Value
 rs.Fields("5") = Range("W4").Value
 rs.Fields("Bottom") = Range("X4").Value
 rs.Fields("8") = Range("Y4").Value
 rs.Fields("side2") = Range("Z4").Value
 rs.Fields("10") = Range("AA4").Value
 rs.Fields("set1") = Range("AB4").Value

 rs.Fields("set2") = Range("AC4").Value
 rs.Fields("set3") = Range("AD4").Value
 rs.Fields("Usage1") = Range("AE4").Value
 rs.Fields("Usage2") = Range("AF4").Value
 rs.Fields("Main (%)") = Range("AG4").Value
 rs.Fields("A (%)") = Range("AH4").Value
 rs.Fields("ratio (%)") = Range("AI4").Value

 rs.Fields("set3") = Range("AJ4").Value
 rs.Fields("set4") = Range("AK4").Value
 rs.Fields("Core ") = Range("AL4").Value
 rs.Fields("Tip1") = Range("AM4").Value
 rs.Fields("Tip2") = Range("AN4").Value
 rs.Fields("Tip3") = Range("AO4").Value
 rs.Fields("set5") = Range("AP4").Value

 rs.Fields("speed") = Range("AQ4").Value
 rs.Fields("speed2") = Range("AR4").Value
 rs.Fields("speed3") = Range("AS4").Value
 rs.Fields("speed4") = Range("AT4").Value
 rs.Fields("speed5") = Range("AU4").Value
 rs.Fields("speed6") = Range("AV4").Value
 rs.Fields("speed7") = Range("AW4").Value

 rs.Fields("setting") = Range("AX4").Value
 rs.Fields("Selected") = Range("AY4").Value
 rs.Fields("S/U1") = Range("AZ4").Value
 rs.Fields("S/U2") = Range("BA4").Value
 rs.Fields("S/U3") = Range("BB4").Value


rs.Fields("ON/OFF") = Range("BC4").Value
rs.Fields("YES/NO") = Range("BD4").Value
rs.Fields("NOTES") = Range("BE4").Value
rs.Fields("Size1") = Range("BF4").Value
rs.Fields("Size2") = Range("BG4").Value
rs.Fields("Size3") = Range("BH4").Value
rs.Fields("Temp1") = Range("BI4").Value

 rs.Fields("Temp2") = Range("BJ4").Value
 rs.Fields("Temp3") = Range("BK4").Value
 rs.Fields("Temp4") = Range("BL4").Value


rs.Update

rs.Close
db.Close

Application.ScreenUpdating = True

End Sub

推荐答案

您需要指定记录集可更新:

You need to specify your recordset to be update-able:

rs.Open qry, cn, adOpenKeyset, adLockOptimistic

由于查询只是获取所有记录，因此可以使用adCmdTable选项，但是您只需要传递表名:

since your query is simply getting all of the records you can use adCmdTable option, but you have to pass the name of the table only:

rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable

这篇关于Excel VBA使用ADO将数据附加到Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！