migrading tables to other database

migrading tables to other database

Post by BK » Sun, 03 Feb 2002 04:43:09



Hi,

I am currently working on two databases.

Using VB, I am able to get recordset from one database and I need to
transfer that recordset to the other database.

I know I can get those recordset and insert them into the other using loops
and insert one row by another.

It just takes too much time, TOO MUCH.

If this is the only way, I have to stick with it but is there any other way
to insert several rows at onece???

also, this table has too many fields( about 30 ~ 50 ) and I don't want to
write all the insert statement if avoidable.

thanx

 
 
 

migrading tables to other database

Post by Stuart Sutherlan » Sun, 03 Feb 2002 06:27:47



>Hi,

>I am currently working on two databases.

>Using VB, I am able to get recordset from one database and I need to
>transfer that recordset to the other database.

>I know I can get those recordset and insert them into the other using loops
>and insert one row by another.

>It just takes too much time, TOO MUCH.

>If this is the only way, I have to stick with it but is there any other way
>to insert several rows at onece???

>also, this table has too many fields( about 30 ~ 50 ) and I don't want to
>write all the insert statement if avoidable.

>thanx

Forget about Insert statements

Assuming that you have a Recordset for the database with the data and
a recordset for receiving the data and both tables have the same
structure, all you need is a for next loop.

NewRecordset.addnew

For a = 0 to Oldrecordset.fields.count
        NewRecordset(a) = oldRecordset(a)
next

NewRecordset.update

Stuart Sutherland

 
 
 

migrading tables to other database

Post by BK » Sun, 03 Feb 2002 06:58:09


Is there way to create table automatically also?

It has too many columns that I need to worry about.

It will be nice if I don't have to create tables with that many fields.

Basically, I am copying over a existing table into another database.

thanx in advance.



> >Hi,

> >I am currently working on two databases.

> >Using VB, I am able to get recordset from one database and I need to
> >transfer that recordset to the other database.

> >I know I can get those recordset and insert them into the other using
loops
> >and insert one row by another.

> >It just takes too much time, TOO MUCH.

> >If this is the only way, I have to stick with it but is there any other
way
> >to insert several rows at onece???

> >also, this table has too many fields( about 30 ~ 50 ) and I don't want to
> >write all the insert statement if avoidable.

> >thanx

> Forget about Insert statements

> Assuming that you have a Recordset for the database with the data and
> a recordset for receiving the data and both tables have the same
> structure, all you need is a for next loop.

> NewRecordset.addnew

> For a = 0 to Oldrecordset.fields.count
> NewRecordset(a) = oldRecordset(a)
> next

> NewRecordset.update

> Stuart Sutherland

 
 
 

migrading tables to other database

Post by BK » Sun, 03 Feb 2002 07:24:13


   Also, can you verify my code for me?

Thanx

the error message is
------------------------------------------------------------------
Run-time error '3251':
Current Recordset does not support updating.  This may be a limition of the
provider, or of the selected locktype.

-------------------------------------------------------------------

    Dim cnReal As New ADODB.Connection
    Dim rsReal As New ADODB.Recordset
    Dim cnGold As New ADODB.Connection
    Dim rsGold As New ADODB.Recordset
    Dim sqlReal As String
    Dim sqlGold As String
    Dim i As Integer

    cnReal.Open "dsn=realConnection"
    cnGold.Open "dsn=goldConnection"

    sqlReal = "select * from tb1"
    sqlGold = "select * from tb2"

    Set rsReal = cnReal.Execute(sqlReal)
    Set rsGold = cnGold.Execute(sqlGold)

    rsGold.AddNew            <------------- Error in here
    For i = 0 To rsReal.Fields.Count
        rsGold(i) = rsReal(i)
    Next
    rsGold.Update



> >Hi,

> >I am currently working on two databases.

> >Using VB, I am able to get recordset from one database and I need to
> >transfer that recordset to the other database.

> >I know I can get those recordset and insert them into the other using
loops
> >and insert one row by another.

> >It just takes too much time, TOO MUCH.

> >If this is the only way, I have to stick with it but is there any other
way
> >to insert several rows at onece???

> >also, this table has too many fields( about 30 ~ 50 ) and I don't want to
> >write all the insert statement if avoidable.

> >thanx

> Forget about Insert statements

> Assuming that you have a Recordset for the database with the data and
> a recordset for receiving the data and both tables have the same
> structure, all you need is a for next loop.

> NewRecordset.addnew

> For a = 0 to Oldrecordset.fields.count
> NewRecordset(a) = oldRecordset(a)
> next

> NewRecordset.update

> Stuart Sutherland

 
 
 

migrading tables to other database

Post by Paul Durdi » Mon, 04 Feb 2002 00:35:19


The quick simple way is to execute a SELECT ... INTO statement, otherwise
you can use a DTS package, either create it in EM and execute from VB, or
create and execute within VB.

--
Paul Durdin


Quote:> Hi,

