Hi all



I have two mdb files where I need to move all records from a table in one 

to a table of the same name in another.



What I am currently doing is opening two connections to the mdb files, 

doing a "select * from table1" on the first mdb then looping through this 

and running an insert one record at a time to put this record into the 

second mdb.



This is not particularly nice code, can anyone see a better way to do this?





Appreciate any pointers



Regards

Graham

Can you not use the DTS facility in SQL server?



Sam Clohesy

Project Manager

Tel: 0208 772 3958

E: samc@e...

W: http://www.etypemedia.co.uk





-----Original Message-----

From: gbrown@c... [mailto:gbrown@c...]

Sent: 04 January 2002 13:42

To: ASP Databases

Subject: [asp_databases] Moving a table from one MDB to another





Hi all



I have two mdb files where I need to move all records from a table in one 

to a table of the same name in another.



What I am currently doing is opening two connections to the mdb files, 

doing a "select * from table1" on the first mdb then looping through this 

and running an insert one record at a time to put this record into the 

second mdb.



This is not particularly nice code, can anyone see a better way to do this?





Appreciate any pointers



Regards

Graham






$subst('Email.Unsub').

can you open the MDBs directly?  i.e. can you have both database windows

open concurrently on your desktop?  If so I think you can simply drag the

source table into the destination DB Window - it should ask you what you

want it to do: replace the existing table, rename it or append the new

records to the existing table (i'm not sure if these options are correct)



Alternatively you can do:



INSERT INTO destination_table FROM source_table



HTH,

Colin





-----Original Message-----

From: gbrown@c... [mailto:gbrown@c...]

Sent: 04 January 2002 13:42

To: ASP Databases

Subject: [asp_databases] Moving a table from one MDB to another





Hi all



I have two mdb files where I need to move all records from a table in one 

to a table of the same name in another.



What I am currently doing is opening two connections to the mdb files, 

doing a "select * from table1" on the first mdb then looping through this 

and running an insert one record at a time to put this record into the 

second mdb.



This is not particularly nice code, can anyone see a better way to do this?





Appreciate any pointers



Regards

Graham






$subst('Email.Unsub').





*******



This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.



For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.

Thanks very much for replying.



I'd like to accomplish this in code either as part of a webpage or as a vb 

utility. The full routine basically converts legacy data into our new 

system. 



This part is the second of five phases with the full thing taking approx 4 

hours to run across a reasonable sized dataset. Probably being run 

overnight when users are off the system.



The old way was to have someone use Access and cut and paste the tables 

but this tended to get missed or done incorrectly so I started looking for 

a way to do this without any user intervention. So finding a 

straightforward way in code sounds best.



As far as I can see I can't use "insert into destination values select * 

from source" as this infers both tables are in the same mdb which they are 

not. That is if I've understood properly.



The select/loop and insert into method does work but it is painfully slow.



All comments as always gratefully received!



Regards

Graham

can you go into the destination DB and create a linked table pointing at the

source table?  Then you can use the INSERT INTO tblDestination FROM

tblSource because access treats the linked table as if it's in the same DB.



Are the two tables exactly the same or does some of the data have to be

converted? - if it does then your only option will be to open the recordset,

looping through it and amending every necessary value, which is very messy

but often required.  I found it much easier to write the VBA in Access as

you don't have to refer to connections etc



Good luck,

Colin



-----Original Message-----

From: gbrown@c... [mailto:gbrown@c...]

Sent: 04 January 2002 15:14

To: ASP Databases

Subject: [asp_databases] RE: Moving a table from one MDB to another





Thanks very much for replying.



I'd like to accomplish this in code either as part of a webpage or as a vb 

utility. The full routine basically converts legacy data into our new 

system. 



This part is the second of five phases with the full thing taking approx 4 

hours to run across a reasonable sized dataset. Probably being run 

overnight when users are off the system.



The old way was to have someone use Access and cut and paste the tables 

but this tended to get missed or done incorrectly so I started looking for 

a way to do this without any user intervention. So finding a 

straightforward way in code sounds best.



As far as I can see I can't use "insert into destination values select * 

from source" as this infers both tables are in the same mdb which they are 

not. That is if I've understood properly.



The select/loop and insert into method does work but it is painfully slow.



All comments as always gratefully received!



Regards

Graham






$subst('Email.Unsub').





*******



This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.



For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.

Off the top of my head (since I have no experience do 

this). I would think that you could accomplish this

in several steps



1) Use the ADO Schema ability to get the table structure

   from the source mdb.



2) Create the SQL to create the same table in the

   destination mdb



3) Open a ADO open a recordset against the source table

   and save it as an XML file.



4) Open a connection to the destination mdb.



5) Open a recordset against the destination mdb



6) Load the saved XML file into the recordset 



7) Batch Update to the destination mdb.



Anyone have any ideas?



Dallas





Quoting gbrown@c...:



> Thanks very much for replying.

>

> I'd like to accomplish this in code either as part of 

a webpage or as a vb

> utility. The full routine basically converts legacy 

data into our new

> system.

>

> This part is the second of five phases with the full 

thing taking approx 4

> hours to run across a reasonable sized dataset. 

Probably being run

> overnight when users are off the system.

>

> The old way was to have someone use Access and cut 

and paste the tables

> but this tended to get missed or done incorrectly so 

I started looking for

> a way to do this without any user intervention. So 

finding a

> straightforward way in code sounds best.

>

> As far as I can see I can't use "insert into 

destination values select *

> from source" as this infers both tables are in the 

same mdb which they are

> not. That is if I've understood properly.

>

> The select/loop and insert into method does work but 

it is painfully slow.

>

> All comments as always gratefully received!

>

> Regards

> Graham

>





> $subst('Email.Unsub').

> 

Here is an idea...

1. First go to the new mdb and select the tables tab.

2. Do a NEW and choose LINK TABLE...then pick the table(s) from the old

mdb

3. Now you have new and old available on the same mdb.

4. Now you can to the "insert into destination values select *

from source"

5. If you only want structure not data later you can do a create where

1=3D2



-----Original Message-----

From: gbrown@c... [mailto:gbrown@c...]

Sent: Friday, January 04, 2002 10:14 AM

To: ASP Databases

Subject: [asp_databases] RE: Moving a table from one MDB to another





Thanks very much for replying.



I'd like to accomplish this in code either as part of a webpage or as a

vb

utility. The full routine basically converts legacy data into our new

system.



This part is the second of five phases with the full thing taking approx

4

hours to run across a reasonable sized dataset. Probably being run

overnight when users are off the system.



The old way was to have someone use Access and cut and paste the tables

but this tended to get missed or done incorrectly so I started looking

for

a way to do this without any user intervention. So finding a

straightforward way in code sounds best.



As far as I can see I can't use "insert into destination values select *



from source" as this infers both tables are in the same mdb which they

are

not. That is if I've understood properly.



The select/loop and insert into method does work but it is painfully

slow.



All comments as always gratefully received!



Regards

Graham






$subst('Email.Unsub').