Microsoft Access Stored Queries and VBscript

How to Create and Edit a Stored Database Query

Jan 3, 2009

A programmer does not actually have to open a Microsoft Access database in order to create or modify a stored query - that can be done by using VBScript

Microsoft Access is a very commonly used database, and enables its users to run queries on the database contents. More than that Microsoft Access allows users to store useful queries meaning that the queries can be rerun whenever required without the need to rewrite them. This is also useful for any programmers who can run queries on the database rather than write them into their applications. If the programmer is using VBScript they can even write a script that will create the queries automatically.

VBScript is a powerful and freely available programming language, and is installed on every Windows computer. VBScript integrates seamlessly with Microsoft Access, and if the computer has XP or Vista then Microsoft Access does not even have to be installed in order for its functionality to be available to the programmer (see Using Microsoft Access without Microsoft Access).

It is, therefore, very easy for the programmer to create queries on a database without having to open Microsoft Access at all.

Using VBScript to Create a Query on a Microsoft Access Database

VBScript makes use of ADOX (Microsoft's Active Data Objects Extensions for Data Definition Language and Security) to create a query on a Microsoft Access database:

dim datasource : datasource = "provider=microsoft.jet.oledb.4.0;" _

& "data source=c:\customers.mdb"

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

catalog.activeconnection = datasource

dim query : set query = createobject("adodb.command")

query.commandtext = "select * from name"

catalog.views.append "qry_names", query

set query = nothing

set catalog = nothing

If the code is stored in a .vbs file and then run (by double clicking on the file) then nothing obvious will happen. However, if the the database is examined then it will be found to contain a new query named qry_names.

Running the Microsoft Access Database Query

'Make the connection

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

conn.open datasource

'Load the results of the query into a recordset

dim rs : set rs = createobject("adodb.recordset")

rs.cursorlocation = 3 'Use a client-side cursor

rs.open "qry_names" , conn

'Display the contents of the recordset

rs.movefirst

while not rs.eof

msgbox rs.Fields(2) & " " & rs.Fields(1)

rs.movenext

wend

'Free up any memory

set rs = nothing

set conn = nothing

Having created and run the query it may then be useful for the programmer to modify it.

Using VBScript to Modify a Query on a Microsoft Access Database

Once the query has been stored then it can still be edited. The programmer may do this by opening the database or by using VBScript again. The initial connection is exactly the same as when the query was created:

dim datasource : datasource = "provider=microsoft.jet.oledb.4.0;" _

& "data source=c:\customers.mdb"

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

catalog.activeconnection = datasource

dim query : set query = createobject("adodb.command")

query.commandtext = "select * from name where surname='smith'"

set catalog.views("qry_names").command = query

set query = nothing

set catalog = nothing

In this way the programmer has complete control over any stored queries in the database.

The copyright of the article Microsoft Access Stored Queries and VBscript in Database Programming is owned by Mark Alexander Bain. Permission to republish Microsoft Access Stored Queries and VBscript in print or online must be granted by the author in writing.