VBA Brush Up 10：Accessing Data Using ADO

Technorati 标签: VBA, ADO

来自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p169-p193

（1）ActiveX Data Objects (ADO) is a set of programming objects that enable client applications(including Microsoft Office Access) to access and manipulate data through an OLE DB provider.

（2）an integrated part of Microsoft Access has been its database engine, commonly referred to as Microsoft Jet or Jet database engine. Jet databases are stored in the familiar .mdb file format.

（3）Open Database Connectivity (ODBC). This technology made it possible to connect to relational databases using appropriate ODBC drivers supplied by database vendors and third parties. While this technology is still in use today, it is not object-oriented and is considered by many to be quite difficult to work with. To access data via ODBC you need a specific ODBC driver installed on the computer containing the data source.

（4）Microsoft enabled programmers to efficiently access and manipulate Microsoft Jet databases with a technology called Data Access Objects (DAO). DAO consists of a hierarchy of objects that provide methods and properties for designing and manipulating databases.
Although you will still encounter numerous Microsoft Access VBA programs that use the DAO Object Model, it is recommended that you perform your database programming tasks by using another object model known as ActiveX Data Objects (ADO).

（5）Microsoft Office Access 2003 continues to use ADO as its main and preferred method of data access. ADO works with the new technology known as OLE DB. This technology is object-based, but it is not limited to relational databases. OLE DB can access both relational and non-relational data sources such as directory services, mail stores, multimedia, and text files, as well as mainframe data (VSAM and MVS).

（6）To access external data with OLE DB you do not need any specific drivers installed on your computer because OLE DB does not use drivers; it uses data providers to communicate with data stores. Data providers are programs that enable access to data. OLE DB has many providers, such as Microsoft OLE DB Provider for SQL Server or Microsoft Jet 4.0 OLE DB Provider. There are also providers for Oracle, NT 5 Active Directory, and even a provider for ODBC.

（7）ActiveX Data Objects (ADO) is OLE DB’s main object model. ADO consists of three object models, each providing a different area of functionality

（8）Setting Up a Reference to ADO Object Libraries：

1. From the Microsoft Visual Basic menu bar, choose Tools | References.
2. Make sure that Microsoft ActiveX Data Objects 2.5 Library is selected. Locate the entry by scrolling down the list, and click the check box to the left of the name to select it. 
3. Click the check box to the left of Microsoft ADO Ext. 2.5 for DDL and Security. 
4. From the list, select Microsoft Jet and Replication Objects 2.5 Library.

（9）Beore you can open a specific database in code to retrieve and manipulate data, you must establish a connection with the data source.

1. Dim conn As ADODB.Connection
2. Set conn = New ADODB.Connection

So far you’ve created a Connection object that doesn’t point to any particular data source.

Next, you need to supply the connection information. When connecting to an unsecured Microsoft Jet database, this includes at least the Provider name and the Data Source name.

You can set the Connection object’s Provider property to specify the name of the provider to be used to connect to the data source. In this example, we are connecting to a Microsoft Jet database, so we’ll use the Microsoft.Jet.OLEDB.4.0 provider. The names of common data providers used with ADO are listed in Table 10-2.

You need to specify additional information, such as the database path, to establish a connection by using the Connection object’s ConnectionString property.

1. With conn 
2.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
3.     .ConnectionString = "Data Source=" & CurrentProject.Path & "/Northwind.mdb" 
4. End With
6. conn.Open
8. conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _ 
9.     "Data Source=" & CurrentProject.Path & _ 
10.     "/Northwind.mdb"
12. With conn 
13.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
14.     .ConnectionString = "Data Source=" & CurrentProject.Path & "/Northwind.mdb" 
15.     .Open 
16. End With
18. conn.Close
20. Set conn = Nothing

The Close method of the Connection object closes the physical connection to the data source. To completely release the resources used by the Connection object, set the Connection object variable to Nothing,

（10）By default, the Connection object’s Open method opens a database for shared access. The Mode property must be set prior to opening the connection because it is read-only once the connection is open. Connections can be opened read-only, write-only, or read/write. You can also specify whether other applications should be prevented from opening a connection. The Mode property will work only if the provider you’re using to open the connection supports it.

1. With conn 
2.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
3.     .Mode = adModeReadWrite 
4.     .ConnectionString = "Data Source=" & strDb 
5.     .Open 
6. End With

