Grafton Falls News

Add Record Microsoft Access

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.

About the Author

AutoIt Tutorial, tips, guides. Learn AutoIt tips. Victor Kimura
Vista Tutorial Please view the original article to properly view the code and step-by-step snapshots:AutoIt:Connect to Access Database

Using Microsoft Access and need some help?

I am using the 2000 version (I work for a University, we haven't updated yet) and I am trying to figure out how to add the records I am typing in one document directly into another document without copying them over. I figure there has to be a way. The reason is because I input new papers received in one file to check against anther document, but then I have to go back through and copy those entries into the overall document. Can anyone help me?

If I understand this correctly you type in information to one table, then copy it to another. Your use of the word document is why I'm asking, are you using MS-Word, or Access.

If you are using Access, just link the two tables together for the repetitive information, this way you only type it once. If your using Word you will need to create a macro within a template to do this.

Hope this helps.

Add Record Microsoft Access
Computer Training : How to Create & Delete Records in Microsoft Access

Ten Simple Tips for Microsoft Access 2010

Although perhaps not as widely used as Word or Excel, Access is the most powerful program in theMicrosoft Office suite. Access is a flexible and relatively simple way to create databases to store, manage and enter data. It even exceeds the capabilities of Excel. Access provides a simple yet robust way to manage your data. Simple, of course, is a relative term. But these ten tips can make life with Access simpler.

Access databases can range from a simple application like a mom & pop inventory tracker to an enterprise app that integrates with SQL servers and contains thousands of records. Access's power has increased significantly with the release of Access 2010. The most exciting new feature for many is the ability to build and publish Web databases. There are a lot more new capabilities, too, such as Web Browser Control, the Image Gallery, and several powerful developer enhancements. Access 2010 also features the Ribbon interface for quick access to commands and menu options. Backstage View is now included as well, giving users a centralized way to perform common database management tasks.

All these capabilities may make Access too high-end for the average IT tinkerer. But you don't have to be a programmer to design a robust Web application or local database. Access includes a variety of templates to get you started in just a few clicks. These ten tips can help, too. While they assume a basic level of knowledge, some of the tips are dead simple, and require little more than clicking a command on the Ribbon. Some involve creating a couple lines of code. They all, however, are great ways to help you add some punch to your databases and get the most you can from Access. Click through the slideshow below to get some quick, simple tips for Access.

Other Business News:laptop battery,toshiba pa3534u-1brs battery,Sony NP-F960 Battery

 

About the Author

We specialize in laptop battery and laptop ac adapter. Give your laptop a new life with higher capacity battery. Each model was engineered for maximum run time so you won't miss those important shots. We are a full service laptop battery distributor offering wholesale price to our customers. We are working hard to make your online shopping easy, fast, convenient and Safe. Please let us know what you think. We are always here to serve your needs and provide quality service. Our products range include replacement computer batteries for most major laptop brands, including Dell, Apple, Compaq/HP, IBM/Lenovo, Fujitsu, Gateway, Sony and Toshiba.

Add Record Microsoft Access