使用VB6将数据从Excel导入Access数据库 [英] Importing Data from excel using VB6 into Access Database

问题描述

你好，

我是vb6的初学者，对Access有一点了解。我想将Excel（电子表格）文件中的一些记录导入到现有Access数据库中的表中 每月。

Excel文件用作包含超过两百条记录的月度源数据。数据库中的一个表将按月以Excel格式接收的源数据更新（不附加重写）。表格上的ID字段可以
接受重复值。

请指导我如何使用代码来执行该操作，该代码用于放置在命令按钮上的单击事件需要执行操作的表单。

感谢您的预期帮助。

Amieen Designer

解决方案

您好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

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