Copy ADO rs to new Access table

[carry1]

does anyone know how to copy adodb.recordset in memory to a table in msaccess?

in a way similar to dumping one to a spreadsheet like - range.copyfromrecordset(rs)

 

[CCLINT]

see my post in thread222-371417 [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Sorry, the last post was meant for another thread.

To do what you want try and copy the record to a string:

strRecord = recordset.GetString

(you can even copy the string to the clipboard if you want)
Then use either a new recordset object on the other table, and use the AddNew method to add the values.

You can also use the VB Split function to change the string to an array and then use the array in the AddNew method.

Or, you can use an INSERT INTO Action query (with the Execute method of the Connection or Command object) on the other table, setting the field values to the values in the array or just to the actual rs.Field(0).Value values. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[carry1]

okay, can you show me an example of executing the INSERT INTO command where the FROM is the array/ or recordset values?

 

[CCLINT]

Just do a Keyword search in tek-tips in the forums "Visual Basic(Microsoft) Databases" or "Visual Basic(Microsoft) Version 5 & 6" using the exact phrase "INSERT INTO".
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[carry1]

the <b>insert into</b> works fine when the source is <u>from</u> another table but i'm trying to dump an ado recordset(disconnected) into a table in msaccess.

 

[CCLINT]

>the insert into works fine when the source is from another table ...

Loop through the records in the recordset, and for each record, use the INSERT INTO and VALUES to assign each field in the destination table to the values of the disconnected recordset:

.Execute &quot;INSERT INTO DestTable (Field1Text,Field2Text,Field3Number) &quot; _
& &quot;VALUES('&quot; & rs(&quot;TextField1Name&quot & &quot;','&quot; & rs(&quot;TextField2Name&quot & &quot;',&quot; & rs(&quot;NumberField1Name&quot & &quot&quot;

You could also create a new recordset(rs2) on the destination table and then loop through the disconnected recordset(rs1) and add the records via AddNew method of the rs2 recordset, using an array on the AddNew method, or adding the values for each field individually. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[MichaelRed]

While the several approaches to making a COPY of a recordset respond to the inquiry, I would pose a different issue to carry1: WHY are you doing this. Creating new table(s) is an &quot;expensive&quot; business, both in Disc usage, memory requirements and execution time, and eventually in maintenance. If the information already exists in another source, and you are able to correctly identify the set of records, it is unlikely that you will improve the operation of your app by creating a new table, while it is quite likely you will see the downside of the additional items noted above.

Ther ARE a few instances where the creation of new (temporary) tables is justified (necessary), but you should review the process and be sure this is NECESSARY.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places