Using Automation to Connect to a Microsoft Access Database

When working with Microsoft Access from Excel (or another application) using Automation, take the following steps:

1. Set a reference to the Microsoft Access 10.0 Object Library. (Refer to "Setting Up References to Object Libraries" earlier in this chapter).

2. Declare an object variable to represent the Microsoft Access Application object.

Dim objAccess As Access.Application

In this declaration line, objAccess is the name of the object variable, and Access.Application qualifies the object variable with the name of the Visual Basic object library that supplies the object.

3. Return the reference to the Application object and assign that reference to the object variable. Return the reference to the Application object using the CreateObject function, GetObject function, or the New keyword. Assign the reference to the object variable with the Set statement.

Dim objAccess As Object

Set objAccess = Create0bject("Access.Application.10")

Use the CreateObject function to return a reference to the Application object when there is no current instance of the object. If Microsoft Access is already running, a new instance is started and the specified object is created.

Dim objAccess As Object

Set objAccess = Get0bject(, "Access.Application.10") or

Set Set objAccess = Get0bject("C:\Program Files\ _

& "Microsoft 0ffice\0ffice\Samples\Northwind.mdb")

Use the GetObject function to return a reference to the Application object to use the current instance of Microsoft Access or to start Microsoft Access and have it load a file. (For more information, see Tip 15-2.)

Dim objAccess As New Access.Application

The above statement uses the New keyword to declare an object variable, return a reference to the Application object, and assign the reference to the object variable, all in one step.

You can also declare an object variable using the two-step method, which gives more control over the object:

Dim objAccess As Access.Application Set objAccess = New Access.Application

■ When you declare the object variable with the New keyword, the Access application does not start until you begin working with the object variable in your VBA code.

■ When you use the New keyword to declare the Application object variable, a new instance of Microsoft Access is created automatically and you don't need to use the CreateObject function.

■ Using the New keyword to create a new instance of the Application object is faster than using the CreateObject function.

Because a computer can have more than one version of Microsoft Access installed, include the version number in the argument of the GetObject or CreateObject function. The last four versions of Microsoft Access are shown below:

Microsoft Access 2002 Access.Application.10 Microsoft Access 2000 Access.Application.9 Microsoft Access 97 Access.Application.8

Microsoft Access 95 Access.Application.7

Once you've created a new instance of the Application class by using one of the methods outlined in step 3, you can open a database or create a new database with the help of the OpenCurrentDatabase or NewCurrent-Database methods. You can close the Microsoft Access database that you opened through Automation by using the CloseCurrentDatabase method.

Tip 15-2: Arguments of the GetObject Function

The first argument of the GetObject function—Pathname—is optional. It is used when you want to work with an object in a specific file. The second argument—Class—is required and specifies which application creates the object and what type of object it is. When the first argument is optional and the second argument is required, you must place a comma in the position of the first argument, as shown below:

Dim objAccess As Object Set objAccess = GetObject(, Access. _ Application.10")

Because the first argument (Pathname) of the GetObject function is omitted, a reference to an existing instance of the Microsoft Access Application class is returned.

Dim objAccess As Object Set objAccess = GetObject("C:\Program_ Files\ & "Microsoft Office\Office_ " \Samples\Northwind.mdb")

When the first argument of the GetObject function is the name of a database file, a new instance of the Microsoft Access Application class is activated or created with the specific database.

Now that you know how to create an object variable that represents the Application object, let's take a look at an example procedure that opens an Access database straight from an Excel VBA procedure.

The AccessViaAutomation procedure shown on the next page opens the sample Northwind database that ships with Microsoft Access. This procedure will use a current instance of the Access automation server, if it is available. If Access isn't running, a run-time error will occur and the object variable will be set to Nothing. By placing the On Error Resume Next statement inside this procedure, you can trap this error. Therefore, if Access isn't running, a new instance of Access will be started. This particular example uses the New keyword to start a new instance of Access. As mentioned earlier, instead of creating a new object instance with the New keyword, you can use the CreateObject() function to start a new instance of an Automation server, as illustrated below:

Set objAccess = GetObject(, "Access.Application.10") If objAccess Is Nothing Then

Set objAccess = Create0bject("Access.Application.10") End If

When you launch Microsoft Access using Automation, you will see the Microsoft Access program icon on the taskbar. The Visible property of the Access Application object is set to False. To restore the application window, set the Visible property to True.

While in use, objects consume memory and system resources. To free the resources, always close the object when you've finished working with it. The example procedure demonstrated below first closes the Northwind database by using the CloseCurrentDatabase method. Next, the Access application object is closed with the Quit method. After closing the object, you should also set the object variable to the Nothing keyword to free the memory resources used by the variable.

You can prevent an instance of Microsoft Access from closing by making an object variable a module-level variable rather than declaring it at the procedure level.

Under these circumstances, the connection to the database will remain open until you close the Automation controller (Excel) or use the Quit method in your VBA code.

Sub AccessViaAutomation()

Dim objAccess As Access.Application Dim strPath As String

On Error Resume Next

Set objAccess = Get0bject(, "Access.Application.9") If objAccess Is Nothing Then

' Get a reference to the Access Application object Set objAccess = New Access.Application End If strPath = "C:\Program Files\Microsoft 0ffice\" _

& "0ffice\Samples\northwind.mdb" ' Open the Northwind database With objAccess

.OpenCurrentDatabase strPath

If MsgBox("Do you want to make the Access " & vbCrLf _ & "Application visible?", vbYesNo, _ "Display Access") = vbYes Then .Visible = True

MsgBox "Notice the Access Application icon " _ & "now appears on the Windows taskbar."

End If

' Close the database and quit Access

.CloseCurrentDatabase .Quit End With

Set objAccess = Nothing End Sub

Run the above procedure by stepping through the code with the F8 key.

Tip 15-3: Opening a Secured Microsoft Access Database

If the Access database is secured with a password, the user will be prompted to enter the correct password. You must use Data Access Objects (DAO) or ActiveX Data Access (ADO) to programmatically open a password-protected Microsoft Access database. The following example uses the DBEngine property of the Microsoft Access object to specify the password of the database. For this procedure to work, you must set up a reference to the Microsoft DAO 3.6 Object Library, as explained in the beginning of this chapter.

Sub OpenSecuredDB() Static objAccess As _ Access.Application Dim db As DAO.Database

Dim strDb As String strDb = "C:\Program Files\Microsoft_ Office\" & "Office\Samples\ _ Northwind.mdb"

Set objAccess = New _ Access.Application Set db = objAccess.DBEngine.Open _ Database(Name:=strDb, _ Options:=False, _ ReadOnly:=False, _ Connect:=";PWD=test") With objAccess .Visible = True .OpenCurrentDatabase strDb End With db.Close

Set db = Nothing End Sub

+1 0

Responses

  • hugo
    How to automate access database?
    12 days ago

Post a comment