How to save the SQL script from ACCESS

[jhilltektips]

Hi,

I am wondering how to save an SQL
script including all the SQL involved
in creating 4 tables in a database.
I.E. How do i already have 4 tables
in an ACCESS 2000 db, and i would like
to drop and recreate the tables. How do
i get the SQL script on how to do that?

Thanks

 

[BNPMike]

Jet doesn't normally generate tables from SQL. Normally you use Access's GUI front end. The database metadata is held partly in tables and partly in collections.

Your two obvious choices are:
1) write the DDL by hand - shouldn't be two difficult
or
2) instead of dropping the table just delete *. If you then COMPACT the database, you finish up with the same end result as dropping and re-creating the table.

 

[jhilltektips]

Thank you BNPMike.
I will probably just write the DDL by hand as you recommended. However, is it possible to export the tables directly from ACCESS into an Oracle database?

Thanks,
Jason

 

[BNPMike]

For each table, write a query to adjust any data formats and then use Export. You can then use Oracle Load to load the data into your new tables.

Your other option is to link to the Oracle database with ODBC and use Access to insert the records with an append query.