Exporting Out/ Importing In - Which Format 1

[porto99]

I have recieved a large database with many tables, now I have made a Query to extract the data I need to allow a C++ application to process.
However to safegaurd the original database, I select all the data display by this query and export it in Excel format.

I have another database which I import this data into, using Get External Data (opt for the Excel option).

My question is what is the best way to Export then Import this data?

It seems that in my program I keep having to change the database access type from Text to Number etc as the type of attributes may change (alterning the Query to get the correct data set).

i.e. The attributes of the table that results via the Query are not always preserved when I Import the data.

Any suggestions please.

Many thanks,

Porto.

 

[AlexCuse]

You could just empty out your destination table and then export your query directly to the second database (provided it is ODBC compliant). If it is a second Access DB this would be super easy, and provided your table is layed out correctly you would not need to worry about the formatting issues caused by exporting/importing to/from excel.

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[porto99]

Hi Alex, I dont understand how to do this. The first MS Access database has about 25 tables (this is where the Query is).

How do I copy and run this Query from the second MS Access batadase, which wil only have the empty table ( yes - and do I have to set up all the fields)

Many thanks,

Porto.

 

[Golom]

I wouldn't mess around with exporting/importing through an Excel format. That often gets you into conversion issues between Access and Excel and you really want an Access-to-Access transfer.

If the table you want to import is in [red]C:\xxx\Other.mdb[/red] then

INSERT INTO LocalTable 

SELECT * From [red][;Database=C:\xxx\Other.mdb].[/red]TableName

and run this from the mdb that contains your destination table. The above assumes that "LocalTable" and "TableName" have the same fields in the same order.

 

[porto99]

Golom, thanks for the post.

Just one question, in the main database, I run a Query that produces the results I want.
So how can I import this 'table' which is not a table just the results of a Query into my destination database?

Many thanks

 

[Golom]

Use exactly the same mechanism as outlined above.

INSERT INTO LocalTable 

SELECT * From [;Database=C:\xxx\Other.mdb].[red]QueryName[/red]

where [red]QueryName[/red] is the name of the query in the source database.

Run it from the database that contains your destination table ("LocalTable" in the above.)