Export table to a New File and Drop

[racer25]

Hi

I have an Linked ODBC Connection to an Oracle 9 database. I am looking to import every table in the database into a table of similar name.

e.g CLIENT_LIVE (Oracle Name) into CLIENT_STATIC (Access2007).

I can easily do a make table query to import the table.

However the problem I have is that after some of the tables are large and I soon reach the file limit.

My idea would be to make the table but make it in a new Access file creating a new file for each table.

Is there away of programatically exporting a table to a new file.

Is there a better way of doing this.

 

[Call_Me_Sam]

Hi

I have an Linked ODBC Connection to an Oracle 9 database. I am looking to import every table in the database into a table of similar name.

e.g CLIENT_LIVE (Oracle Name) into CLIENT_STATIC (Access2007).

I can easily do a make table query to import the table.

However the problem I have is that after some of the tables are large and I soon reach the file limit.

My idea would be to make the table but make it in a new Access file creating a new file for each table.

Is there away of programatically exporting a table to a new file.

Is there a better way of doing this.

Firstly, are you saying the data after each import needs to be in a different table or the same table, therefore appending data.

If appending, then have you tried having seperate db files with a table in each, and linking these to a main import controlling db, which has links to Oracle. You could then code it to fire queries that appends the data to the seperately linked tables?

If not then that's something i haven't covered

 

[racer25]

Thanks for the reply.

I have 200 + linked tables in one Access file.

I would like to import the data from each table into an Access table rather than have it linked.

The imported tables will not fit in one Access DB - so I thought the best solution would be to export the newly created unlinked table into a new file. Ideally I would like a maketable query that actually makes the table in another Access File.

I hope this makes sense.

Thanks again.

 

[DJkarl]

Well first thing I would look at is why you need to copy data locally in the first place? If the data you need is in linked tables already why make another copy of it? I've had systems where I've needed to copy bits and pieces of data from linked sources locally, but never all the tables and all the data.

That being said you can create a new database using the CreateDatabase method in VBA, you could then point your make table query to that new database. This could be repeated for each table.