> I am currently working on two databases.

> Using VB, I am able to get recordset from one database and I need to
> transfer that recordset to the other database.

> I know I can get those recordset and insert them into the other using
loops
> and insert one row by another.

> It just takes too much time, TOO MUCH.

> If this is the only way, I have to stick with it but is there any other
way
> to insert several rows at onece???

> also, this table has too many fields( about 30 ~ 50 ) and I don't want to
> write all the insert statement if avoidable.

> thanx

 
 
 

migrading tables to other database

Post by Stephany Youn » Wed, 06 Feb 2002 15:33:47


First of all, the Execute method of the ADODB.Connection object, (with a
'select ...' statement), returns, by default a ForwardOnly cursor.  In this
context your AddNew method is illegal and is correctly throwing the error.

Secondly, lets talk about the type of database, (you only mention that you
are working on 2 databases and give no details of the database types).

If you are using SQL Server and the 2 databases are parented in the same
server instance then you can simply do an 'select * into
<db2_name>.<table_name> from <db1_name>.<table_name>'.

If you are using 2 Jet databases then there is a similar mechanism and you
will be able to find the syntax in the documentation.

If the target database is in SQL Server and the source database is in Jet,
(or Oracle or any other database that has an OLEDB Provider) then you can
link the source database and then use the 'select * into
<db2_name>.<table_name> from <db1_name>.[<catalog_name>]..<table_name>'
style syntax.

If the target database is in Jet then the chances are pretty good that you
will be able to link the source table via ODBC and simply do a 'select *
into <table1> from <table2>'

I think you find that this approach will solve your problem.

Note: The fact that the mechanism 'select ... into ... from ...' is part of
the SQL standard should have prompted you to investigate it's use in your
environment.


>    Also, can you verify my code for me?

> Thanx

> the error message is
> ------------------------------------------------------------------
> Run-time error '3251':
> Current Recordset does not support updating.  This may be a limition of
the
> provider, or of the selected locktype.

> -------------------------------------------------------------------

>     Dim cnReal As New ADODB.Connection
>     Dim rsReal As New ADODB.Recordset
>     Dim cnGold As New ADODB.Connection
>     Dim rsGold As New ADODB.Recordset
>     Dim sqlReal As String
>     Dim sqlGold As String
>     Dim i As Integer

>     cnReal.Open "dsn=realConnection"
>     cnGold.Open "dsn=goldConnection"

>     sqlReal = "select * from tb1"
>     sqlGold = "select * from tb2"

>     Set rsReal = cnReal.Execute(sqlReal)
>     Set rsGold = cnGold.Execute(sqlGold)

>     rsGold.AddNew            <------------- Error in here
>     For i = 0 To rsReal.Fields.Count
>         rsGold(i) = rsReal(i)
>     Next
>     rsGold.Update




> > >Hi,

> > >I am currently working on two databases.

> > >Using VB, I am able to get recordset from one database and I need to
> > >transfer that recordset to the other database.

> > >I know I can get those recordset and insert them into the other using
> loops
> > >and insert one row by another.

> > >It just takes too much time, TOO MUCH.

> > >If this is the only way, I have to stick with it but is there any other
> way
> > >to insert several rows at onece???

> > >also, this table has too many fields( about 30 ~ 50 ) and I don't want
to
> > >write all the insert statement if avoidable.

> > >thanx

> > Forget about Insert statements

> > Assuming that you have a Recordset for the database with the data and
> > a recordset for receiving the data and both tables have the same
> > structure, all you need is a for next loop.

> > NewRecordset.addnew

> > For a = 0 to Oldrecordset.fields.count
> > NewRecordset(a) = oldRecordset(a)
> > next

> > NewRecordset.update

> > Stuart Sutherland

 
 
 

1. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

I have several Access tables, 30 or so, that should be linked to each other.
I have a diagram on paper that shows how the tables link to each other.
When I opened up the Access files to start working on this project, I
discovered they were in separate files.  How is this?  Is there a way to
link a table in one Access file to a table in another Access file?

After the file linking is accomplished, I need to transfer about 30,000 rows
from these tables into one Access table.

Right now I'm using VB6/DAO to query the information and plug it into the
new table one row at a time.  With 30,000 rows, this method will take more
than a few hours.

By the way, the new table will also have different column names.  So the
info will have to get copied to a table with a different column names or
after the table is populated, the column names will have to be changed;
which ever is easiest.

Is this table linking possible and could someone suggest a method to speed
up this process?

Thanks
Sally

2. Grid and More Details

3. How can I publish a snapshot off a table with Foreign Keys to others tables

4. log file

5. joining a table to two others

6. computer for todo list

7. How to join 1 table to many others

8. hosts.equiv and ESQL/C

9. Access97 can link to some SQL2000 tables, not others

10. Help: Need sp to empty a table that is FK to others

11. Help on table corrupt, and others...

12. New LAN-some machines fine others some tables won't open

13. joining a table to two others