SQL Statement to build database

[ChipperT]

This is actually two questions but they are related. I am fairly new to Access - well, coming back. I did a bit of work in Access 97. Most of my development experience has been in SQL Server. There I am used to generating SQL code for several reasons. First, it is easy to document a db that way, second, it is a great disaster-recovery, re-implementation tool, and third, it is a good way to move dbs initally. I will sometimes even do it for an intial load of data, dropping an old database, re-creating the new structure, and loading the data into the new structure. Now all that said:

1. How would I generate SQL code for tables in an Access 2007 db (or even for the entire DB)?

2. Assuming I could do that, could I execute the SQL Code (maybe using DoCMD.SQL) within VBA?

I am thinking this might be a good way to update remote stand-alone sites when necessary.

Thank you for your help.

 

[stopher]

1. How would I generate SQL code for tables in an Access 2007 db (or even for the entire DB)?

I think the only obvious thing that has changed for creating queries since '97 is the menu! In 2007 you can enter SQL by choosing the Design tab from the ribbon then clicking on the SQL button.

If you're lazy and less skilled at SQL (like me) then you can use the Query Designer (Create tab => Query Design)

2. Assuming I could do that, could I execute the SQL Code (maybe using DoCMD.SQL) within VBA?

Yes. Your code might look something like this:

Dim mySQL As String
mySQL = "my sql expression here"
DoCmd.RunSQL mySQL

You could also run a query that you had previously saved (see 1):

DoCmd.OpenQuery "my query name here"

hth
Chris

 

[ChipperT]

Thanks for your reply, Chris. However I cannot see the SQL for Tables (there is no SQL view that I can find). I could go through and manually script each table I know but this legacy project I have inherited has a LOT of intertwined tables. It would be very helpful if I could just have Access generate the SQL script for me, as it can for queries (and as SQL Server can for entire databases). Am I missing something somewhere?

 

[Galaxiom]

Create a query and convert it to a Make Table query.

Otherwise you export a table with VBA.
http://www.techonthenet.com/access/questions/new_mdb.php

 

[stopher]

Thanks for your reply, Chris. However I cannot see the SQL for Tables (there is no SQL view that I can find). I could go through and manually script each table I know but this legacy project I have inherited has a LOT of intertwined tables. It would be very helpful if I could just have Access generate the SQL script for me, as it can for queries (and as SQL Server can for entire databases). Am I missing something somewhere?

I'm probably missing something as I don't have any knowledge of SQL Server and hence I'm probably not understanding what you are asking for.

You can create a table from scratch (I guess like you would in SQL server). Take a look here:
http://www.vb123.com/toolshed/99/createtables.htm

But I'm guessing you are looking for the ability to have a full script of Create Table queries created for you that would reproduce the whole database structure? I can't think why you would really need this in Access. It's easy to backup/export/import tables and you also have the documenter. Also, the relationship view is very very useful.

hth
Chris

 

[ChipperT]

I have a good reason (at least I think it is). I have an application that is distributed to disconnected users across several states. The data itself is maintained centrally and cannot be changed by site users, but since the users are disconnected, periodically there is a need to "refresh" the local datastore for each of these users with the updates, deletions, and additions to the data, which can range from dozens of changes to hundreds. Sometimes tables need to be dropped, changed or added as well. Rather than distribute this rather large database in its entirety each time, it can be preferrable to ship SQL scripts to do the work. In that case it would make life much easier to autogenerate the scripts as much as possible from Access and then tweak it for the desired results before distribution.

 

[stopher]

You could put all your queries into a new database and just ship that i.e. with no data. That would make your distribution/maintenance file pretty small. The queries can still perform maintenance on other Access databases i.e. the client database.

But of course this doesn't help you with your original Autogenerate question.

Chris