将数据表单Excel插入Access 2010 [英] Insert data form Excel to Access 2010
本文介绍了将数据表单Excel插入Access 2010的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找一种将Excel中的数据插入Access 2010中的解决方案.Excel中的数据采用表格形式.填写表格后,Excel中的VBA会将数据作为新记录插入到Access表中.
I am looking for a solution to inserting data from Excel into Access 2010. The Data in Excel are in a form. After filling in the form, VBA in Excel will insert data to the Access table as a new record.
- 我不想在Access中使用功能-从Excel导入.
- 我不想在Excel-对象表中存储数据.
我查看了几个主题,但没有找到我想要的主题.有人可以帮我吗?
I looked at several topics, but none were what I was looking for. Could somebody help me with this?
推荐答案
您可以参考 https://stackoverflow.com/a /61950934/12289278
Sub ImportJEData()
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
Dim var
Dim PayIDnxtRow As Long
'add error handling
On Error GoTo errHandler:
'Variables for file path and last row of data
dbPath = Sheets("Update Version").Range("b1").Value
Set var = Sheets("JE FORM").Range("F14")
PayIDnxtRow = Sheets("MAX").Range("c1").Value
'Initialise the collection class variable
Set cnn = New ADODB.Connection
'Create the ADODB recordset object.
'Set rst = New ADODB.Recordset 'assign memory to the recordset
'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
Do
On Error Resume Next 'reset Err.obj.
'Get the Max ID +1
Set rst = Nothing
Set rst = New ADODB.Recordset 'assign memory to the recordset
SQL = "SELECT Max(ApNumber)+1 FROM PayVoucherID "
rst.Open SQL, cnn
'Check if the recordset is empty.
'Copy Recordset to the Temporary Cell
Sheets("MAX").Range("A2").CopyFromRecordset rst
'Insert the Data to Database And Check If no Errors
Sql2 = "INSERT INTO PayVoucherID(ApNumber)Values('" & Sheets("MAX").Range("A2") & "') "
cnn.Execute Sql2
Loop Until (Err.Number = 0)
xlFilepath = Application.ThisWorkbook.FullName
SSql = "INSERT INTO PaypaymentID(Apnumber) " & _
"SELECT * FROM [Excel 12.0 Macro;HDR=YES;DATABASE=" & xlFilepath & "].[MAX$G1:G15000] where APNumber > 1"
cnn.Execute SSql
Set rst = Nothing
Set rst = New ADODB.Recordset 'assign memory to the recordset
SQL = "Select PayID From PayPaymentID where APNumber = " & _
Sheets("LEDGERTEMPFORM").Range("B8") & " order by PayID "
rst.Open SQL, cnn
Sheets("PaySeries").Range("B2").CopyFromRecordset rst
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
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub
这篇关于将数据表单Excel插入Access 2010的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文