Copy a table from one database to another using SQL via ADODB

I am using 2 ADODB connections one (cn1) to database called LAYOUTS and the other (cn2) to SHOWS.

I have access to both databases and can use the cn1.execute "SELECT * INTO [TABLE1] FROM [TABLE2]" to create a copy of a table into the same database.

How can I do the above if [TABLE2] uses cn1 (LAYOUTS) and [TABLE1] uses cn2 (SHOWS) ? Obviously the execute command is only relevant to one connection.

Can someone help?

John

Comments

  • [b][red]This message was edited by Sagacious at 2002-10-21 13:28:26[/red][/b][hr]
    : I am using 2 ADODB connections one (cn1) to database called LAYOUTS and the other (cn2) to SHOWS.
    :
    : I have access to both databases and can use the cn1.execute "SELECT * INTO [TABLE1] FROM [TABLE2]" to create a copy of a table into the same database.
    :
    : How can I do the above if [TABLE2] uses cn1 (LAYOUTS) and [TABLE1] uses cn2 (SHOWS) ? Obviously the execute command is only relevant to one connection.
    :
    : Can someone help?
    :
    : John
    :

    John,
    This is no simple task. You can use CREATE TABLE in your SQL to make an empty [TABLE1] in (SHOWS). Then loop each field in each record into the new table. Give it a shot and if you like email me for a question or two.

    You are going to get a RecordSet of [Table1] with cn1 and then use cn2 to make the new table. Then move the data from the recordset to the new table field by field, record by record. It is a little slow, but I am able to move about 21mb in under a minute.

    [Red]
    Lookup [/Red][Blue]ADO RecordSet... Now What[/Blue][Red] from the Visual Basic Board. GENJURO was great help when I had the same questions.[/Red]

    Hope this helps,
    Sagacious


  • : [b][red]This message was edited by Sagacious at 2002-10-21 13:28:26[/red][/b][hr]
    : : I am using 2 ADODB connections one (cn1) to database called LAYOUTS and the other (cn2) to SHOWS.
    : :
    : : I have access to both databases and can use the cn1.execute "SELECT * INTO [TABLE1] FROM [TABLE2]" to create a copy of a table into the same database.
    : :
    : : How can I do the above if [TABLE2] uses cn1 (LAYOUTS) and [TABLE1] uses cn2 (SHOWS) ? Obviously the execute command is only relevant to one connection.
    : :
    : : Can someone help?
    : :
    : : John
    : :
    :
    : John,
    : This is no simple task. You can use CREATE TABLE in your SQL to make an empty [TABLE1] in (SHOWS). Then loop each field in each record into the new table. Give it a shot and if you like email me for a question or two.
    :
    : You are going to get a RecordSet of [Table1] with cn1 and then use cn2 to make the new table. Then move the data from the recordset to the new table field by field, record by record. It is a little slow, but I am able to move about 21mb in under a minute.
    :
    : [Red]
    : Lookup [/Red][Blue]ADO RecordSet... Now What[/Blue][Red] from the Visual Basic Board. GENJURO was great help when I had the same questions.[/Red]
    :
    : Hope this helps,
    : Sagacious
    :
    :
    :
    Thank you Sagacious
    Yes I thought of doing it the way you have suggested. I started working on it last night.

    I made a temporary recordset from [TABLE2]. My next step is to CREATE a new [TABLE1] in Shows. I have to get the definition of [TABLE2] from Layouts and replicate it with the creation of [TABLE1]. Then as you say cycle through all the records of [TABLE2] copying them to [TABLE1].

    Is there an easy way to obtain the structure of [TABLE2] and use it to create [TABLE1] ?

    Regards

    John :]

  • : [b][red]This message was edited by Sagacious at 2002-10-21 13:28:26[/red][/b][hr]
    : : I am using 2 ADODB connections one (cn1) to database called LAYOUTS and the other (cn2) to SHOWS.
    : :
    : : I have access to both databases and can use the cn1.execute "SELECT * INTO [TABLE1] FROM [TABLE2]" to create a copy of a table into the same database.
    : :
    : : How can I do the above if [TABLE2] uses cn1 (LAYOUTS) and [TABLE1] uses cn2 (SHOWS) ? Obviously the execute command is only relevant to one connection.
    : :
    : : Can someone help?
    : :
    : : John
    : :
    :
    : John,
    : This is no simple task. You can use CREATE TABLE in your SQL to make an empty [TABLE1] in (SHOWS). Then loop each field in each record into the new table. Give it a shot and if you like email me for a question or two.
    :
    : You are going to get a RecordSet of [Table1] with cn1 and then use cn2 to make the new table. Then move the data from the recordset to the new table field by field, record by record. It is a little slow, but I am able to move about 21mb in under a minute.
    :
    : [Red]
    : Lookup [/Red][Blue]ADO RecordSet... Now What[/Blue][Red] from the Visual Basic Board. GENJURO was great help when I had the same questions.[/Red]
    :
    : Hope this helps,
    : Sagacious
    :
    :
    :
    Thanks again Sagacious

    I had a look at the postings AD) RecordSet.... Now What. I has given me some indication as to the path I should take.

    I hope I've got it right. I assume that creating a recordset of the [TABLE2]. Creating a new [TABLE1] using the ADO.Fields property of [TABLE2] Because I may not know what [TABLE2]'s structure may be at the time. Then cycle true the temporary recordset appending the field values into the newly created [TABLE1]. Then kill off the temporary recordset.

    I don't have to be selective with the records I need them all to come across.

    I am resonabley familiar with SQL statements. So I hope that I will be able to achieve my goal.

    I have tried to resist the temptation to go back to DAO and Jet. It just seems that the above process is a little easier in this environment.

    Thanks

    John
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories