access: getting create table string

Question

i have a table in access and i would like to get the SQL string from it that will generate the table like:

CREATE TABLE example (
         id INT,
         data VARCHAR(100)
       );

is there any way to do this?

Answer 1

More or less as you have it:

CREATE TABLE example (
         id INT,
         data text(100)
       );

You may wish to check out DAO data types: http://allenbrowne.com/ser-49.html

Answer 2

I don't believe there is a built in way. You will have to use a third party tool to convert the schema:

• DBWScript
• MDBScript

To run a VB Script to convert the tables there is on here: http://stackoverflow.com/questions/172895/table-creation-ddl-from-microsoft-access

Answer 3

If you're trying to port this to SQL server or the like, I think you'll have to build the scripts by hand.

You could always use the SQL server import wizard (or the export to SQL from Access) to move it over, then create the scripts in SQL server.

Don't forget, you can usually get SQL Express for free, so that's a way to do things.

Answer 4

If you are talking about a generic method that will work on any Access table I don't know of any way to get a SQL CREATE table statement directly. I suspect there are too many features in Access (drop down values for fields, input masks, etc.) that don't translate well to SQL.

Access does have the ability to export the table directly to SQL Server however. You could try to push the table to SQL Server and then generate the CREATE statement from that.