Create table in new database from existing databse [RESOLVED]

[satyarao]

Hi,

I am working with Ms-Access2000, with VB6.
I am having two identical database in two different folders with same name, and identical table name with similar structures.

eg:
1. D:\ABC\Fabric.mdb

2. D:\NEW\Fabric.mdb

the first data base consists of data, where as second one consists of empty tables...

Here... I want to dump the data of selected tables of ABC folder's Fabric database to NEW folder's Fabric database.

I thought of 2 ways....

1) first reading the data of table1 and using INSERT command putting each record to NEW folder's Fabric database...
(like this i have to go on reading the records... from selected tables and put in the correspoinding table1 in NEW folder's Fabric database)

2) Deleting the 'table1' from NEW folder's Fabric database... and simply copy the 'table1' from ABC folder to NEW folder...


in the second model i am not clear that how to copy an existing table from on database to another database.
or
is there any way to create new table in required (NEW) database from existing database(ABC)... (like in Oracle Create table emp1 as(Select * from EMP)).... in this case first CREATE part should be done in NEW database... and SELECT part should be done at ABC database...

please help me....

thanks in advance...

[brucevde]

The Insert Into sql statement can be used to copy a table from one database to another. The Select Into sql statement can be used to create a table in another database.

Connect to your D:\ABC\Fabric.mdb database and execute one of these statement against your connection (check the syntax).

Insert Into Table1 In D:\NEW\Fabric.mdb Select * From Table1

Select * Into Table1 In D:\NEW\Fabric.mdb From Table1

[satyarao]

hai... bruce...

Insert Into Table1 In D:\NEW\Fabric.mdb Select * From Table1

the above.... syntax is not working as i wished...

I am working.... using ADODB...

my connection is connected to d:\abc\fabric.mdb...

and i tried to execute... it but i couldn't get it dumped...

it is not allowing me to specify... the full path... instead of D:\new\Fabric.mdb I could only... specify... Fabric.mdb only...

my table data went to ... C:\My Documents\Fabric.mdb database...

is there any chance to change the default database directory...?

I tried this thru... MsAccess , Tools menu, options option 'General' tab... i set the Default database Folder to D:\new ...
then also... it is neglecting the posting the data in my required folder's database.

any how thank you for your suggestion...

can you... explain in descriptive... for my problem...

[brucevde]

You need to surround the path to the external database within single quotes.

Private Sub Command1_Click()
    Dim objDB As ADODB.Connection
 
    Set objDB = New ADODB.Connection
 
    objDB.Open "provider=microsoft.jet.oledb.4.0;data source=c:\projects\northwind.mdb"
    
    objDB.Execute "Insert Into Customers In 'C:\Junk\Testing.mdb' Select * From Customers"
    objDB.Close
 
    Set objDB = Nothing
End Sub

[satyarao]

thank you very much... bruce..

It is working as i wished...

once again thank you for your response.