The Connection Object


The Connection object establishes a link to a database. You use a Connection object implicitly or explicitly when you work with a database. When you explicitly create this object, you can efficiently manage one or more connections and reassign the roles that these connections serve in an application. Implicitly creating a Connection object shortens your code, but each new object created with an implicit connection consumes more resources. If your application has only one or two objects, each requiring its own connection, implicit connections might be best. ADO lets you choose how to create and manage connections as you see fit.

Unlike DAO, ADO is a universal data access language, so not all ADO properties, methods , or even data types are appropriate for the Jet engine. There is, however, a special OLE DB provider for Jet 4. Microsoft introduced this provider with Access 2000, and it is still available for use with Access 2003. Since Connection objects critically depend on provider specifications, the ability to set a Connection parameter that references the Jet 4 provider is valuable . This custom provider allows ADO to reflect many of the special strengths that Jet offers. When you refer to an Access database in another file, you might want to include a Data Source parameter, which points to the physical location of a database that is not in the current project.

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.

Note  

Using ADODB as a prefix in declarations for classes from the ADODB library is good programming practice. The convention explicitly names the source library for the class name .

 SubOpenMyDB() Dimcnn1AsNewADODB.Connection Dimrst1AsADODB.Recordset     'Createtheconnection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;"     'Createrecordsetreference,andsetitsproperties Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic     'Openrecordset,andprintatestrecord rst1.Open"Customers",cnn1 Debug.Printrst1.Fields(0).Value,rst1.Fields(1).Value     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

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.

 SubOpenFast() Dimrst1AsADODB.Recordset     'Lesscode,butpotentiallygreaterresourceconsumption. Setrst1=NewADODB.Recordset rst1.Open"Customers","Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" Debug.Printrst1.Fields(0),rst1.Fields(1)     'Cleanupobjects. rst1.Close Setrst1=Nothing     EndSub 

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.

 SubOpenMyDBLinkedTable() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset     'Pointcnn1attheCurrentProject Setcnn1=CurrentProject.Connection     'Createrecordsetreference,andsetitsproperties. Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic     'Openrecordset,andprintatestrecord. rst1.Open"Customers",cnn1 Debug.Printrst1.Fields(0).Value,rst1.Fields(1).Value     'Cleanupobjects. rst1.Close Setrst1=Nothing     EndSub 

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.

Note  

It is more efficient to re-use an existing connection object than it is to open a new one for every recordset or command. There are two reasons for this. First, opening a new connection is a relatively long process, so re-using an existing connection saves time. Second, minimizing the number of connections to an Access database improves its ability to respond to requests expeditiously. You can think of each connection as simulating a new user .

 SubReuseAConnection() Dimcnn1AsADODB.Connection OnErrorGoToconnTrap     'Assigntheconnectionreference Setcnn1=NewADODB.Connection     'UseJetprovidertoconnecttoNorthwind cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" 'Runinitiallywiththenextstatementcommented; 'thenrerunwiththenextstatementuncommented 'cnn1.Close     'Incrementallybuildsconnectionstring. 'ForceserrorwhentheConnectionobjectisalreadyopen. cnn1.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" cnn1.ConnectionString=cnn1.ConnectionString&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind_backup.mdb;" cnn1.Open     connExit: 'Closeanyconnectionstillopenbeforeexiting cnn1.Close Setcnn1=Nothing ExitSub     connTrap: Iferr.Number=3705Then 'Closeanopenconnectionforitsreuse Debug.Print"Closingcnn1" cnn1.Close Resume Else Debug.Printerr.Number;err.Description Debug.Printcnn1.Provider Debug.Printcnn1.Provider;cnn1.ConnectionString EndIf     EndSub 

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.

Note  

