How to Use SQL Statements with VBScript

Using VBScript to Insert, Update, Delete and Select in a Database

Dec 31, 2008

The contents of a database (such as Microsoft Access) can be examined and modified by using VBScript and the insert, update, delete and select statements.

When working with Microsoft Access databases (or any other databases, for that matter) there are 4 types of SQL statements (or queries) that can be used to work with the information in the database, and these SQL statements are:

And all of these SQL statements can be used with VBScript - however, the first step is to connect to the database.

Connecting to a Microsoft Access Database

A programmer can easily use VBScript to make a connection to a Microsoft Access database by making use of one of Microsoft's Active Data Objects:

dim connection_string : connection_string = _

"provider=microsoft.jet.oledb.4.0;" _

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

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

conn.open connection_string

In this example a connection is made to the database using the Microsoft Jet engine and accesses a Microsoft .mdb file. Then, with the connection in place, the programmer can then use VBScript to run queries and work with the contents of the database

The Select Query

The select query is perhaps the most commonly used query - this extracts information from the database and, in the case of VBScript, any results from the query are loaded into a recordset - in this example the contents of the id field from the name table are loaded into the recordset:

dim sql : sql = "select id from name"

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

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

rs.open sql, conn, 3, 3

'A static copy of a set of records, locked only when updating

The contents of the recordset can then be used in VBScript - in this case to obtain a new id number for use in the database's name table:

rs.movelast

dim id

if not rs.eof then

id = rs.Fields(0) + 1

else

id = 1

end if

msgbox id

The Insert Statement

dim surname : surname = "jones"

dim firstname : firstname = "john"

sql = _

"insert into name (id, surname,firstname)" _

& " values (" & id & ",'" & surname & "','" & firstname & "')"

conn.Execute sql

The insert statement is different from the select statement in that there is no result returned to VBScript - another select statement would have to be used in order to examine the new contents of the table.

The Delete Statement

The insert statement adds a record to a table and so, of course, the delete statement removes a record:

sql = _

"delete from name" _

& " where surname = '" & surname & "'" _

& " and firstname = '" & firstname & "'"

conn.Execute sql

The Update Query

The final statement - update - modifies a record in a database table, and so this next example inserts and then updates a record:

surname = "smith"

firstname = "jane"

sql = _

"insert into name (id, surname,firstname)" _

& " values (" & id & ",'" & surname & "','" & firstname & "')"

conn.Execute sql

dim new_surname : new_surname = "smyth"

sql = _

"update name" _

& " set surname = '" & new_surname & "'" _

& " where surname = '" & surname & "'" _

& " and firstname = '" & firstname & "'"

conn.Execute sql

And again no result would be returned and a select statement would be needed for the updated contents to be viewed.

Summary

The 4 types of SQL statements (or queries) used with databases, such as Microsoft Access, are:

Each can be used with VBScript by making use of Microsoft's Active Data Objects which make data manipulation very easy:

The copyright of the article How to Use SQL Statements with VBScript in Database Programming is owned by Mark Alexander Bain. Permission to republish How to Use SQL Statements with VBScript in print or online must be granted by the author in writing.