Opening a dBASE File Using Msdasql Provider

MSDASQL is the Microsoft OLE DB provider for ODBC. This provider works with all ODBC data sources. You can use MSDASQL to access data located in an external data source such as a dBASE file. When you connect to a database via the ODBC, you must specify the connection information. You do this by creating the DSN (Data Source Name) via the ODBC Administrator. The ODBC icon is located in the Windows Control Panel. If you are using Windows 2000 or Windows XP, open the Administrative Tools in the Control Panel, then click the Data Sources (ODBC) icon. The DSN contains information about database configuration, user security, and location. There are three types of DSNs:

User DSN — A User DSN is stored locally in the Windows registry and limits database connectivity to the user who creates it. File DSN — All the information required to connect to the data source is stored in a DSN file that can be shared with other users. These files are stored by default in the Program Files\Common Files\Odbc\Data Sources folder. The File DSN provides access to multiple users and can be easily transferred from one server to another by copying DSN files. System DSN — A System DSN is stored locally in the Windows registry. The System DSN enables all users logged on to a particular server to access a database.

The procedure in Hands-On 10-7 illustrates how to open a dBASE file (Cus-tomer.dbf) via the ODBC data source named MyDbaseFile.

©Hands-On 10-7: Opening a dBASE file with ADO

1. Open the Control Panel, click Administrative Tools, and click Data Sources (ODBC).

2. Click the Add button and add a User DSN.

3. Select Microsoft dBASE driver (*.dbf) and click Finish.

4. Enter the Data Source Name and Database Version as shown in Figure 10-2. Be sure to clear the Use Current Directory check box. Click the Select Directory button and move to the folder where the current Acc2003_Chap10.mdb file is located.

Figure 10-2: To access a data source programmati-cally by using the default provider (MSDASQL), you can specify the connection information by defining the Data Source Name (DSN).

ODBC dBASE Setup

HS

1 Data Source Name:

I 0K 1

Cancel |

1 Description:

1 Database

Heb

1 Version: [dBase 5,0

1 Drectoiy: C:V..\CHAPTER SOURCE FILES 2003

Select Directory...

Select Indexes...

I I Use Cuirent Direeiofi1

Options» |

Creating and Manipulating Databases with ADO

5. Click OK to complete your entry and then close the ODBC Data Source Administrator dialog box.

6. Switch back to the Acc2003_Chap10 database and press Alt+F11 to activate the Visual Basic Editor window.

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

8. In the module's Code window, type the Open_dBaseFile procedure shown below.

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

Sub Open_dBaseFile()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection conn.Open "Provider=MSDASQL;DSN=MyDbaseFile;"

Set rst = New ADODB.Recordset rst.Open "Customer.dbf", conn, , , adCmdTable

Do Until rst.EOF

Debug.Print rst.Fields(1).Value rst.MoveNext Loop rst.Close

Set rst = Nothing conn.Close

Set conn = Nothing

MsgBox "The Immediate window contains the list of customers." End Sub

After connecting to the external data source (dBASE), the Open_dBaseFile procedure opens a recordset based on the Customer.dbf table and writes the values of the first field of each record to the Immediate window. This procedure uses the Data Source Name (DSN) to connect to an external dBASE file. The DSN holds information about the location of the file as well as the required ODBC (Open Database Connectivity) driver. After connecting to the dBASE file, the procedure creates a Recordset object and uses its EOF property to loop through the recordset until the end of the file is reached. While looping, the procedure prints the values of each record's first field to the Immediate window. You will see more examples of using EOF properties in Chapter 14, "Working with Records."

Because users can modify or delete the DSN your program may fail, so it may be a better idea to use a so-called DSN-less connection. Instead of setting up a DSN, you can specify your ODBC driver and server in your connect string when using ADO. This is called a "DSN-less" ODBC connection because you do not need to set up a DSN to access your ODBC database server. All connection information is specified in code by using the DRIVER and DBQ parameters (for a dBASE connection). The following procedure demonstrates how to perform the same task as the above procedure by using a DSN-less connection to the dBASE file.

Part II

Sub Open_dBase_DSNLess()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection conn.Open "DRIVER={Microsoft dBase Driver (*.dbf)};" &_

"DBQ=" & CurrentProject.Path & "\" Debug.Print conn.ConnectionString Set rst = New ADODB.Recordset rst.Open "Select * From Customer.dbf", conn, _

adOpenStatic, adLockReadOnly, adCmdText Do Until rst.EOF

Debug.Print rst.Fields(1).Value rst.MoveNext Loop rst.Close

Set rst = Nothing conn.Close

Set conn = Nothing

MsgBox "The Immediate window contains the list of customers." End Sub

In the DSN-less connection shown above, you provide all the information required to open the connection. Notice that the dBASE filename is specified in the SQL statement.

0 0

Responses

  • leon
    How to add MSDASQL provider?
    7 years ago

Post a comment