（11）If a database is secured with a password, you have to specify the Jet OLEDB:Database Password property as part of a ConnectionString property, like this: "Jet OLEDB:Database Password=secret;" Passwords are case sensitive.

（12）Open the NorthSecure.mdb database file with exclusive access. (Choose File | Open, switch to the folder containing the NorthSecure.mdb file, highlight the name of the file, click the arrow next to the Open button, and choose Open Exclusive.)

Set the database password to “Secret” by choosing Tools | Security | Set Database Password and typing Secret.

1. With conn 
2.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
3.     .ConnectionString = "Data Source=" & strDb & ";" & _ 
4.         "Jet OLEDB:Database Password=secret;" 
5.     .Open 
6. End With

（13）To open a database that is secured at the user level, you must supply the:

• Full path to the workgroup information file (system database)
• User ID
• Password
• Specify the workgroup information file by using the Jet OLEDB: System Database property.

1. Open the NorthSecureUser.mdb database and choose Tools | Security | User-Level Security Wizard to create a new workgroup information file.
2. Follow the steps of the Security Wizard. Do not change anything until you get to the screen asking for User Name and Password. Set up a user account named Developer with a password WebMaster, and click the Add This User to The List button. Click the Next button, assign Developer to the Admin group, and press the Finish button. Access will display the One-Step Security Wizard report. Print it out for your reference.
3. Next, close the Microsoft Access application window with the NorthSecureUser database.

1. strDb = CurrentProject.Path & "/NorthSecureUser.mdb" 
2. strSysDb = CurrentProject.Path & "/Security.mdw"
3. Set conn = New ADODB.Connection 
4. With conn 
5.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
6.     .ConnectionString = "Data Source=" & strDb & ";" & _ 
7.         "Jet OLEDB:System Database=" & strSysDb 
8.     .Open , "Developer", "WebMaster" 
9. End With

The code snippet below demonstrates how to avoid formatting problems with long connection strings by using the Connection object’s Properties collection.

1. With conn 
2.     .Provider = "Microsoft.Jet.OLEDB.4.0;" 
3.     .Properties("Jet OLEDB:System Database") = strSysDb 
4.     .Open strDb, "Developer", "WebMaster" 
5. End With

Notice that before you can reference provider-specific properties from the Connection object’s Properties collection, you must indicate which provider you are using.

（14）You can open external data sources supported by the Microsoft Jet database engine by using ADO and the Microsoft Jet 4.0 OLE DB provider. Use the Extended Properties of the Connection object to pass the connection string.

1. conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
2.     "Data Source=" & CurrentProject.Path & _ 
3.     "/Report.xls;" & _ 
4.     "Extended Properties=Excel 8.0;"

（15）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.

（16）

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.

1. Set conn = New ADODB.Connection 
2. conn.Open "Provider=MSDASQL;DSN=MyDbaseFile;" 
3. Set rst = New ADODB.Recordset 
4. rst.Open "Customer.dbf", conn, , , adCmdTable

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.

（17）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. All connection information is specified in code by using the DRIVER and DBQ parameters (for a dBASE connection).

1. Set conn = New ADODB.Connection 
2. conn.Open "DRIVER={Microsoft dBase Driver (*.dbf)};" & _ 
3.      "DBQ=" & CurrentProject.Path & "/" 
4. Debug.Print conn.ConnectionString 
5. Set rst = New ADODB.Recordset 
6. rst.Open "Select * From Customer.dbf", conn, _ 
7.     adOpenStatic, adLockReadOnly, adCmdText

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.

（18）The following example procedure shows how to open a recordset based on a comma-separated file format and write the file contents to the Immediate window.

This data file can be prepared from scratch by typing the following in Notepad and saving the file as Employees.txt:

"Last Name", "First Name", "Birthdate", "Years Worked"
"Krawiec","Bogdan",#1963-01-02#,3
"Górecka","Jadwiga",#1948-05-12#,1
"Olszewski","Stefan",#1957-04-07#,0

1. conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _ 
2.     "DBQ=" & CurrentProject.Path & "/" 
3. Set rst = New ADODB.Recordset 
4. rst.Open "select * from [Employees.txt]", conn, adOpenStatic, _ 
5.     adLockReadOnly, adCmdText 
6. Do Until rst.EOF 
7.     For Each fld In rst.Fields 
8.         Debug.Print fld.Name & "=" & fld.Value 
9.     Next fld 
10.     rst.MoveNext 
11. Loop

（19）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.

（20）You can create a new Microsoft Jet database programmatically by using the ADOX Catalog’s Create method. The Create method creates and opens a new ADO connection to the data source. An error will occur if the provider does not support creating new catalogs.

（21）The procedure in Hands-On 10-10 creates a new blank database named NewAccessDb.mdb in your computer’s root directory. The error trap ensures that the procedure works correctly even if the specified file already exists. The VBA Kill statement is used to delete the file from your hard disk when the error is encountered.

1. Sub CreateI_NewDatabase() 
2.     Dim cat As ADOX.Catalog 
3.     Dim strDb As String
5.     Set cat = New ADOX.Catalog 
6.     strDb = "C:/NewAccessDb.mdb" 
7.     On Error GoTo ErrorHandler 
8.     cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
9.         "Data Source=" & strDb 
10.     MsgBox "The database was created (" & strDb & ")." 
11.     Set cat = Nothing 
12.     Exit Sub
14. ErrorHandler: 
15.     If Err.Number = -2147217897 Then 
16.         Kill strDb 
17.         Resume 0 
18.     Else 
19.         MsgBox Err.Number & ": " & Err.Description 
20.     End If 
21. End Sub 

While creating a database, you may specify that the database should be encrypted by setting the Jet OLEDB:Encrypt Database property to True. You can also include the database version information with the JetOLEDB:Engine Type property.

1. cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
2.     "Data Source=" & strDb & _ 
3.     "Jet OLEDB:Encrypt Database=True;" & _ 
4.     "Jet OLEDB:Engine Type=1;"

（22）ADO does not have a special method for copying files. However, you can set up a reference to the Microsoft Scripting Runtime reference in the Microsoft Visual Basic Editor screen to gain access to your computer file system, or use the CreateObject function to access this library without setting up a reference.

1. Sub Copy_AnyFile() 
2.     Dim fs As Object 
3.     Dim strDb As String 
4.     Set fs = CreateObject("Scripting.FileSystemObject") 
5.     strDb = "C:/NewAccessDb.mdb" 
6.     fs.CopyFile strDb, CurrentProject.Path & "/" 
7.     Set fs = Nothing 
8. End Sub

（23）To access data residing on Microsoft SQL Server 6.5, 7.0, or 2000, you can use SQLOLEDB, which is the native Microsoft OLE DB provider for SQL.

1. With conn 
2.     .Provider = "SQLOLEDB" 
3.     .ConnectionString = "Data Source=Mozartv4;" & _ 
4.         "database=Musicians; UserId=sa; Password=;" 
5.     .Open 
6. End With 
7. If conn.State = adStateOpen Then 
8.     MsgBox "Connection was established." 
9. End If

Notice that in the example above, the Connection object’s Provider property is set to SQLOLEDB and the ConnectionString property includes a server name, database name, user ID, and password information.

（24）Another way of connecting to an SQL database is by using the MSDASQL provider. This provider allows you to access any existing ODBC data sources.

You can open a connection to your remote data source by using an ODBC DSN. This, of course, requires that you create a Data Source Name (DSN) entry on your workstation via the 32-bit ODBC tool in the Windows Control Panel (in Windows 2000, use Data Sources (ODBC), which is available in Administrative Tools in the Windows Control Panel).

The code example below opens then closes a connection with the remote data source based on a DSN named Pubs. You could skip setting the Provider property because MSDASQL is the default provider for ADO. All you really need to establish a connection in this case is a DSN.

1. With conn 
2.     .Open "Provider=MSDASQL; DSN=Pubs" 
3.     .Close 
4. End With

（25）DSN connections are inconvenient, as they require that you create a Data Source Name (DSN) on each user computer. Fortunately, there is a workaround. You can create a DSN-less connection independent of user workstations. Instead of creating a DSN on a user machine, simply provide all the connection information to the ODBC data source in your VBA code.

