Putting an ADO recordset into an Access table

[mike777]

Hello folks.
Wow...just got back to Tek-tips after some time away. The new look is really sharp.
Question: I just finished some experimentation with populating a native MS Access table with the data contained in an ADO recordset. Initially, I ran a loop on the ADO recordset, and imbedded an INSERT statement within it. I updated the status bar (syscmd acsyscmdsetstatus...) with a counter so you can track progress. It was ... ah...quite slow (say, for 3k records).
So I had the "bright idea" to take my SQL, put it in a pass-through query, and then run and INSERT statement (only one this time) that inserted the query into the table. This was about 500 times faster. So this is good.
The question is, though, there are times when it would be really convenient if I could skip the whole set qDf=dBs.createquery("somequery)....qDf.connect="OBDC..."...qdf.SQL="{call pkg...procedure..from...Oracle"...insert into myNativeAccessTable select * from passthroughquery..etcetc...and just take the data out of the ADO recordset and transfer it to a table directly.
I'm one of those guys that tries to stick with code and stay away from all those gui-based object things as much as possible. Don't ask me why..
Anyway, if you have any ideas (I've asked around before and only got deafening silence for an answer...is it a bad question?), I would greatly appreciate your input.
BTW, the reason I need the data in a native Access table is another issue...and this thing is already long.
Thanks.
-Mike Kemp

 

[Elysium]

Are you asking how to add the whole lump of data in one shot without looping?

 

[mike777]

Elysium,
Yes! How did you make my question so short?
Thanks.
-Mike

 

[Elysium]

Mike,

Since it seems like you have a good handle on using ADO in the first place, let me know if you need more detail than what I am putting below. I would create a string variable to hold the SQL syntax that inserts records:

sSQL = "INSERT INTO TABLE_A VALUES( .... field names separated by commas .... ) SELECT * FROM TABLE_B WHERE <CRITERIA>"

Then take your connection object and issue the 'EXECUTE' command using the sSQL as its argument. That will add the entire contents of your source into your destination without looping. However, if your criteria needs to change, then some sort of loop is inevitable.

Randy

 

[mike777]

Elysium,
Thanks for the help.
The issue is that I need to take data from a remote (Oracle, Interbase, etc.) database and load it temporarily to a native MS Access table to be used in a report.
So, there is no "TABLE_B", there's just a table in memory, identified by an ADO recordset variable.
Thanks again.
-Mike

 

[LittleSmudge]

The issue is that I need to take data from a remote (Oracle, Interbase, etc.) database and load it temporarily to a native MS Access table to be used in a report.
So, there is no "TABLE_B", there's just a table in memory, identified by an ADO recordset variable.

So can you use the ADO Recordset itself as the recordsource for the report ?


By that I mean:-
In general it IS possible to use an ADO recordset as the RecordSource for a Form or Report.

Is it appropriate in your your application to do this ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[mike777]

LittleSmudge,
You present an interesting idea that I would like to pursue. I suppose I could do what you suggest. Isn't this true only in an Access Project, though, and not an Access Application ( i.e., an ADP vs. and MDB, I think I have those terms and abbreviations correct). If so, I haven't found a way to use the ADP with Oracle; I believe it's setup to be used only with SQL Server.
What do you think?
Thanks again.
-Mike

 

[LittleSmudge]

I'm not sure about Projects.

All my work is using A2k .mdbs as user interfaces for a MySQL back end.

Therefore I often open up ADODB.Recordsets via ODBC and then, when I want a continuous form to show multiple records, I just bind the form's RecordSource to the ADORecordset.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[mike777]

'Allo Lihul Smudge (that's an example of an American trying to write with a British accent),
If my memory serves me, you can bind a form to an ADO recordset with either an MDB or an ADP. A report, however, can only use an ADO recordset if it is in an ADP.
I'll try to confirm that with some documentation and get back to you.
Thanks for the help.
-Mike

 

[LittleSmudge]

Mike,

Your comments got me digging back in the notes too.

In a .mdb you can bind an ADODB.Recordset to a Form.

However, Reports do not have the .Recordset property that was introduced into the Forms property set in A2k so you do need to use a slightly different approach.

Report.RecordSource = sqlString



'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.