Inserting matching records from dbX into dbY?

[sarah_anne]

I have a main data-filled database with a table called "aTable", as well as a secondary empty but structurally identical database.

On my ASP page (export.asp?ID=[id]) I can open a recordset containing all records from main db matching that URL parameter; let's call it rsData.

I would like to add every single record in rsData to the secondary database (in the identical but empty equivalent table "aTable").

I can open the recordset from main db and filter it, but how do I actually insert every record in rsData into the 2nd database?

There seems to be 2 ways of doing it - SQL (execute) and a method using recordsets, I assume it's the latter I'd want as it's for every record in rsData, but can't find any beginner examples of use on the net!

Thanks in advance for any help guys!

 

[sarah_anne]

Hmm. Having considered I think I may have placed this in the wrong forum, oops! I'll leave it at the mods' discretion on this one

 

[workmad3]

just as a quick question,what is the purpose of this? cant you do whatever work you need to on the data in the recordset you have filtered out? I only ask because copying the data like this removes one of the main reasons for having a database in the first place.

 

[sarah_anne]

Of course, I'll explain...

We have a main database that's edited using the intranet with ASP pages. However, the data needs to be edited offshore (literally onboard ships with no net connections) and they aren't happy using CSV files, they want to use Access forms as they're familiar with that format (old dogs and all that...).

Solution:
- Have main database file with no forms in it - edited using intranet, filled with data.
- Copy the empty db (template.mdb) with forms in it and no data to new file called exported.mdb
- Export certain data to exported.mdb from main database
- Provide download link via intranet page to download newly amalgamated form+data filled exported.mdb
- Allow engineers to edit offshore using Access' inbuilt forms in exported.mdb
- Worry about importing later

Hope that's clear enough! They're very picky about how they want to edit the data, and it's Access/forms all the way.

 

[workmad3]

heh, no accounting for taste at times not sure how would be best to do this though
ill have a think about it unless someone else already knows a solution (other than manually exporting the tables as csv from one and importing into the other)

 

[workmad3]

ok, i think i have an idea, based on SQL inserts.

Dim Conn1 As New ADODB.Connection, rs As ADODB.Recordset, sql As String

Conn1.ConnectionString = <Open a connection to the second db here>
Conn1.Open
rs = CurrentDb.OpenRecordset("rsData")

While Not rs.EOF
   sql = "INSERT INTO <table_name>(field1, field2, field 3, etc) VALUES(" & rs.Fields("Field1") & ", " & rs.Fields("Field2") & ", " & rs.Fields("Field3") & ", " & etc & ")"
   Conn1.Execute sql
   rs.MoveNext
Loop
Conn1.Close

hope that helps

 

[sarah_anne]

Workmad, thanks for your input - however the server I am working on does not permit SQL execution of statements (.execute)! Found that out after a couple hours troubleshooting

I have figured out a way to insert using recordsets, however, but am having different problems... it's mainly related to using an AutoID with insertion into tables, I'll start a new thread since it's going off on a tangent to what I'm asking in this one....