Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Copy ADO rs to new Access table
- Thread starter carry1
- Start date
see my post in thread222-371417 [/b][/i][/u][sub]*******************************************************
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!
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!
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][sub]*******************************************************
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!
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][sub]*******************************************************
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!
- Thread starter
- #4
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][sub]*******************************************************
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!
[/b][/i][/u][sub]*******************************************************
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!
- Thread starter
- #6
>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 "INSERT INTO DestTable (Field1Text,Field2Text,Field3Number) " _
& "VALUES('" & rs("TextField1Name"
& "','" & rs("TextField2Name" & "'," & rs("NumberField1Name" & ""
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][sub]*******************************************************
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!
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 "INSERT INTO DestTable (Field1Text,Field2Text,Field3Number) " _
& "VALUES('" & rs("TextField1Name"
& "','" & rs("TextField2Name" & "'," & rs("NumberField1Name" & ""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][sub]*******************************************************
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
Programmer
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 "expensive" 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
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
- Status
