How can I import a database schema into MS Access 2003 from sql text file?

Question

I chave a db schema generated in a text file (DDL - MS Access compliant).

Where is the option in MS Acces to import that damn schema into na empry database ?

Answer 1

I'm not aware of any import for DDL.

However, DDL contains the definition for the schema.

You simply have the execute DDL as you would any query.

Either create a query, put it in sql mode, paste your ddl, and execute

or....

Create a VBA Sub to essentially do the same: currentdb.execute SQL

Good Luck

Answer 2

To execute a SQL DDL in the SQL View of a Query object, you may need to change the Access user interface to ANSI-92 Query Mode. While the 'traditional' query mode (ANSI-89 Query Mode) supports a SQL DDL syntax it is very limited.

The Access database engine can only execute one SQL statement (DML, DDL or DCL) at a time. To execute a SQL script consisting of multiple SQL statement, you need something to parse individual SQL statements, so it really helps if your script has semicolon ; characters separating them, then execute each statement on at a time i.e. synchronously. If you are doing this in VBA code you are better off using ADO because it always uses ANSI-92 Query Mode.

Answer 3

Is it possible to create the database using the DDL in something like SQL Server. Then import the database schema into Access?

Answer 4

See if this helps: http://support.microsoft.com/kb/180841

Answer 5

I have been very succesful with reverse / forward engineering MS Access databases with Dezign for Databases by Datanamic. It reads all kinds of DDL scripts (from almost all available database) and can translate between different databases. There is a free trial available.