Some Access Tools

Act Three

Hey, Rocky. Watch me while I pull an MDB out of my hat!

The ability to create a database on a SQL server, from a SQL dump generated from an Access database, is cool. It also occurs to me that being able to create a fresh new MDB from that same file, is going to be useful about every second time you want to cut an install set for your VB database app. However, Access doesn't provide a way to easily do this.

Write it once with VB, however, and you have a cool tool for creating blank MDBs. So I wrote the VB app. When an opportunity comes for you to write a cool tool, seize the day. It took me about forty minutes to write… I wouldn't bother counting how much time it would save in the long run.

We have a defined file format, and we have all the information we need in that file in the form of pre-built SQL statements. We also have a cool VB function called CreateDatabase. A snazzy little function call that creates an empty MDB with only system tables.

The CreateDatabase call looks like this:

  Set dbNew = CreateDatabase("C:\VB\BIBLIO2.MDB", DB_LANG_GENERAL)

The same call returns a VB database object, all ready for us to execute our SQL statements. The trick is this - Access does not support the `CREATE PROC procname. . .' SQL statement (I'm still waiting, Microsoft). Therefore, we need to do a little parsing to be able to create our QueryDefs from what is in the text file. However, we know what the structure is, so it is a relatively simple matter. The other consideration is that the SQL for QueryDefs is usefully (?) presented to us by Access as multiple lines, whereas the TableDef and Index SQL statements we built were expressed on one line. Fortunately Access also appends a semicolon to each SQL statement so we have a handy delimiter (I never really liked that semi-colon before).

We add a little code to handle and report any errors to the user. We simply add them to a listbox, quoting the line number in the file. Apart from that, the code is really very straightforward. On the front end all we need is a couple of text boxes for paths and browse buttons, as well as our error listbox.

All the code is in the MDBMaint Visual Basic Project included in acctools.zip (12kb). The Access Basic code is in the text file, DDL.TXT. A known limitation of this VB app is that it cannot create QueryDefs in your destination database that reference System tables. However, these will produce a permission error and any such QueryDefs can be created in the database manually, after all the SQL statement is on hand in your text file. I have also included the SQL text file for the BIBLIO.MDB that ships with VB3, just so you can have a look at what the text files look like with a known database as the reference.



[HOME] [TABLE OF CONTENTS] [SEARCH]