Copy a Table to another Table 
Author Message
 Copy a Table to another Table

Question, (VB6 +Acces97)

Is it possible to copy an acces table (e.g. TABLE1) with about 5000 records
to a new table (e.g. TABLE2 which is empty).

The way I do it now is:

I open TABLE1 and select all records.=>

Data1.databasename="Database.mdb"
Data1.recorssettype=0
Data1.recordsource="TABLE1"
Data1.refresh

Data2.databasename="Database.mdb"       'Same database as Data1
Data2.recordsettype= 0
Data2.recordsource="NEW"            '    This is an empty table with the
same structure as TABLE1

with Data1.recordset

        .movelast
        .movefirst

Do while not .EOF

Data2.recordset.addnew

Data2.recordset.fields(0)= .Fields(0)
Data2.recordset.fields(1)= .Fields(1)
Data2.recordset.fields(2)= .Fields(2)
Data2.recordset.fields(3)= .Fields(3)
Data2.recordset.fields(4)= .Fields(4)
Data2.recordset.fields(5)= .Fields(5)
Data2.recordset.fields(6)= .Fields(6)

Data2.recordset.Update

.Movenext

Loop

End With

This way is working, only the speed is to slow for me.
Is it possible to copy table Table1 direct to table NEW without opening
Tabl1 and do it record by record.

I hope someone can give me an answer

Thanks

Gerwin Spijkerboer




Tue, 23 Sep 2003 16:50:21 GMT  
 Copy a Table to another Table
Gerwin

Go to my Tips & Tricks | Code Library | VB(A) Code | Backup/Copy open files

The first method on that page demonstrates how to use a Append query to do
what you want.

regards

Ian

** invalid email address, change dk to denmark

homepage http://www.kingsoft-denmark.com/
Tips & Tricks page http://tips.kingsoft-denmark.com/


Quote:
> Question, (VB6 +Acces97)

> Is it possible to copy an acces table (e.g. TABLE1) with about 5000
records
> to a new table (e.g. TABLE2 which is empty).

> The way I do it now is:

> I open TABLE1 and select all records.=>

> Data1.databasename="Database.mdb"
> Data1.recorssettype=0
> Data1.recordsource="TABLE1"
> Data1.refresh

> Data2.databasename="Database.mdb"       'Same database as Data1
> Data2.recordsettype= 0
> Data2.recordsource="NEW"            '    This is an empty table with the
> same structure as TABLE1

> with Data1.recordset

>         .movelast
>         .movefirst

> Do while not .EOF

> Data2.recordset.addnew

> Data2.recordset.fields(0)= .Fields(0)
> Data2.recordset.fields(1)= .Fields(1)
> Data2.recordset.fields(2)= .Fields(2)
> Data2.recordset.fields(3)= .Fields(3)
> Data2.recordset.fields(4)= .Fields(4)
> Data2.recordset.fields(5)= .Fields(5)
> Data2.recordset.fields(6)= .Fields(6)

> Data2.recordset.Update

> .Movenext

> Loop

> End With

> This way is working, only the speed is to slow for me.
> Is it possible to copy table Table1 direct to table NEW without opening
> Tabl1 and do it record by record.

> I hope someone can give me an answer

> Thanks

> Gerwin Spijkerboer





Tue, 23 Sep 2003 18:07:32 GMT  
 Copy a Table to another Table
you can use an SQL for it

SELECT * INTO table2 FROM table1

On Fri, 6 Apr 2001 10:50:21 +0200, "Gerwin Spijkerboer"

Quote:

>Question, (VB6 +Acces97)

>Is it possible to copy an acces table (e.g. TABLE1) with about 5000 records
>to a new table (e.g. TABLE2 which is empty).

>The way I do it now is:

>I open TABLE1 and select all records.=>

>Data1.databasename="Database.mdb"
>Data1.recorssettype=0
>Data1.recordsource="TABLE1"
>Data1.refresh

>Data2.databasename="Database.mdb"       'Same database as Data1
>Data2.recordsettype= 0
>Data2.recordsource="NEW"            '    This is an empty table with the
>same structure as TABLE1

>with Data1.recordset

>        .movelast
>        .movefirst

>Do while not .EOF

>Data2.recordset.addnew

>Data2.recordset.fields(0)= .Fields(0)
>Data2.recordset.fields(1)= .Fields(1)
>Data2.recordset.fields(2)= .Fields(2)
>Data2.recordset.fields(3)= .Fields(3)
>Data2.recordset.fields(4)= .Fields(4)
>Data2.recordset.fields(5)= .Fields(5)
>Data2.recordset.fields(6)= .Fields(6)

>Data2.recordset.Update

>.Movenext

>Loop

>End With

>This way is working, only the speed is to slow for me.
>Is it possible to copy table Table1 direct to table NEW without opening
>Tabl1 and do it record by record.

>I hope someone can give me an answer

>Thanks

>Gerwin Spijkerboer





Tue, 23 Sep 2003 19:50:09 GMT  
 Copy a Table to another Table

Quote:

> Question, (VB6 +Acces97)

> Is it possible to copy an acces table (e.g. TABLE1) with about
> 5000 records to a new table (e.g. TABLE2 which is empty).

<Database>.Execute "INSERT INTO new_table SELECT * FROM old_table"

only if the new_table already exists.

--
MikeC

Please reply to the group.



Wed, 24 Sep 2003 12:24:25 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Copy a Table to another Table

2. copy dbase table to access table

3. Copy Dbase table to Access Table

4. Copy fields from record in table A to record in table B using VBA

5. Copy Record from Table 1 to Table 2 - Acc97 - VBA

6. HowTo Copy a Recordset from a Table to an identically structured table

7. Copying the structure of a table to a new table

8. Copying Table text to tables

9. Copying table definition to create new table

10. Help Help Need to copy a field from one table into another table

11. MS Access DB - Copy Table A from Database A to Table A Database B

12. SQL and Copying Table to Table

 

 
Powered by phpBB® Forum Software