Connecting to Jet Databases

The following simple code sample, OpenMyDB, opens the Northwind database. Like all the samples in this chapter, the code is available from the sole module in the Chapter01.mdb file, which is available in this book's companion content (see the Introduction for a URL designating the location of this content). Notice that a Dim statement declares and creates a reference to cnn1 as a Connection object from the ADODB library. The use of the Open method on cnn1 makes the database available to the rest of the procedure. The Provider and Data Source parameters appear within a single set of double quotes. These two parameters are delimited from each other via a semicolon. The Provider parameter points to the Jet 4 OLE DB provider, and the Data Source parameter points to the physical location of the Northwind database.

Sub OpenMyDB()
Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
   
'Create the connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office" & _
    "\Office11\Samples\Northwind.mdb;"
   
'Create recordset reference, and set its properties
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
   
'Open recordset, and print a test record
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
   
'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub

After creating a reference to the connection, the code instantiates a Recordset object. Instantiating an object reference makes it available for use within the procedure. Next, the procedure assigns values to a couple of properties for the recordset. The next-to-last block of code opens the recordset and prints a couple of fields from the current record when the recordset initially opens. The Open method for a Recordset object can reference a connection to a database and some source of records in the database. The previous code selects all of the records from the Customers table in a database. The cnn1 variable specifies the database as the Northwind.mdb file.

Although object references declared within a procedure normally go out of scope when the procedure closes, it is good programming practice to close objects and set them to Nothing before exiting a procedure. The preceding code sample applies this practice for the rst1 and cnn1 variables in its last four lines. By setting an object reference to Nothing, you formally release the object's memory. This practice can help avoid memory leaks, which can slow an application. If objects do not close successfully when your application exits a procedure, the memory remains reserved for the object. After repeatedly running a procedure, these memory reservations can slow down your application. The following sub procedure, OpenFast, also opens a recordset based on the Customers table in the Northwind Access database file and prints the first record. However, it uses fewer lines of code and the code is less complicated than the preceding sample. This is because this sample implicitly creates a connection and accepts more default settings.

Sub OpenFast()
Dim rst1 As ADODB.Recordset
   
'Less code, but potentially greater resource consumption.
Set rst1 = New ADODB.Recordset
rst1.Open "Customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
Debug.Print rst1.Fields(0), rst1.Fields(1)
   
'Clean up objects.
rst1.Close
Set rst1 = Nothing
   
End Sub

