Reading Excel Recordset

[yaaara]

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.

[Stewart Ross]

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:

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

[yaaara]

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.

[Stewart Ross]

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

[yaaara]

Hello and thanks again for the reply..

Could you also please elaborate as to what you mean by "transfer the other data separately into another table then combine them in Access itself?"

I'm not sure I understood what you meant... sorry :-(

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

[Stewart Ross]

Assuming that there are fields in common between the two Excel datasets you could use an Access Query to join the two tables together as necessary. I am not convinced that there is any need at all to form a third table from the two combined tables - the norm is to use queries as 'virtual tables' or views of your data which avoids the need to redundantly create a third table to join two existing ones. Even if there was a need for the conjoined table, you could use an Access Maketable query to do that for you.

It will be different if you are combining data according to some local rule or algorithm which does not rely on fields in common (for example, add data from sheet 2 to every third row of sheet 1); you would have to programme that yourself.

-Stewart

[yaaara]

Actually, the data is combined based on some calculations on the tables, which i'm not sure would be possible using the access query.. The entire data is ready to be fetched into the access table (to re-iterate, the final recordset comprises of the excel data and access data).. all is required is a method to fetch the data into the access table in one go.. just like the data may be fetched into excel from access in one go using "CopyFromRecordset" method...

Assuming that there are fields in common between the two Excel datasets you could use an Access Query to join the two tables together as necessary. I am not convinced that there is any need at all to form a third table from the two combined tables - the norm is to use queries as 'virtual tables' or views of your data which avoids the need to redundantly create a third table to join two existing ones. Even if there was a need for the conjoined table, you could use an Access Maketable query to do that for you.

It will be different if you are combining data according to some local rule or algorithm which does not rely on fields in common (for example, add data from sheet 2 to every third row of sheet 1); you would have to programme that yourself.

-Stewart

[Stewart Ross]

...which is what TransferSpreadsheet does, as mentioned above.

CopyfromRecordset takes the results of any existing recordset - from a single table through to a complex query joining and calculating in any number of ways - and transfers the recordset to Excel. TransferSpreadsheet does the same thing in reverse - you specify the worksheet you want to transfer, and it takes the specified worksheet (or range) and imports that into Access in one step. Please look up the TransferSpreadsheet help data as I suggested.

If you need to perform joins on the Excel data you will have to do those first, before you apply Transferspreadsheet.

Otherwise, I can make no further suggestions on this topic. I have covered all the options I know of: link the worksheet directly, import it using TransferSpreadsheet, or use the loop-processing method you are already embarked upon.

-Stewart

[yaaara]

Thanks for all the help... I'll surely check out the options and see what works out for me.. Will surely let you know the results..

Many Thanks.

[NeoPa]

As Stewart says, you can either link the spreadsheet as a table, or import the data into a table.

That done, you can do the processing you need with the Excel side of things "converted" to Access thereby allowing any data manipulation to be done in Access and, of course, produce your results in Access.

Welcome to Bytes!

[amyroell]

I have been able to work with linked spreadsheets in access while opening them up through code and still look at them by putting them in share mode. Works like a charm.

[NeoPa]

Good to hear from you Amy.

Welcome to Bytes!