阅读Excel Recordset [英] Reading Excel Recordset

问题描述

我正在尝试将excel文件中的数据导入我的数据库。


我能够创建一个ado连接然后创建excel数据的记录集..


但是，由于我无法找到任何属性，我无法在访问2003 db中同时导入所有数据启用此..相反，我必须循环数据，然后一次更新表一条记录（这是耗时的）..


这有什么办法可以完成了吗？


谢谢。

Hi,

I am trying to import data from an excel file into my database.

I am able to create an ado connection and then create a recordset of the excel data..

However, I am stuck at importing that data all at once in my access 2003 db as I am not able to find any property to enable this.. Instead, I have to loop through the data and then update the table one record at a time (which is time consuming)..

Is there any way this can be accomplished?

Thanks.

推荐答案

嗨。为什么不直接将Excel表链接为链接表？然后，您就可以像查询任何其他表一样在查询等中使用它。


如果这不是一个选项，那么使用Transferspreadsheet方法导入您的电子表格。查找帮助文件（来自VBA）以获得确切的语法。


帮助文件中的以下摘录显示了如何使用它：

Hi. Why not link the Excel table directly as a linked table? You will then be able to use it in queries and so on like any other table.

If this is not an option then use the Transferspreadsheet method to import your spreadsheet. Look up the help file (from VBA) to get the exact syntax.

The following extract from the helpfile shows how it can be used:

示例

以下示例将Lotus电子表格Newemps.wk3的指定范围内的数据导入Microsoft Access Employees表。它使用电子表格的第一行作为字段名称。


DoCmd.TransferSpreadsheet acImport，3，_

" Employees"，C：\\ \\ Lotus \Newemps.wk3"，True，" A1：G12"

Example
The following example imports the data from the specified range of the Lotus spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses the first row of the spreadsheet as field names.

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

-Stewart

-Stewart

您好，感谢您的快速回复。


transferpreadsheet功能可能无法正常工作，因为我需要读取excel文件，然后将数据与另一个表中的更多字段连接，以达到最终记录集，该记录集将插入到单独的表中。


我们有没有什么方法可以简单地将这个记录集复制到表记录集中并更新表格？


谢谢。

Hi and Thanks for the prompt reply.

The transferspreadsheet functionality may not work as I need to read the excel file and then also join the data with some more fields in another table to reach to a final recordset which is to be inserted into a separate table..

Isn''t there any method by which we can simply copy this recordset into the table recordset and update the table?

Thanks.

对不起，没有。为什么不将其他数据单独转移到另一个表中，然后将它们组合在Access本身？否则，您将不得不满足于在循环中逐行遍历Excel数据。


-Stewart

Sorry, no there is not. Why not just transfer the other data separately into another table then combine them in Access itself? Otherwise you will have to be content with traversing the Excel data row by row in your loop as at present.

-Stewart

这篇关于阅读Excel Recordset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！