Insert ADO recordset contents into table

[DatabaseDude]

A custom object returns an ADO recordset to Access. I'd like the contents to be inserted into a table as quickly and efficiently as possible.

I know that this can be accomplished by looping thru the records, but I was curious whether there was a more efficient operation.

I'm particularly interested in any solution that could be as flexible as possible, in the event that the object design is updated & a recordset containing additional or differently named fields is then returned.

Thanks in advance!

 

[Zion7]

What statement are you using to open the recordset with?

rec.open "SELECT * FROM ...WHERE...", CurrentProj....
rec.Open "tblCountry", CurrentPro...

Maybe you can use TransferDatabase() (look it up)

Or an SQL Execute command


Dim strSQL As String, strResult As String
Dim strExtDBase As String

strExtDBase = "C:\Documents and Settings\Dan\My Documents\" & _
"BackUp\DBase\AddIns\AddIn.mdb"

Select Case SQL
Case 0,1,2,3
Case 3
'adds record to external DBase
strSQL = "INSERT INTO Table1(pkOID) " & _
"IN '" & strExtDBase & "' " & _
"VALUES('opoiuyt')"
Case 4
'creates new table, in exterior DBase _
from local table, with all data
strSQL = "SELECT * " & _
"INTO tblNewCustomers " & _
"IN '" & strExtDBase & "' " & _
"From tblCountries"
Case 5
'gets recordset from external DBase
strSQL = "SELECT * " & _
"From tblSupplier " & _
"IN '" & strExtDBase & "' "
Case 6
'creates new table, in local DBase _
from exterior table, with all data
strSQL = "SELECT * INTO tblLocal FROM tblSupplier IN '" & strExtDBase & "' "
End Select

 

[DatabaseDude]

Code to create the initial recordset that is returned by the custom object:

Dim obj as New CustomObject
Dim rst as New ADODB.Recordset
Dim strID as String

strID = "1" ' Just an example

obj.Retrieve strID, rst

This returned recordset would then -- in this instance -- be appended to a Jet table. (On other occasions, the recordset will be used in other manners. This will not be its sole purpose.)

I'm aware of the ways to do this by doing a simple append query etc, via referring to source and destination tables by name. However, to keep the data retrieval and update processes all within the class module, those steps wouldn't immediately work here I don't think.

Does that make sense? (still on my first cup of coffee, so I may inadvertently leave something out)

Thanks!