Connecting to the Current Access Database

Microsoft Access provides a quick way to access the current database by referencing the ADO Connection object with the CurrentProject.Connection statement. This statement works only in VBA procedures created in Access. If you'd like to reuse your VBA procedures in other Microsoft Office or Visual Basic applications, you will be better off by using the standard way of creating a connection via an appropriate OLE DB provider.

The procedure in Hands-On 10-9 uses the CurrentProject.Connection statement to return a reference to the current database. Once the connection to the current database is established, the example procedure loops through the Properties collection of the Connection object to retrieve its property names and settings. The results are written both to the Immediate window and to a text file named C:\Propfile.txt.

©Hands-On 10-9: Establishing a Connection to the Current Access Database

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Connect_ToCurrentDb procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Connect_ToCurrentDb()

Dim conn As ADODB.Connection Dim fs As Object

Part II

Dim txtfile As Object Dim I As Integer

Set conn = CurrentProject.Connection

Set fs = CreateObject("Scnpting.FileSystemObject")

Set txtfile = fs.CreateTextFile("C:\Propfile.txt", True)

For I = 0 To conn.Properties.Count - 1

Debug.Print conn.Properties(I).Name & "=" &_

conn.Properties(I).Value txtfile.WriteLine (conn.Properties(I).Name & "=" & _ conn.Properties(I).Value)

Next I

MsgBox "Please check results in the " & _ "Immediate window." & vbCrLf _ & "The results have also been written to " _ & "the 'C:\Propfile.txt' file."

txtfile.Close Set fs = Nothing conn.Close Set conn = Nothing End Sub

To gain access to a computer's file system, the Connect_ToCurrentDb procedure uses the CreateObject function to access the Scripting.FileSystemObject. This function returns the FileSystemObject (fs). The CreateTextFile method of the FileSystemObject creates the TextStream object that represents a text file (txtfile). The WriteLine method writes each property and the corresponding setting to the newly created text file (C:\Propfile.txt). Finally, the Close method closes the text file.

0 0

Post a comment