Since there is no explicit connection, the OpenFast procedure does not need to declare and instantiate a Connection object (and therefore doesn't have to open or close such an object). As you can see, the Open method for a Recordset object can include the essential connection information of a provider and a data source. Calling the Open method on a Recordset object instead of a Connection object requires only one additional parameter—the source for the recordset, which is the Customers table. The Open method relies on the default CursorType and LockType settings, which are, respectively, forward-only and read-only. These settings provide for very fast operations, but they do not offer as much functionality as other cursor types. Nevertheless, if the default CursorType and LockType settings suit your needs and let you turn your attention to other aspects of application development, they might be the best choice.

You can specify a connection to the current Access database file with the syntax CurrentProject.Connection. If your current database file has a table, a query, or a linked table named Customers, you can rewrite the OpenMyDB sample so that cnn1 points at CurrentProject.Connection. This eliminates the need to instantiate a new Connection object since you are using the existing one for the current database. The OpenMyDBLinkedTable procedure illustrates the syntax for the Chapter01.mdb file with a linked table named Customers pointing at the Northwind database. Notice that the sample uses a variable named cnn1, which it declares as a Connection object. This declaration is optional since the sample could also specify CurrentProject.Connection instead of cnn1 as the Connection parameter for the recordset's Open method. In any event, there is no need to set cnn1 to Nothing because it was never instantiated as a Connection object. The cnn1 variable merely points at the current database.

Sub OpenMyDBLinkedTable()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
   
'Point cnn1 at the CurrentProject
Set cnn1 = CurrentProject.Connection
   
'Create recordset reference, and set its properties.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
   
'Open recordset, and print a test record.
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
   
'Clean up objects.
rst1.Close
Set rst1 = Nothing
   
End Sub

The ReuseAConnection sub procedure shown next illustrates a couple of ADO programming features not yet covered and reinforces several others. This sample assumes the availability of a copy of the Northwind database named Northwind_backup.mdb in the same default folder as the Northwind database. The best way to discover what the sample offers is to step through it (for example, with the Step Into button on the Debug toolbar). First, the sample demonstrates how to reuse a Connection object (or any other ADO object). You must close an object before you can reset its properties so that the object can serve another purpose, such as connecting to a different database. If you step through the sample, you'll see that the code generates and traps a 3705 run-time error. The code in the error trap closes the cnn1 connection so that the procedure can reuse the object reference to connect to another database.

Sub ReuseAConnection()
Dim cnn1 As ADODB.Connection
On Error GoTo connTrap
   
'Assign the connection reference
Set cnn1 = New ADODB.Connection
   

'Use Jet provider to connect to Northwind
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source =C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
'Run initially with the next statement commented;
'then rerun with the next statement uncommented
'cnn1.Close
   
'Incrementally builds connection string.
'Forces error when the Connection object is already open.
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
cnn1.ConnectionString = cnn1.ConnectionString & _
    "Data Source =C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind_backup.mdb;" 
cnn1.Open
   
connExit:
'Close any connection still open before exiting
cnn1.Close
Set cnn1 = Nothing
Exit Sub
   
connTrap:
If err.Number = 3705 Then
'Close an open connection for its reuse
    Debug.Print "Closing cnn1"
    cnn1.Close
    Resume
Else
    Debug.Print err.Number; err.Description
    Debug.Print cnn1.Provider
    Debug.Print cnn1.Provider; cnn1.ConnectionString
End If
   
End Sub

The ReuseAConnection sub procedure demonstrates another point worthy of your attention. The cnn1 declaration includes an ADODB prefix before Connection. As noted previously, this is good programming practice. This is especially true when a VBA project has references to both the DAO and ADODB libraries. Since both libraries contain classes with the same name, you might end up with an object based on the wrong class if you do not use the library prefix in your declaration. As you work with more and more references (an easy way to expand the capabilities of your applications), it becomes increasingly desirable to use library name prefixes. The chance of your libraries having identical names for different classes increases as you reference more libraries.

If you are inexperienced with VBA programming, take note of the syntax for declaring an error trap. Just like events, errors happen. In many cases, these errors do not necessarily indicate a problem with the code in a solution (other than the need for more code for an error trap). Error traps give your solutions a way to dynamically adapt to run-time errors, which are not typically caused by failures in your code but by an inappropriate environment for your application. The ReUseAConnection sample illustrates use of the Resume statement without an argument. When used like this, the statement transfers control back to the point that raised the error.

Connecting to Non-Jet Databases

The next code sample, OpenMySQLDB, demonstrates two additional points about the behavior of the Connection object.

• First, this sample shows how to connect to a SQL Server database instead of a Jet database. In this case, the database is the NorthwindCS database, a SQL Server database shipping with generally the same tables, queries, forms, reports, and data access pages as those in the Northwind Access database file. You can find more information on the NorthwindCS database in the "Sample Databases" section of Chapter 11.

• Second, this sample highlights how similarly ADO programs for two entirely different database types. In both this sample and the OpenMyDB sample, you declare, instantiate, and open a Connection object in like ways. The major difference is the connection string, which is always unique for individual data providers. This sample then instantiates a recordset, assigns it property settings, and uses the connection as a parameter for the recordset's Open method. This code is identical in the two procedures—despite the fact that the earlier sample references a file-server database and this one references a client/server database.

  Note

  If you do not have the NorthwindCS database installed on the SQL Server to which you can connect, consider referencing a database other than the NorthwindCS database. For example, a SQL Server Northwind database has shipped with all versions of SQL Server since SQL Server 7. This database has the same tables as the NorthwindCS database. If you use a database other than the SQL Server Northwind database, it is likely that you will have to change the reference to the Customers table in the line of code invoking the Open method for rst1.

Sub OpenMySQLDB()
Dim cnn1 As Connection
Dim rst1 As Recordset
Dim str1 As String
   
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn1 = New ADODB.Connection
str1 = "Provider=SQLOLEDB;Data Source=CabSony1;" & _
    "Initial Catalog=NorthwindCS;User Id=sa;Password=password;"
cnn1.Open str1
   
'Create recordset reference, and set its properties.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
   
'Open recordset, and print a test record.
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
   
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub

The Provider parameter in the preceding sample points at the SQLOLEDB provider. This is the ADO data provider specifically designed for SQL Server databases. When connecting to a SQL Server database, you must designate the server name, which is CabSony1 in this case, and the database name. If you know your application will be running against a SQL Server on the same computer as your application, you can use (local) or localhost as the server name. As mentioned, this sample connects to the NorthwindCS database. The designation of a user identification and password depends on the type of authentication your SQL server uses. If your application uses SQL Server authentication, you must specify the user ID and password. If the SQL server for an application uses Windows NT authentication, you do not need to specify a user ID and password in your connection string. If your application ever runs on a Windows 98 computer or a computer disconnected from an office, department, or enterprise server, SQL Server authentication is a necessity. This is because your application will not necessarily have Windows NT or Windows 2000 available to authenticate the identification of users when it runs.

The call to the Open method for the Connection object in the preceding sample uses a string variable rather than a string constant as an argument. By assigning a different value to the string variable, you can connect to a different server or database. In addition, you can specify either SQL Server or Windows integrated security, which is based on either Windows NT or Windows 2000 logins. For example, the following connection string illustrates the syntax for specifying a connection to the Northwind database on the local server with Windows integrated security. In order for this to work, you must have a SQL Server instance on the local computer and you must have logged on to the workstation with a user ID and password that maps to a SQL Server login with Select permission for the Customers table in the NorthwindCS database. A complete procedure named OpenMySQLDBIntegrated illustrating the application of the connection string below is available in Chapter01.mdb. See Chapter 10 for more coverage of SQL Server security.

"Provider=SQLOLEDB;Data Source=localhost;" & _
    "Initial Catalog=NorthwindCS;Integrated Security=SSPI"

The rich diversity of ways in which ADO enables applications to connect to remote data sources is one of its major strengths. The ThreeWaysToConnectRemotely procedure highlights three different syntaxes for connecting to remote databases. This sample extends the earlier discussion about connecting to remote databases by contrasting the SQLOLEDB provider with the OLE DB provider for ODBC data sources as well as a DSN.

Sub ThreeWaysToConnectRemotely()
Dim cnn1 As ADODB.Connection
On Error GoTo connTrap
   
'Assign the connection reference
Set cnn1 = New ADODB.Connection
   
'A connection based on SQLOLEDB
   
With cnn1
    .Provider = "SQLOLEDB"
    .ConnectionString = "data source = CabSony1;" & _
        "user id = sa; Password=password; initial catalog =Pubs"
    .Open
End With
cnn1.Close
   
'Use connection string parameters with MSDASQL provider.
'Notice syntax difference from MSDASQL and SQLOLEDB providers.
cnn1.Open "Provider=MSDASQL;Driver=SQL Server;" & _
    "Server=CabSony1;Database=Pubs;uid=sa;pwd=password;"
cnn1.Close
   
'Designation of the provider, or even the user ID and password,
'might not be necessary with a DSN
cnn1.Open "DSN=Pubs;"
'The following close, in combination with the next one,
'raises a 3704 error number
cnn1.Close
   
connExit:
'Close any connection still open before exiting
cnn1.Close
Set cnn1 = Nothing
Exit Sub
   
connTrap:
If err.Number = 3704 Then
'The connection is already closed; skip close method
    Resume Next
Else
'Unanticipated run-time error
    Debug.Print err.Number; err.Description
    Debug.Print cnn1.Provider; cnn1.ConnectionString
End If
   
End Sub

All three examples in the ThreeWaysToConnectRemotely procedure connect to the Pubs database on a server named "CabSony1". The Pubs database ships with all versions of SQL Server. The first connection uses the SQLOLEDB provider. Consider using this provider whenever you connect to a SQL Server database. The second example in the code reveals the correct syntax for using the MSDASQL provider, the default ADO provider. If you omit the provider name from a connection string, ADO uses this provider. This provider is appropriate for databases that do not have a specific OLE DB data provider. The third example in the procedure uses a data source name (DSN) to designate the connection string. This technique is very popular when using ADO on a Web server and in some large organizations that push the DSN onto each computer on a network. Create a System DSN with the ODBC Data Source Administrator. You can reach this administrator through the Control Panel or the Administrative Tools icon within the Control Panel. The sample explicitly specifies a user ID and password for the first two connections, but it buries the user ID and password (along with other connection string elements) in the DSN for the third connection.

The Mode Property

By default, the Connection object's Open method creates a database for shared access. However, you can set the Connection object's Mode property to any of eight other settings that grant various degrees of restricted access to a database. These mode settings for Connection objects pertain generally to recordsets and commands that inherit Connection object settings through their ActiveConnection property. Additionally, when you open a Recordset object on a Command object, the Recordset object inherits the mode setting that applies to the command. Record and Stream objects, like Connection objects, have a Mode argument for their Open methods.

The following pair of procedures shows the impact of the read-only mode setting on the ability to update a recordset. Depending on the value of a conditional compiler constant named varPermitError, the procedure opens the Northwind database in either the default shared mode or read-only mode. Since the procedure attempts to update the Customers table in the Northwind database, opening the database in read-only mode forces a run-time error. Setting the varPermitError constant to True causes the error by setting the Mode property for cnn1 to read-only mode. The recordset inherits this setting through its ActiveConnection property. An error trap catches the error and shuts downs the program gracefully with a custom error message. The OpenLookOnly procedure manages the overall process, and the EncodeMode procedure returns a string with the name of the enum member corresponding to the Mode property setting.

Sub OpenLookOnly()
On Error GoTo LookOnlyTrap
Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim varPermitError As Variant
   
'Set varPermitError to True to generate
'error from Mode property restriction.
#Const varPermitError = True
   
'Instantiate a Connection object and
'conditionally set it to read-only data access.
Set cnn1 = New ADODB.Connection
#If varPermitError = True Then
    cnn1.Mode = adModeRead
#End If
   
'Open the Connection object.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
   
'Use the next line to determine the default mode setting.
Debug.Print EncodeMode(cnn1.Mode)
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
rst1.Open "Customers", , adOpenKeyset, adLockOptimistic, adCmdTable
   
'An adModeRead setting for cnn1.Mode causes an error in this
'procedure when you execute the next two lines.
rst1.Fields("CustomerID") = "xxxxx"
rst1.Update
Debug.Print rst1.Fields("CustomerID")
   
LookOnlyExit:
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
Exit Sub
   
LookOnlyTrap:
If err.Number = -2147217911 Then
'Error -2147217911 signals an attempt to edit a database open
'in read-only mode.
    MsgBox "Forced error by attempt to update with a " & _
        "read-only connection.", vbInformation, _
        "Programming Microsoft Access 2003"
    Resume LookOnlyExit
Else
'Another unanticipated error occurred.
    Debug.Print err.Number; err.Description
    MsgBox "View Immediate window for error diagnostics.", _
        vbInformation, "Programming Microsoft Access 2003"
End If
   
End Sub
   
Function EncodeMode(cnn1Mode As Long) As String
   
'Decode Mode setting and return corresponding enum member value.
Select Case cnn1Mode
    Case 0
        EncodeMode = "adModeUnknown"
    Case 1
        EncodeMode = "adModeRead"
    Case 2
        EncodeMode = "adModeWrite"
    Case 3
        EncodeMode = "adModeReadWrite"
    Case 4
        EncodeMode = "adModeShareDenyRead"
    Case 8
        EncodeMode = "adModeShareDenyWrite"
    Case 12
        EncodeMode = "adModeShareExclusive"
    Case 16
        EncodeMode = "adModeShareDenyNone"
    Case 4194304
        EncodeMode = "adModeRecursive"
End Select
   
End Function

Running the OpenLookOnly procedure with varPermitError equal to any value other than True opens the recordset in shared mode. This setting permits you to modify the value of table cells in the database. The first time you run the procedure it succeeds, unless you previously created a customer with a CustomerID field value of "xxxxx". The procedure fails the second time you run it because it attempts to create a duplicate value for the primary key field. The RestoreFirstCustomerID procedure, found in Chapter01.mdb, replaces the "xxxxx" CustomerID value with the original "ALFKI" value, which is the original CustomerID field value for the first customer in the table.

The EncodeMode procedure translates the numeric value of the Mode property setting into the name of the ConnectModeEnum member matching the value. The procedure uses a Select Case statement to perform the translation. Table 1-2 lists the member names and their matching values. While the procedure does not return the actual value of the Mode property setting, the ConnectModeEnum member name is more meaningful output in the Immediate window from the OpenLookOnly procedure.

Besides demonstrating the impact of mode settings, the OpenLookOnly procedure is noteworthy for at least one other reason. The recordset Open method includes a parameter after the cursor-type setting of adLockOptimistic. This is the first sample to show this optional Open method parameter. In fact, the parameter has the name Options. You designate a value for the parameter with intrinsic constants that are members of either the CommandTypeEnum or the ExecuteOptionEnum enums. The Object Browser in VBE can help you with the member names and values of enums. The Options parameter can designate the type of source for a recordset and indicate how the recordset returns records (for example, synchronously or asynchronously). In the case of the OpenLookOnly procedure, the Options parameter of adCmdTable instructs the Open method to treat the first parameter designating Customers as a table accessed via a SQL query.

Table 1-2 describes the nine constants you can use to set a connection's Mode property. These constants control the type of editing that one or more users can do through a connection to a database.

The OpenSchema Method

The Connection object's OpenSchema method lets an application browse the objects in the collections available through a connection without the requirement of creating a reference to the ADOX library and enumerating the elements in a collection. The output from the OpenSchema method for an Access database file provides information about the design of a database, such as the names of user-defined tables, its queries, and even details such as column and table validation rules. The specific details depend on how a given OLE DB provider implements the general capabilities of the method. For additional details on the scope of this method see the ADO210.chm Help file. Then, click the SchemaEnum link for additional help.

The following code uses the OpenSchema method with the Jet 4 provider to list the user-defined tables available from a connection. These tables appear in the Database window when you select Tables from the Objects bar (and the default option of not showing system tables prevails). The procedure starts by declaring a connection and a recordset. The connection acts as the source for the output from the OpenSchema method. The recordset holds the output from the OpenSchema method. The argument for the OpenSchema method indicates that the method returns a rowset of all tables in the data source designated in the connection. By filtering the rows returned by the method, the procedure prints just the names of user-defined tables.

Sub OpenSchemaTableTables()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
   
'Connect to the Northwind database
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
   
Set rst1 = cnn1.OpenSchema(adSchemaTables)
   
'Print just tables; other selection criteria include
'TABLE, ACCESS TABLE, and SYSTEM TABLE
Do Until rst1.EOF
    If rst1.Fields("TABLE_TYPE") = "TABLE" Then
        Debug.Print "Table name: " & _
            rst1.Fields("TABLE_NAME")
    End If
    rst1.MoveNext
Loop
   
'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub

New Access developers, and perhaps some experienced ones, might be surprised to learn that some types of queries can also be considered as tables. For example, you can use the OpenSchema method to discover the names of all stored queries that return rows and do not depend on parameters in an Access database file. Access database files typically refer to this type of query as a view.

The following code sample is a simple adaptation of the preceding one; it enumerates all the views in a connection. A view is a stored query that returns rows without requiring parameter values at run time.

Sub OpenSchemaTableViews()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
   
'Connect to the Northwind database
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
   
Set rst1 = cnn1.OpenSchema(adSchemaTables)
   
'Print just views; other selection criteria include
'TABLE, ACCESS TABLE, and SYSTEM TABLE
   
Do Until rst1.EOF
    If rst1.Fields("TABLE_TYPE") = "VIEW" Then
        Debug.Print "View name: " & _
            rst1.Fields("TABLE_NAME")
    End If
    rst1.MoveNext
Loop
   
'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub

The information provided by the OpenSchema method can be substantially richer than just the names of tables within a database. Examine members of the SchemaEnum to discover the valid arguments for the OpenSchema method. You use an enum member to specify a domain about which to gather schema-based information and then examine the column names for the rowset returned by the method to retrieve the detailed information available for that category. These columns vary according to the enum member.

The OpenSchemaConstraints sub procedure that appears next uses the adSchemaCheckConstraints argument value to return the set of validation rules in an Access database file. Before listing information in individual rows from the rowset returned by the method, the procedure shows the syntax for enumerating the field column names. As you can see, the columns are zero-based. You can use this enumeration to help decide which detailed information you want to examine. The sample that follows lists the CONSTRAINT_NAME, CHECK_CLAUSE, and Description columns. Some additional code in the sample spaces the string data for columns evenly across the Immediate window. I will review the logic to achieve this result after we examine the procedure's output.

Sub OpenSchemaConstraints()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
Dim i As Integer
   
'Connect to a backup of the Northwind database with a
'clean set of constraints
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
   
'Open the constraints in the database
Set rst1 = cnn1.OpenSchema(adSchemaCheckConstraints)
   
'Demonstrate syntax for enumerating the column names
'in the recordset returned by the OpenSchema method;
'the columns vary depending on the method's parameter
For i = 0 To rst1.Fields.Count - 1
    Debug.Print rst1.Fields(i).Name
Next i
   
'Find the length of each of the first two columns
int1 = FindTheLongest(rst1, "CONSTRAINT_NAME") + 1
rst1.MoveFirst
int2 = FindTheLongest(rst1, "CHECK_CLAUSE") + 1
rst1.MoveFirst
   
'Print contraint names, expressions, and descriptions
Do Until rst1.EOF
    Debug.Print rst1(2) & String(int1 - Len(rst1(2)), " ") & _
        rst1(3) & String(int2 - Len(rst1(3)), " ") & " " & rst1(4)
    rst1.MoveNext
Loop
   
'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub
   

Function FindTheLongest(rst1 As ADODB.Recordset, _
    FieldIndex As String) As Integer
Dim Length As Integer
   
'Loop to return longest string in a field
Do Until rst1.EOF
    Length = Len(rst1.Fields(FieldIndex))
    If Length > FindTheLongest Then
        FindTheLongest = Length
    End If
    rst1.MoveNext
Loop
   
End Function

The output from the OpenSchemaConstraints procedure appears in Figure 1-3. It begins by listing the names for the five columns from the OpenSchema method called with an adSchemaCheckConstraints argument. Next, the output shows the constraints for the columns within the Access Northwind database file. The procedure succinctly provides that information by listing the table and column of each constraint along with the expression governing the input for a column.

Figure 1-3: The Immediate window output from the OpenSchemaConstraints procedure.

Multicolumn string data of uneven lengths do not normally appear in neat columns within the Immediate window. The arrangement of columns is typically uneven because the length of a string in any column usually varies from row to row. This can make the results difficult to read. However, the preceding code sample circumvents this difficulty by padding each string after using a function procedure to determine the longest string in a field within a recordset. The main procedure spaces columns of string data evenly across the Immediate window, based on the longest strings in the CONSTRAINT_NAME and CHECK_CLAUSE columns.

If you are working with very large result sets from the OpenSchema method, you might find it valuable to filter directly with the Criteria argument for the OpenSchema method. Use an Array function to specify the appropriate criterion for each argument from the return set. Search for the q186246 article at the support.microsoft.com site for more detail, including code samples implementing this approach for both Access and SQL Server databases. The OpenSchemaTablesWithArray procedure demonstrates applying this technique to the first sample in this section. The use of the Criteria argument syntax removes the need for an If statement within a Do loop. However, the cost of this benefit is a requirement to use the Array function. This, in turn, demands a grasp of how to specify the field criteria arguments for the Array function in this context. Some readers will find the Array function a worthwhile improvement, but others are likely to find the If…Then statement a more natural way to code the solution. This section presents both approaches so you can choose the one you prefer.

Sub OpenSchemaTableTablesWithArray()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
   
'Connect to the Northwind database.
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
    
'Query for user-defined tables with Criteria argument
'specified as an array
Set rst1 = cnn1.OpenSchema(adSchemaTables, _
    Array(Empty, Empty, Empty, "Table"))
   
'Print just tables; other selection criteria include
'TABLE, ACCESS TABLE, and SYSTEM TABLE.
Do Until rst1.EOF
    Debug.Print "Table name: " & rst1("TABLE_NAME")
    rst1.MoveNext
Loop
   
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub