AutoIt:Connect to Access Databases:mdb & accdb files

Ever wonder how to connect to from the AutoIt to a MS Access database? AutoIt, for those who don't know, is a program to automate keystrokes and mouse movements. This is in reference to a blog post regarding a problem I had for connecting to Access from AutoIt. There are two different file types in MS Access 2007. Access 2003, 2000 and older version will most use a .mdb file type; whereas, Access 2007 can use .accdb file extension/type. I'll show some basic code that will work with both versions. For this example, I will only retrieve one record and one field.

First let's first create some variables that will hold the database file name (whether .mdb or .accdb), the table name and the query to execute:

$dbname = "C:UsersvkDocumentsdbMarketingarticleSubmissionsTutorialRef.mdb"
$tblname = "articles"
$query = "SELECT * FROM " & $tblname & " WHERE articleID = 4"

The & is simply a concatenation of the strings.

Let's set the variable for the one field that we want to retrieve from the database.

Local $title

Pretty straightforward so far, isn't it?

Then create the connection to the ADODB:

$adoCon = ObjCreate("ADODB.Connection")

Then set the Provider. There is a different Provider for each file extension. A .mdb file will have its own Provider and a .accdb file will have another.

Here is the Provider for a .mdb file:

$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname)

Here is the Provider for .accdb file:

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

Now let's create the object Recordset, set some required options and then execute the query:

$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open ($query, $adoCon)

A few notes on the CursorType and LockType properties. I needed to set these in order to retrieve the specified field's value.

Setting the CursorType to a value of '1' means 'adOpenKeyset'. CursorType is defined on the MSDN as: "Sets or returns a CursorTypeEnum value. The default value is 'adOpenForwardOnly'. Use the CursorType property to specify the type of cursor that should be used when opening the Recordset object." The adOpenKeyset is defined on the MSDN Microsoft site as: "Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible".

Oddly enough, when I tried to set the CursorType to a value of '2' which means 'adOpenDynamic' AutoIt could not retrieve the field's value. It was just blank but no error occurred. 'adOpenDynamic' is defined as: "Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them".

LockType is defined as "Indicates the type of locks placed on records during editing. Sets or returns a LockTypeEnum value. The default value is adLockReadOnly." With it's value set at '3' which indicates 'adLockOptimistic'. 'adLockOptimistic' is "Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method" which is good so we leave it at that value.

Then we simply place the result into the $title variable we declared earlier. You can use either of the two following lines. The first is the retrieving the value by the field's name and the latter is retrieving it by the field's numerical sequence. Essentially, it's the column's number. Column numbers starts at '0' (i.e. zero) and not '1' (i.e. one). So if you wish to grab the third column's value then you would use the value '2'.:

$title = $adoRs.Fields("title").value

$title = $adoRs.Fields(2).value

Close the connection unless you have a specific reason for leaving your connection open:

$adoCon.Close

Then test the results with the MsgBox():

MsgBox(0,"testing",$title)

So here is the entire code:

Local $title
$adoCon = ObjCreate("ADODB.Connection")
;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower
$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) ;Use this line if using MS Access 2007 and using the .accdb file extension
$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open ($query, $adoCon)
$title = $adoRs.Fields("title").value    ;Retrieve value by field name
;$title = $adoRs.Fields(2).value         ;Retrieve value by column number
$adoCon.Close
MsgBox(0,"testing",$title)

In this article, we showed how to connect to an Access 2007 or 2003 database using AutoIt. This article discusses how to retrieve one record or row and only one field. In a future article, we'll describe how to retrieve multiple records in an array.