Import table from one db into another

[MikeR]

My front end is Delphi, and the back is Jet. I'm using DAO.

I need to import a table from one DB into another. The MS doc says

"The general syntax of the "copy table" SQL is: SELECT * INTO <Target> FROM <Source>

Where <Source> is the table we want to copy and <Target> is the destination for the
table. Note that this SQL statement will attempt to create <Target> with the same
table structure as <Source> and populate <Target> with all of the records from <Source>.

Note that <Target> and <Source> allow the following three variations: ( I left off
two) [<Full path to Microsoft Access database>].[<Table Name>]"

Both DB are open. My query is:

sql := 'Select * into DB1.mdb.Alias from Upd45.MDB.Alias';
dbDAO.Execute(sql);

I get 'Query input must contain at least one table or query'.

Any suggestions?
Thanx,
Mike

 

[MikeR]

Both DB are open. My query is:

sql := 'Select * into DB1.mdb.Alias from Upd45.MDB.Alias';

Never mind, I got it.
sql := 'Select * into [DB1.mdb].Alias from [Upd45.MDB].Alias';

 

[Wayne-I-M]

I can't see your app so I don't know if it important but using SELECT * INTO
will not maintain any relationships you may have with the "old" table. Or -
worse - you end up with lots of new tables tables(1) tables(2) tables(3) etc
etc

You may be better just importing the table contents ?

 

[MikeR]

I can't see your app so I don't know if it important but using SELECT * INTO
will not maintain any relationships you may have with the "old" table. Or -
worse - you end up with lots of new tables tables(1) tables(2) tables(3) etc
etc

You may be better just importing the table contents ?

Thanks, Wayne. The table I'm importing has additional fields, so I thought it would
just be simpler to drop the table and import the new one. I'm not sure what you mean
by relationships (one to many kind of thing?). How would I wind up with copies of the
table?

Now the drop isn't working. (It did *ONCE*, I swear ;=)) "The database engine could
not lock table 'Alias' because it is already in use by another person or process."

The only reference to Alias is: dbDAO.Execute('Drop TABLE Alias');

 

[MikeR]

Now the drop isn't working. (It did *ONCE*, I swear ;=)) "The database
engine could not lock table 'Alias' because it is already in use by
another person or process."

The only reference to Alias is: dbDAO.Execute('Drop TABLE Alias');

Got this one working also. I was updating 2 other tables, and doing the drop after
that. Making the drop the first operation after opening the DB did it...... Go figure.

 

[Wayne-I-M]

Hi

Say you have very simple database with 2 tables -
tblHouses - details of houses eg. Street. Number. Colour.etc
tblPeople - details of the people that live in the houese eg. Name. Sex.
Date of birth. etc

You would have the primary field of tblPeople in tblHouese to produce a
realationship between the 2 tables.

If you import another table called tblHouses it will be placed as
tblHouses(1) and will not have the realtionship with tblPeople so you will
not be able to use the newly imported table when refering to the people who
live in a house (Hope that makes sense).

All you need to do - instead of importing a new table with the same name is
to import the data within the table an (via an append query) write this data
into tblHouses - this will mean the you maintain the relationship and you can
use the data you have just imported.

Don't import the table (unless there is a good reason to) - just import the
data