2

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?

Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • You should edit your question to reflect all the additional information you have provided in comments. Otherwise, there's no way for anyone to provide you with a useful answer. At this point, I'm not even certain what your actual question is. I'm withholding a downvote on the question to give you an opportunity to refine it. – David-W-Fenton May 12 '10 at 22:09

5 Answers5

4

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

Community
  • 1
  • 1
Glennular
  • 17,827
  • 9
  • 58
  • 77
  • You can run DDL in the query design window, SQL view, or in VBA. There is no need for these tools. – Fionnuala May 12 '10 at 16:33
  • I do not see why this answer was voted up, when it is incorrect. At very least, the opening sentence should be corrected. – Fionnuala May 12 '10 at 16:38
  • 1
    Remou - How do you open an already defined table in Access and create the script to export the table definition? Yes, you can write a "create table" script in the query editor in Access, however you cannot right click a table and choose to have the script generated. – jcolebrand May 12 '10 at 16:57
  • None of this appears in the question which simply asks how to run DDL in Access. Additional information in a comments shows it is needed for MySQL, but that is not part of the question, so how is right-clicking to create a script relevant to the question that was asked? This answer says "I don't believe there is a built in way", but that is not true, you can use the query design window to run DDL. – Fionnuala May 12 '10 at 18:34
2

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.

TLiebe
  • 7,913
  • 1
  • 23
  • 28
  • Why this harking on SQL Server, it is not mentioned in the question? Furthermore, look-up fields in tables should be avoided. The main problems are with the hyperlink type (which is a really a memo, and a nuisance) and memo type, which is useful, but may not be necessary. – Fionnuala May 12 '10 at 16:43
  • I never suggested that look-up fields in tables were a good idea, I just said that they are one reason an Access table may not be directly convertible to a simple CREATE statement. – TLiebe May 12 '10 at 17:00
  • You can export from Jet/ACE via the Access UI to any ODBC database if the database already exists and has a DSN defined. – David-W-Fenton May 12 '10 at 22:11
1

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.

jcolebrand
  • 15,889
  • 12
  • 75
  • 121
  • Where is the question is SQL Server mentioned? There are reasons why one might wish to run DDL in Access with no reference to any other database. – Fionnuala May 12 '10 at 16:37
  • 2
    It wasn't mentioned, I made an ASSumption. I also suggested a manner by which he might easily (and freely) create the scripts (via SQL Express) and it might already be something he has installed (if he has Visual Studio). Just trying to think outside the box and look for nonobvious answers, since "purchase a tool to do it for you" is so oftly quoted. – jcolebrand May 12 '10 at 16:55
1

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.

Ivan Ferrer Villa
  • 2,129
  • 1
  • 26
  • 23
-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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Switch to SQL View in the query design window and type or paste your SQL, then choose run or the large red exclamation point from the menu or toolbar. Alternatively, build the strings in VBA and use Execute. – Fionnuala May 12 '10 at 16:40
  • 2
    remou, i have a table, and i want to generate create table SQL for it for mysql – Alex Gordon May 12 '10 at 16:43
  • If you wish to go to MySQL, why not use http://dev.mysql.com/doc/migration-toolkit/en/index.html? If that is not possible, you can use Access http://wiki.lessthandot.com/index.php/Transfer_Access_Database_or_Table_to_MySQL – Fionnuala May 12 '10 at 16:46
  • 1
    If you have a DSN defined for an existing MySQL database, you can export Access tables via the Access menus, selecting each table in turn and choosing EXPORT from the file menu. The data types may need refining after export, though. – David-W-Fenton May 12 '10 at 22:08