It is often a good practice to not close and remove objects when you detect an error. This is because run-time errors can result from an unanticipated problem associated with the instantiation, closing, or opening of object references. If such an error occurs, looping to a code segment that attempts to close a non-instantiated object can generate an infinite loop.

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 .

 SubOpenMySQLDB() Dimcnn1AsConnection Dimrst1AsRecordset Dimstr1AsString     'CreateaConnectionobjectafterinstantiatingit, 'thistimetoaSQLServerdatabase. Setcnn1=NewADODB.Connection str1="Provider=SQLOLEDB;DataSource=CabSony1;"&_ "InitialCatalog=NorthwindCS;UserId=sa;Password=password;" cnn1.Openstr1     'Createrecordsetreference,andsetitsproperties. Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic     'Openrecordset,andprintatestrecord. rst1.Open"Customers",cnn1 Debug.Printrst1.Fields(0).Value,rst1.Fields(1).Value     'Cleanupobjects. rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

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;DataSource=localhost;"&_ "InitialCatalog=NorthwindCS;IntegratedSecurity=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.

 SubThreeWaysToConnectRemotely() Dimcnn1AsADODB.Connection OnErrorGoToconnTrap     'Assigntheconnectionreference Setcnn1=NewADODB.Connection     'AconnectionbasedonSQLOLEDB     Withcnn1 .Provider="SQLOLEDB" .ConnectionString="datasource=CabSony1;"&_ "userid=sa;Password=password;initialcatalog=Pubs" .Open EndWith cnn1.Close     'UseconnectionstringparameterswithMSDASQLprovider. 'NoticesyntaxdifferencefromMSDASQLandSQLOLEDBproviders. cnn1.Open"Provider=MSDASQL;Driver=SQLServer;"&_ "Server=CabSony1;Database=Pubs;uid=sa;pwd=password;" cnn1.Close     'Designationoftheprovider,oreventheuserIDandpassword, 'mightnotbenecessarywithaDSN cnn1.Open"DSN=Pubs;" 'Thefollowingclose,incombinationwiththenextone, 'raisesa3704errornumber cnn1.Close     connExit: 'Closeanyconnectionstillopenbeforeexiting cnn1.Close Setcnn1=Nothing ExitSub     connTrap: Iferr.Number=3704Then 'Theconnectionisalreadyclosed;skipclosemethod ResumeNext Else 'Unanticipatedrun-timeerror Debug.Printerr.Number;err.Description Debug.Printcnn1.Provider;cnn1.ConnectionString EndIf     EndSub 

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.

Note  

You can readily hide the user ID and password with either of the first two approaches. First, VBA offers you the opportunity to lock projects for viewing except by those users with a password. Second, you can convert your .mdb file to an .mde format. This process removes all editable code. Both approaches receive more coverage in Chapter 10.

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.

 SubOpenLookOnly() OnErrorGoToLookOnlyTrap Dimcnn1AsNewADODB.Connection Dimrst1AsADODB.Recordset DimvarPermitErrorAsVariant     'SetvarPermitErrortoTruetogenerate 'errorfromModepropertyrestriction. #ConstvarPermitError=True     'InstantiateaConnectionobjectand 'conditionallysetittoread-onlydataaccess. Setcnn1=NewADODB.Connection #IfvarPermitError=TrueThen cnn1.Mode=adModeRead #EndIf     'OpentheConnectionobject. cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'Usethenextlinetodeterminethedefaultmodesetting. Debug.PrintEncodeMode(cnn1.Mode) Setrst1=NewADODB.Recordset rst1.ActiveConnection=cnn1 rst1.Open"Customers",,adOpenKeyset,adLockOptimistic,adCmdTable     'AnadModeReadsettingforcnn1.Modecausesanerrorinthis 'procedurewhenyouexecutethenexttwolines. rst1.Fields("CustomerID")="xxxxx" rst1.Update Debug.Printrst1.Fields("CustomerID")     LookOnlyExit: 'Cleanupobjects. rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing ExitSub     LookOnlyTrap: Iferr.Number=-2147217911Then 'Error-2147217911signalsanattempttoeditadatabaseopen 'inread-onlymode. MsgBox"Forcederrorbyattempttoupdatewitha"&_ "read-onlyconnection.",vbInformation,_ "ProgrammingMicrosoftAccess2003" ResumeLookOnlyExit Else 'Anotherunanticipatederroroccurred. Debug.Printerr.Number;err.Description MsgBox"ViewImmediatewindowforerrordiagnostics.",_ vbInformation,"ProgrammingMicrosoftAccess2003" EndIf     EndSub     FunctionEncodeMode(cnn1ModeAsLong)AsString     'DecodeModesettingandreturncorrespondingenummembervalue. SelectCasecnn1Mode Case0 EncodeMode="adModeUnknown" Case1 EncodeMode="adModeRead" Case2 EncodeMode="adModeWrite" Case3 EncodeMode="adModeReadWrite" Case4 EncodeMode="adModeShareDenyRead" Case8 EncodeMode="adModeShareDenyWrite" Case12 EncodeMode="adModeShareExclusive" Case16 EncodeMode="adModeShareDenyNone" Case4194304 EncodeMode="adModeRecursive" EndSelect     EndFunction 

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.

Table 1.2: ConnectModeEnum Members

Constant

Value

Behavior

adModeUnknown

Permissions not set or determined

adModeRead

1

Read-only permission