1. With conn 
2.     ' DSN-less connection using the ODBC driver 
3.     ' (modify the data source information below) 
4.     .Open "Driver={SQL Server};" & _ 
5.         "Server=11.22.17.153;" & _ 
6.         "UID=myId;" & _ 
7.         "PWD=myPassword;" & _ 
8.         "Database=SupportDb" 
9.     .Close 
10. End With
12. With conn 
13.     ' DSN-less connection using the SQLOLEDB provider 
14.     ' (modify the data source information below) 
15.     .Open "Provider=SQLOLEDB;" & _ 
16.         "DataSource=Mozart;" & _ 
17.         "Initial Catalog=MusicDb;" & _ 
18.         "UID=myId; Password=myPassword;" 
19.     .Close 
20. End With

（26）The Description property of the Err object contains the message for the encountered error number. When using ADO to access data, in addition to the VBA Err object, you can get information about the errors from the ActiveX Data Objects (ADO) Error object. When an error occurs in an application that uses the ADO Object Model, an Error object is appended to the ADO Errors collection of the Connection object and you are advised about the error via a message box.

While the VBA Err object holds information only about the most recent error, the ADO Errors collection can contain several entries regarding the last ADO error. You can count the errors caused by an invalid operation by using the Count property of the Errors collection. By checking the contents of the Errors collection you can learn more information about the nature of the error.

The Errors collection is available only from the Connection object. Errors that occur in ADO itself are reported to the VBA Err object. Errors that are provider-specific are appended to the Errors collection of the ADO Connection object. These errors are reported by the specific OLE DB provider when ADO objects are being used to access data.

1. Sub DBError()
3.     Dim conn As New ADODB.Connection 
4.     Dim errADO As ADODB.Error 
5.     On Error GoTo CheckErrors
7.     conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
8.         & "Data Source=C:/my.mdb"
10. CheckErrors: 
11.     Debug.Print "VBA error number: " _ 
12.         & Err.Number & vbCrLf _ 
13.         & " (" & Err.Description & ")" 
14.     Debug.Print "Listed below is information " _ 
15.         & "regarding this error " & vbCrLf _ 
16.     & "contained in the ADO Errors collection." 
17.     For Each errADO In conn.Errors 
18.         Debug.Print vbTab & "Error Number: " & errADO.Number 
19.         Debug.Print vbTab & "Error Description: " & errADO.Description 
20.         Debug.Print vbTab & "Jet Error Number: " & errADO.SQLState 
21.         Debug.Print vbTab & "Native Error Number: " & errADO.NativeError 
22.         Debug.Print vbTab & "Source: " & errADO.Source 
23.         Debug.Print vbTab & "Help Context: " & errADO.HelpContext 
24.         Debug.Print vbTab & "Help File: " & errADO.HelpFile 
25.     Next
27.     MsgBox "Errors were written to the Immediate window."
29. End Sub

（27）With frequent use over a period of time, the performance of your database may deteriorate. When objects are deleted from a database but the space isn’t reclaimed, fragmentation may occur. To improve the database performance and to reduce the database file size, you can compact or repair Microsoft Office Access databases by using the ADO extension library, Microsoft Jet and Replication Objects (JRO).

You can compact a Microsoft Jet database by using the CompactDatabase method of the JRO JetEngine object. To compact the database, first ensure that it is closed. Provide a new filename for the compacted database, then rename or delete the original and rename the compacted database to the original name.

1. Sub CompactDb()
3.     Dim jetEng As JRO.JetEngine 
4.     Dim strCompactFrom As String 
5.     Dim strCompactTo As String 
6.     Dim strPath As String
8.     strPath = CurrentProject.Path & "/" 
9.     strCompactFrom = "Northwind.mdb" 
10.     strCompactTo = "NorthwindComp.mdb"
12.     ' Make sure there isn't already a file with the 
13.     ' name of the compacted database. 
14.     On Error GoTo HandleErr
16.     ' Compact the database 
17.     Set jetEng = New JRO.JetEngine 
18.     jetEng.CompactDatabase "Data Source=" & _ 
19.         strPath & strCompactFrom & ";", _ 
20.         "Data Source=" & _ 
21.         strPath & strCompactTo & ";" 
22.     ' Delete the original database 
23.     Kill strPath & strCompactFrom
25.     ' Rename the file back to the original name 
26.     Name strPath & strCompactTo As strPath & strCompactFrom
28. ExitHere: 
29.     Set jetEng = Nothing 
30.     MsgBox "Compacting completed."
32.     Exit Sub
34. HandleErr: 
35.     MsgBox Err.Number & ": " & Err.Description 
36.     Resume ExitHere
38. End Sub