Create database from SQL script or multiple tables with SQL script

[teachman]

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

 

[xenou]

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]

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]

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.

 

[teachman]

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

 

[stumac]

It isn't possible in access SQL , however, it could be run from VBA.

There is a pinned post on this forum that shows you something similar.

https://www.mrexcel.com/forum/microsoft-access/616526-access-table-recreator.html

 

[xenou]

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.