One of the exciting aspects of using Microsoft Access on a Windows XP or Vista computer is that Microsoft Access doesn’t even have to be installed for it to be used. All that the computer user needs is a text editor and some VBScript code. By using VBScript the would be database designer can:
create a new database file (a .mdb file)
add tables to the database
update the contents of the database
run queries on the database
The great thing is that they will not need to have any additional software in order to do all of this.
Creating a Microsoft Access Database File
It is surprisingly simple to create the .mdb file for a Microsoft Access database – the Microsoft Access application is not needed – all that’s needed is XP and Vista’s inbuilt Active Data Objects Extension (ADOX):
dim datasource : datasource = “provider=microsoft.jet.oledb.4.0;” _
& “data source=c:\customers.mdb”
dim catalog : set catalog = createobject(“adox.catalog”)
catalog.create datasource
If this code were to be saved into a .vbs file and then run then the user would find a new file c:/customers.mdb. Obviously they would be unable to open the file or to add tables to the database (unless they had Microsoft Access installed) – however, that’s not a problem because ADOX can create any tables that are required.
Creating a Table in a Microsoft Access Database
Having created the .mdb file the next step is to create at least one table for the database, and the table will need:
a name
at least one field
a primary key
and this is all achieved by using the ADOX again
dim new_table : set new_table = createobject(“adox.table”)
new_table.Name = “name”
new_table.Columns.Append “id”, 3 ‘Integer Field
new_table.Columns.Append “surname”, 202, 30 ‘Varchar field, 30 characters wide
new_table.Columns.Append “firstname”, 202, 30
new_table.Keys.Append “pk_name_id”, 1, “id”
catalog.Tables.Append new_table
If the database could now be viewed then it would be found to contain one (empty) table -obviously that needs some data.
Populating a Microsoft Access Table
ADOX is only used for the creation of the table, but another ADO is used – ADODB – and this is used to run queries on the database, such as an insert query:
dim conn : set conn = createobject(“adodb.connection”)
conn.open datasource
conn.Execute “insert into name (id, surname,firstname)” _
& ” values (1,’smith’,’fred’)”
If the table could be examined then it would be found to contain a single record – since this can’t be be done using Microsoft Access it must be done by using VBScript.
Examining Data in a Microsoft Access Database
As before the ADODB is used to run a query on the database, but this time a select query is run, and this will return a recordset:
dim rs : set rs = createobject(“adodb.recordset”)
rs.cursorlocation = 3 ‘Use a client-side cursor
rs.open “select * from name” , conn, 3, 3
‘A static copy of a set of records, locked only when updating
The recordset will now contain the results from the select query, and these can be processed by using VBScript:
rs.movefirst
while not rs.eof
msgbox rs.Fields(2) & ” ” & rs.Fields(1)
rs.movenext
wend
In this simple example the name of every person in the customer database will be displayed one by one.
Summary
Microsoft Access does not need to be installed on a Windows XP or Vista computer for its functionality to be used by VBScript. That’s because the Microsoft Access functionality is built into the Microsoft ADOs (Active Data Objects), in particular:
ADOX – used to create the .mdb file and the database tables
ADODB – used to run queries on the database and to access recordsets returned by the queries
By using these the programmer can use the functionality of Microsoft Access without having Microsoft Access.
