Copying tables from a linked database in to a new database

[David Paterson]

Hi,

I am using access 97 and I am trying to copy tables from one database to
another one which I have just created.

The problem I have is that the CopyObject does not allow you to assign the
database that you wish to copy from, it just uses the currentdb. My current
db has the linked tables in it but I need to copy the orignal tables in to
the new database.

I need to do this via code placed on a button on a form.

 

[David Lloyd]

David:

One approach you may want to investigate is the SQL "IN" statement. I know
this is supported in recent version of Access, however, I do not have Access
97 installed.

For example:

INSERT INTO NewDatabaseTable
SELECT *
FROM OldDatabaseTable
IN "C:\OldDatabase.mdb"

Alternatively, you may be able to just use your linked tables to query the
data into new tables.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,

I am using access 97 and I am trying to copy tables from one database to
another one which I have just created.

The problem I have is that the CopyObject does not allow you to assign the
database that you wish to copy from, it just uses the currentdb. My current
db has the linked tables in it but I need to copy the orignal tables in to
the new database.

I need to do this via code placed on a button on a form.

 

[David Paterson]

Hi David

I have tried that. It doesn't help because I need to insert the new table
into another external database and not the current db.

INSERT INTO NewDatabaseTable

SELECT *
FROM OldDatabaseTable
IN "C:\OldDatabase.mdb"

The Insert INTO only allows you to use the current db and not specify an
external path to a different db.

I need to do something like

INSERT INTO (newdatabasepath)NewDatabaseTable

SELECT *
FROM OldDatabaseTable
IN "C:\OldDatabase.mdb"

I don't think this is allowed.


Thanks anyway.