Create database from SQL script or multiple tables with SQL script

teachman

Active Member
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

MrExcel MVP, Moderator
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
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
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
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
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/office/client-developer/access/desktop-database-reference/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:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top