Howdy, Stranger!

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

Categories

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

jlegjleg Member Posts: 237
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

  • SagaciousSagacious Member Posts: 36
    [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


  • jlegjleg Member Posts: 237
    : [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 :]

  • jlegjleg Member Posts: 237
    : [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
  • Justin BibJustin Bib USAMember Posts: 0

    [ http://forcoder.org ] free video tutorials and ebooks about || Perl, Objective-C, Visual Basic, C, Ruby, MATLAB, Delphi, PL/SQL, C++, C#, R, Assembly, Go, Java, Scratch, PHP, Python, Swift, Visual Basic .NET, JavaScript Bash, Alice, Scheme, Logo, D, Apex, VBScript, Dart, FoxPro, COBOL, SAS, Erlang, LabVIEW, Ada, Scala, Hack, Fortran, Kotlin, Prolog, Transact-SQL, Crystal, Rust, Julia, ABAP, F#, Lisp, Awk, Clojure, Lua, ML || _____

Sign In or Register to comment.