AutoIt – Join to Access Databases – mdb & Accdb Information

Ever question how to hook up to from the AutoIt to a MS Obtain database? AutoIt, for those who don’t know, is a system to automate keystrokes and mouse actions. This is in reference to a weblog article with regards to a challenge I experienced for connecting to Accessibility from AutoIt. There are two distinctive file varieties in MS Access 2007. Accessibility 2003, 2000 and older version will most use a .mdb file kind while, Accessibility 2007 can use .accdb file extension/style. I am going to display some basic code that will function with each variations. For this instance, I will only retrieve a person report and a single subject.

1st let us first develop some variables that will maintain the databases file identify (irrespective of whether .mdb or .accdb), the desk title and the query to execute:

$dbname = “C:UsersvkDocumentsdbMarketingarticleSubmissionsTutorialRef.mdb”
$tblname = “articles or blog posts”
$question = “Pick * FROM ” & $tblname & ” Where articleID = 4″

The & is basically a concatenation of the strings.

Let us set the variable for the 1 subject that we want to retrieve from the databases.

Neighborhood $title

Pretty easy so far, isn’t it?

Then create the connection to the ADODB:

$adoCon = ObjCreate(“ADODB.Connection”)

Then set the Service provider. There is a unique Provider for each individual file extension. A .mdb file will have its own Supplier and a .accdb file will have a different.

In this article is the Provider for a .mdb file:

$adoCon.Open up(“Driver=Microsoft Obtain Driver (*.mdb) DBQ=” & $dbname)

Right here is the Service provider for .accdb file:

$adoCon.Open up (“Supplier=Microsoft.Jet.OLEDB.4. Information Supply=” & $dbname)

Now let us produce the object Recordset, set some needed solutions and then execute the query:

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

A several notes on the CursorType and LockType properties. I required to established these in get to retrieve the specified field’s value.

Environment the CursorType to a benefit of ‘1’ suggests ‘adOpenKeyset’. CursorType is outlined on the MSDN as: “Sets or returns a CursorTypeEnum worth. The default price is ‘adOpenForwardOnly’. Use the CursorType residence to specify the style of cursor that need to be used when opening the Recordset item.” The adOpenKeyset is outlined on the MSDN Microsoft web-site as: “Works by using a keyset cursor. Like a dynamic cursor, apart from that you can not see information that other people increase, even though records that other users delete are inaccessible from your Recordset. Information modifications by other consumers are still seen”.

Oddly plenty of, when I attempted to set the CursorType to a value of ‘2’ which usually means ‘adOpenDynamic’ AutoIt could not retrieve the field’s price. It was just blank but no error transpired. ‘adOpenDynamic’ is outlined as: “Takes advantage of a dynamic cursor. Additions, adjustments, and deletions by other users are seen, and all styles of motion through the Recordset are permitted, besides for bookmarks, if the company does not aid them”.

LockType is outlined as “Indicates the form of locks put on information for the duration of enhancing. Sets or returns a LockTypeEnum price. The default value is adLockReadOnly.” With it truly is benefit set at ‘3’ which suggests ‘adLockOptimistic’. ‘adLockOptimistic’ is “Implies optimistic locking, history by history. The service provider makes use of optimistic locking, locking records only when you simply call the Update technique” which is very good so we leave it at that worth.

Then we simply just spot the consequence into the $title variable we declared earlier. You can use possibly of the two subsequent strains. The first is the retrieving the value by the field’s identify and the latter is retrieving it by the field’s numerical sequence. Fundamentally, it can be the column’s quantity. Column figures commences at ‘0’ (i.e. zero) and not ‘1’ (i.e. just one). So if you desire to grab the 3rd column’s benefit then you would use the worth ‘2’.:

$title = $adoRs.Fields(“title”).price

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

Close the link until you have a particular explanation for leaving your link open up:

$adoCon.Close

Then exam the success with the MsgBox():

MsgBox(,”tests”,$title)

So below is the overall code:

Nearby $title
$adoCon = ObjCreate(“ADODB.Link”)
$adoCon.Open(“Driver=Microsoft Entry Driver (*.mdb) DBQ=” & $dbname) Use this line if utilizing MS Entry 2003 and decreased
$adoCon.Open (“Company=Microsoft.Jet.OLEDB.4. Facts Source=” & $dbname) Use this line if making use of MS Obtain 2007 and applying the .accdb file extension
$adoRs = ObjCreate (“ADODB.Recordset”)
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open up ($question, $adoCon)
$title = $adoRs.Fields(“title”).worth Retrieve benefit by discipline name
$title = $adoRs.Fields(2).benefit Retrieve value by column selection
$adoCon.Near
MsgBox(,”testing”,$title)

In this write-up, we confirmed how to link to an Access 2007 or 2003 database using AutoIt. This post discusses how to retrieve a single file or row and only one particular subject. In a future post, we’ll explain how to retrieve multiple documents in an array.