CODEWITHSUNDEEP
sqlms-access
Alex Gordon
Alex Gordon

Reputation: 60902

access: getting create table string

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?

Upvotes: 2

Views: 4891

Answers (5)

Ivan Ferrer Villa
Ivan Ferrer Villa

Reputation: 2158

May be exporting the tables to XML/XSD? It's not DDL but you have the schema in a file that you can import using other tools.

Upvotes: 1

Glennular
Glennular

Reputation: 18225

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

To run a VB Script to convert the tables there is on here: Table Creation DDL from Microsoft Access

Upvotes: 4

TLiebe
TLiebe

Reputation: 7996

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.

Upvotes: 2

jcolebrand
jcolebrand

Reputation: 16035

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.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: -1

Related Questions