Create database from SQL script or multiple tables with SQL script

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
First off, I am an Access newbie. I have a DDL SQL query script that creates many different tables in a database. But, all I can find is a make table query that only works one table at a time.

So, my first question is how do a create a database's tables from one DDL SQL script?

Second question is there a way to use the DDL SQL to create a database and all its tables in one step?

I could create the database using ACCESS methods. Then I would want to create all the tables at once.

I am much better at Excel, mostly because the help I've received over the years from the very helpful folks in the Excel Forum of MrExcel.com. I'm hoping to get some of the same kind of help here.

Thanks,

George Teachman
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,612
Office Version
2013
Platform
Windows
First off, I am an Access newbie. I have a DDL SQL query script that creates many different tables in a database. But, all I can find is a make table query that only works one table at a time.
I'm not sure what you mean. You are saying that you have a script that creates many tables (so far so good). But then all you can find is a make table query to works on one table at at time? What does that mean?
 

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
Sorry for the lack of clarity. In the create tab in ACCESS a user can create table(s) using the interface and make that process into a MAKE TABLE Query. So, that the query can be run directly as an SQL script. But, I haven't been able to get that process to create more than one table at a time.

Does this make it more clear?

Thanks,

George Teachman
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,612
Office Version
2013
Platform
Windows
You can write a script (?) that runs all your make table scripts, basically. If that's what you want. A make-table query is not technically the same as a create table script. Generally in Access people do not use scripts to create tables (it's more of a gui-driven platform than a script-driven platform).

Not sure what your use-case scenario is. Also possibly is to have a temp table available for use rather than having users creating new tables.
 
Last edited:

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
I have a DDL SQL script created by another SQL database (My SQL Server) and I'd like to see what it looks like in Access.

Thanks,

George Teachman
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,612
Office Version
2013
Platform
Windows
I have a DDL SQL script created by another SQL database (My SQL Server) and I'd like to see what it looks like in Access.
Specifically, a MySQLServer script as-is will almost certain to be incompatible with MSAccess. In general, as I said, you won't see scripts for MSAccess databases very often although it is possible to do with ADO or DAO vba scripts, and to some extent with Access DDL scripts (although Access DDL doesn't have the fine granularity that you would see in other database DDL languages).

In addition to the above link, you can see here for examples of using DAO for creating tables:
http://allenbrowne.com/func-DAO.html
http://www.functionx.com/vbaccess2013/table/dao/create.htm

and here for ADO:
http://allenbrowne.com/func-ADO.html
http://www.mahipalreddy.com/vb/article86695.htm

And here for plain DDL:
https://docs.microsoft.com/en-us/of...e/create-table-statement-microsoft-access-sql

But for example with Access you cannot click a button to generate a script for your entire database, as you can do in MySQL. Or even for any individual tables.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,468
Messages
5,414,703
Members
403,541
Latest member
J0hnJ

This Week's Hot Topics

Top