adModeWrite

2

Write-only permission

adModeReadWrite

3

Read/write permission

adModeShareDenyRead

4

Prevents others from opening record source with read permissions

adModeShareDenyWrite

8

Prevents others from opening record source with write permissions

adModeShareExclusive

12

Prevents others from opening the connection

adModeShareDenyNone

16

Shared access (default)

AdModeRecursive

4194304

Can propagate share-deny restrictions to children of the current record

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.

Note  

Intrinsic constants are constants supplied by Microsoft applications and technologies, such as Access, VBA, ADO, and DAO. These constant names represent numeric or string values. Sets of related intrinsic constants comprise enums, such as the CommandTypeEnum or the ExecuteOptionEnum . By selecting a library and clicking < globals > in the Object Browser, you can list the full set of intrinsic constants associated with a library. Searching for a particular intrinsic constant with the Object Browser will reveal the enum to which an intrinsic constant belongs.

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.

Note  

If you go to the SchemaEnum Help page in the ADO Help file (ADO210.chm), you will notice that the hyperlinks in the Description column do not work. The links work only from a Web page at the Microsoft Developer Network (MSDN) site. Open your browser to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp for a version of the page with working hyperlinks in the Description column. By the way, the linked pages provide detailed information about the field names for the recordset returned by the OpenSchema method.

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.

 SubOpenSchemaTableTables() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset     'ConnecttotheNorthwinddatabase Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" Setrst1=cnn1.OpenSchema(adSchemaTables)     'Printjusttables;otherselectioncriteriainclude 'TABLE,ACCESSTABLE,andSYSTEMTABLE DoUntilrst1.EOF Ifrst1.Fields("TABLE_TYPE")="TABLE"Then Debug.Print"Tablename:"&_ rst1.Fields("TABLE_NAME") EndIf rst1.MoveNext Loop     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

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.

 SubOpenSchemaTableViews() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset     'ConnecttotheNorthwinddatabase Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     Setrst1=cnn1.OpenSchema(adSchemaTables)     'Printjustviews;otherselectioncriteriainclude 'TABLE,ACCESSTABLE,andSYSTEMTABLE     DoUntilrst1.EOF Ifrst1.Fields("TABLE_TYPE")="VIEW"Then Debug.Print"Viewname:"&_ rst1.Fields("TABLE_NAME") EndIf rst1.MoveNext Loop     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

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.

 SubOpenSchemaConstraints() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimint1AsInteger Dimint2AsInteger DimiAsInteger     'ConnecttoabackupoftheNorthwinddatabasewitha 'cleansetofconstraints Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;"     'Opentheconstraintsinthedatabase Setrst1=cnn1.OpenSchema(adSchemaCheckConstraints)     'Demonstratesyntaxforenumeratingthecolumnnames 'intherecordsetreturnedbytheOpenSchemamethod; 'thecolumnsvarydependingonthemethod'sparameter Fori=0Torst1.Fields.Count-1 Debug.Printrst1.Fields(i).Name Nexti     'Findthelengthofeachofthefirsttwocolumns int1=FindTheLongest(rst1,"CONSTRAINT_NAME")+1 rst1.MoveFirst int2=FindTheLongest(rst1,"CHECK_CLAUSE")+1 rst1.MoveFirst     'Printcontraintnames,expressions,anddescriptions DoUntilrst1.EOF Debug.Printrst1(2)&String(int1-Len(rst1(2)),"")&_ rst1(3)&String(int2-Len(rst1(3)),"")&""&rst1(4) rst1.MoveNext Loop     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing EndSub     FunctionFindTheLongest(rst1AsADODB.Recordset,_ FieldIndexAsString)AsInteger DimLengthAsInteger     'Looptoreturnlongeststringinafield DoUntilrst1.EOF Length=Len(rst1.Fields(FieldIndex)) IfLength>FindTheLongestThen FindTheLongest=Length EndIf rst1.MoveNext Loop     EndFunction 

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.

click to expand
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.

 SubOpenSchemaTableTablesWithArray() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset     'ConnecttotheNorthwinddatabase. Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb;" 'Queryforuser-definedtableswithCriteriaargument 'specifiedasanarray Setrst1=cnn1.OpenSchema(adSchemaTables,_ Array(Empty,Empty,Empty,"Table"))     'Printjusttables;otherselectioncriteriainclude 'TABLE,ACCESSTABLE,andSYSTEMTABLE. DoUntilrst1.EOF Debug.Print"Tablename:"&rst1("TABLE_NAME") rst1.MoveNext Loop     'Cleanupobjects. rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net