How to Create an Access Database with VBScript

Use VBScript to Automate Microsoft Access

Dec 23, 2008
Mark Alexander Bain

Automate Microsoft Access with VBScript - Mark Alexander Bain

There is no need for a database designer to manually create a Microsoft Access database - they can automate the process using VBScript.

Microsoft Access has been the database of choice for many people for many years - and that's not really surprising because it's such an easy database to use, both for the general user and the database developer. Take, for instance, creating a database and tables from scratch - this can be done with just a few lines of VBScript code.

Creating a VBScript File

The beauty of a VBScript application is that it is simple a text file with a .vbs extension and can, therefore, be created with a text editor such as Notepad or Notepad++. Once the file has been saved then it can be run by double clicking on it.

Preparing a Computer for Use with a Database

The computer will, of course, need Microsoft Access installed and the computer will also need MDAC (Microsoft DataAccess Objects which is can be downloaded from the Microsoft Dowload Center). Once both Microsoft Access and MDAC have been installed then the computer is ready for database automation.

Creating a Microsoft Access File

The MDAC ADOX objects uses the Microsoft Jet engine to create the database file and so this and the database file name need to be saved as variables:

dim provider : provider = "microsoft.jet.oledb.4.0"

dim db : db = "c:\work.mdb"

dim ds : ds = "provider=" & provider & "; data source=" & db

The MDAC's ADOX object can then be used to create the database file:

dim catalog : set catalog = createobject("adox.catalog")

catalog.create ds

Next any required tables can be added to the database file.

Creating a Microsoft Access Database Table

MDAC's ADOX object is used to create any tables as well as the database file, and to do that a few constants are needed:

const adInteger = 3 'Integer

const adVarChar = 202 'Variable Character

The table can now be defined:

dim new_table : set new_table = createobject("adox.table")

new_table.Name = "customer"

as can any columns:

new_table.columns.append "id", adInteger

new_table.columns.append "surname", adVarChar, 30

and any indexes:

new_table.keys.append "pk_cust_id", 1, "id" 'unique id

and then the table can be created in the database:

catalog.Tables.Append new_table

Finally any memory that's been used can be freed:

set new_table = nothing

set catalog = nothing

With the table in place it can be populated with data.

Populating a Microsoft Access Database Table

A MDAC ADODB's connection is used to populate the table and so the next step is to create the connection:

dim conn : set conn = createobject("adodb.connection")

conn.open ds

Now the data can be inserted using a standard SQL statement:

sql = "insert into customer (id, surname) values (1,'smith')"

conn.Execute sql

It's worth noting that the id must be unique (so, for example, the next record should have an id of 2) and once all of the records have been loaded then again any memory that's been used can be reclaimed:

conn.close

set conn = nothing

At the end of the process the table will contain a new record.

Summary

Microsoft's MDAC ADOX object is used to created the Microsoft Access database file and any tables, and then the MDAC ADODB object can be used to load any required data into the table.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...