您好Amieen，

您可以创建一个ado连接并使用此连接导出数据访问。这是vba中的一个简单代码。您使用的是VB6还是VBA？VB6和vba类似。您可以尝试将其转换为VB6代码并根据需要调整sql字符串。

 dbPath =" C：\Users\Documents\DataBaseForTesting。 ACCDB" 
设置xlApp = CreateObject（" Excel.Application"）
设置dbwb = xlApp.Workbooks.Open（" C：\ Users \Desktop \ForTest.xlsm"）
设置dbws = dbwb.worksheets（" Sheet3"）
设置cn = CreateObject（" ADODB.Connection"）
使用cn 
 .Provider =" Microsoft.ACE.OLEDB.12.0 " 
 .Mode = adModeReadWrite 
 .ConnectionString =" Data Source =" &安培; dbPath 
。打开
以
结尾dsh =" [" &安培; dbws.Name& " 

"
sqlStr =" INSERT INTO Test（[Name]，[Contact No]，[Email ID]）"
sqlStr = ssql& "SELECT * FROM [Excel 8.0; HDR = YES; DATABASE =" &安培; dbwb.Name& "。] QUOT; &安培; dsh
cn.Execute sqlStr

有关详细信息，您可以参考：

使用Excel VBA将数据导出到MS Access表

方法从Visual Basic将数据传输到Excel

***的问候，

Terry

Hi There,

Am a beginner in vb6 with a little knowledge of Access. I would like to import some record from an Excel (Spreadsheet) file into a Table in an existing Access Database  every month.

The Excel file serves as the monthly source data consisting of over two hundreds records. One of the table on the database will be updated (Append not Over-write) on a month basis by the source data received in form of Excel. The ID field on the table can accept duplicate value.

Please guide me on how to do that with a code to be used in a click event of a command button placed on a form to perform the action require.

Thanks for your anticipated help.

Amieen Designer

Hi Amieen,

You could create an ado connection and use this connection to export data to access. Here is a simple code in vba. Are you using VB6 or VBA? VB6 and vba are similar. You could try to convert it to VB6 code and adjust the sql string for you needed.

dbPath = "C:\Users\Documents\DataBaseForTesting.accdb"
  Set xlApp = CreateObject("Excel.Application")
  Set dbwb = xlApp.Workbooks.Open("C:\Users\Desktop\ForTest.xlsm")
  Set dbws = dbwb.worksheets("Sheet3")
  Set cn = CreateObject("ADODB.Connection")
  With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Mode = adModeReadWrite
    .ConnectionString = "Data Source=" & dbPath
    .Open
End With
  dsh = "[" & dbws.Name & "

" sqlStr = "INSERT INTO Test ([Name], [Contact No], [Email ID]) " sqlStr = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb.Name & "]." & dsh cn.Execute sqlStr

For more information, you could take references:

Using Excel VBA to export data to MS Access table

Methods for transferring data to Excel from Visual Basic

Best Regards,

Terry