Client/server is one of the hot computing terms of the 90s. It refers to distributed processing of information. It involves the storage of data on database servers that are dedicated to the tasks of processing data as well as storing it. These database servers are referred to as back-ends. The presentation of the data is accomplished by a front-end tool such as Microsoft Access. Microsoft Access, with its tools that assist in the rapid development of queries, forms, and reports, provides an excellent front-end for the presentation of back-end data. As more and more applications are downsized from mainframes and upsized from personal computers, it is becoming necessary for more of us to understand the details of client/server technology.
For years, most information professionals have worked with traditional programming languages. These languages are responsible for both processing and maintaining data integrity within the application. This means that data-validation rules must be embedded within the programming code. Furthermore, these types of applications are record-oriented. All records are read into memory and processed. This scenario has several drawbacks:
The client/server model introduces a separation of functionality. The client, or front-end, is responsible for presenting the data and doing some processing. The server, or back-end, is responsible for storing, protecting, and performing the bulk of processing the data.
Client/server was not as necessary when there was a clear delineation between mainframe applications and personal computer applications. Today, the line of demarcation is becoming blurry. Personal computer applications are beginning to take over many applications that had been relegated to mainframe computers in the past. The problem is that we are still very limited by the bandwidth of network communications. This is one place where client/server can really help.
Many developers are confused about what client/server really is. (In fact, I have participated in many debates in which other developers have insisted that Access itself is a database server application. Well, it's not.) Access is a front-end application that can process data stored on a back-end. In this scenario, the Access application runs on the client machine accessing data stored on a database server running software such as Microsoft SQL Server. Access does an excellent job acting as the client-side front-end software in this scenario. The confusion lies in Access's capability to act as a database server.
Many people mistakenly believe that an Access MDB database file stored on a file server acts as a database server. This is not the case. The difference lies in the way in which data is retrieved when Access is acting as the front-end to a database server versus when the data is stored in an Access MDB file. Imagine the following scenario. Assume that you have a table with 500,000 records. A user runs a query that is based on the 500,000-record table stored in an Access database on a file server. The user wants to see a list of all the Californians who make over $75,000 per year. With the data stored on the file server in the Access MDB file format, all records are sent over the network to the workstation, and the query is performed on the workstation (see Figure 20.1). This results in significant network traffic.
Figure 20.1. Access as a front-end and a back-end.
On the other hand, assume that these 500,000 records are stored on a database server such as Microsoft SQL Server. The user runs the same query. In this case, only the names of the Californians who make more than $75,000 per year are sent over the network. In fact, if you request only specific fields, only the fields that you request are retrieved (see Figure 20.2).
Figure 20.2. Access as a front-end utilizing a true back-end.
What does this mean to you? When should you become concerned with client/server technology and what it can offer you? The following are meant to be guidelines for why you might want to upsize.
As the volume of data within an Access database increases, you will probably notice a degradation in performance. Many people say that 100M is the magical number for the maximum size of an Access database, whereas many back-end database servers can handle databases containing multiple gigabytes of data. Although a maximum size of 100M for an Access database is a good general guideline, it is not a hard and fast rule. You might find that the need to upsize occurs when your database is significantly larger or smaller than 100M. The magic number for you depends on all the factors discussed in the following sections, as well as on how many tables are included in the database. Generally, Access performs better with large volumes of data stored in a single table rather than in multiple tables.
Just as a large volume of data can be a problem, so can a large number of concurrent users. In fact, more than 10 users concurrently accessing an Access database can really degrade performance. As with the amount of data, this is not a magical number. I have seen applications with less than 10 users where performance is awful, and I have seen applications with significantly more than 10 users where performance is acceptable. It often depends on how the application is designed, as well as what tasks the users are performing.
Certain applications by nature demand better performance than other applications. An On-line Transaction Processing system (OLTP) generally requires significantly better performance than a Decision Support System (DSS). Imagine 100 users simultaneously taking phone orders. It would not be appropriate for the users of the system to ask their customers to wait 15 seconds between entering each item that is ordered. On the other hand, asking a user to wait 60 seconds to process a management report that the user runs once each month is not a lot to ask (although many will still complain about the minute). Not only does the client/server architecture itself lead to better performance, but most back-end database servers can utilize multithreaded operating systems with multiple processors. Access cannot.
As a file-server within an organization experiences increasing demands, the Access application might simply exacerbate an already growing problem. By moving the application data to a database server, the reduced demands on the network overall might provide all users on the network with better performance regardless of whether or not they are utilizing the Access application.
Probably one of the most exaggerated situations I have seen is one in which all the workstations were diskless. Windows and all application software were installed on a file server. The users were all concurrently loading Microsoft Word, Microsoft Excel, and Microsoft Powerpoint over the network. In addition, they had large Access applications with many database objects and large volumes of data. This was all stored on the file server as well. Needless to say, performance was abysmal. You can't expect an already overloaded file server to be able to handle sending large volumes of data over a small bandwidth. The benefits offered by client/server technology can help alleviate this problem.
The backup and recovery options offered with an Access MDB database stored on a file server simply do not rival the options for backup and recovery on a database server. Any database server worth its salt sports very powerful uninterruptible power sources (UPSs). Many have hot-swappable disk drives with disk mirroring, disk duplexing, or disk striping with parity (RAID Level 5). Disk mirroring and duplexing mean that data can be written to multiple drives at one time, providing instantaneous backups. Furthermore, some database server tape backup software enables backups to be completed while users are accessing the system. Many offer automatic transaction logging. All these mean that there is less chance of data loss or downtime. With certain applications, this type of backup and recovery is overkill. With other applications, it is imperative. Although some of what back-ends have to offer in terms of backup and recovery can be mimicked by using code and replication, it is nearly impossible to get the same level of protection from an Access database stored on a file server that you can get from a database stored on a database server.
Access offers what can be considered the best security for a desktop database. Although this is the case, the security offered by an Access database cannot compare with that provided by most database servers. Database server security often works in conjunction with the network operating system. This is the case, for example, with Microsoft SQL Server and Windows NT Server. Remember that no matter how much security you place on an Access database, this does not prevent a user from deleting the entire MDB file from the network disk. It is very easy to offer protection from this potential problem, and others, on a database server. Furthermore, many back-end application database server products offer field-level security not offered within an Access MDB file. Finally, many back-ends offer integrated security with one logon for both the network and the database.
The Access MDB file format is proprietary. Very few other products can read data stored in the Access database format. With a back-end database server that supports Open Database Connectivity (ODBC), front-end applications can be written in a variety of front-end application software, all concurrently utilizing the same back-end data.
You need to evaluate the specific environment in which your application will run:
After you answer all these questions, and additional ones, you can begin to make decisions as to whether the benefits of the client/server architecture outweigh the costs involved. The good news is that it is not an all-or-none decision. Various options are available for client/server applications utilizing Access as a front-end. Furthermore, if you design your application with upsizing in mind, moving to client/server will not require you to throw out what you have done and start again.
Before you move on to learn more about client/server technology, let's take a look at the different roles that Access can take in an application design. Several options are available.
Prior to this chapter, you have learned about using Access as both the front-end and the back-end. The Access database is not acting as a true back-end in that it is not doing any processing. The architecture in this scenario is shown in Figure 20.3. The Access application resides on the workstation. Utilizing the Microsoft Jet engine, it communicates with data stored in an Access MDB database file stored on the file server.
Figure 20.3. Access as a front-end utilizing an MDB File for data storage.
In the second scenario, back-end tables can be linked to the front-end application database. The process of linking to back-end tables is almost identical to that of linking to tables in other Access databases or to external tables stored in FoxPro, Paradox, or dBASE. After the back-end tables have been linked to the front-end application database, they can be treated like any other linked tables. Access utilizes ODBC to communicate with the back-end tables (see Figure 20.4). Your application sends an Access SQL statement to the Access Jet engine. Jet translates the Access SQL statement into ODBC SQL. The ODBC SQL statement is then sent to the ODBC Manager. The ODBC manager locates the correct ODBC driver and passes it the ODBC SQL statement. The ODBC driver, supplied by the back-end vendor, translates the ODBC SQL statement into the back-end's specific dialect. The back-end specific query is sent to the SQL server and to the appropriate database. As you might imagine, all this translation takes quite a bit of time. That is why one of the two alternatives that follow might be a better solution.
Figure 20.4. Access as a front-end utilizing links to back-end tables.
One of the bottlenecks of linked tables is the translation of the Access SQL statement by Jet to ODBC SQL, which is then translated by the ODBC driver to a generic SQL statement. Not only is the translation slow, but there might be other reasons why you want to bypass the translation process:
Pass-through queries are covered in more detail in the "Pass-Through Queries" section of this chapter. For now, let's look at what happens when a pass-through query is executed. The pass-through query is written in the syntax specific to the back-end database server. Although the query does pass through the Jet engine, Jet does not perform any translation on the query. Neither does ODBC. The ODBC Manager sends the query to the ODBC driver. The ODBC driver passes the query on to the back-end without performing any translation. In other words, exactly what was sent from Access is what is received by the SQL database. This scenario is shown in Figure 20.5. Notice that the Jet engine, the ODBC Manager, and the ODBC Driver are not eliminated entirely. They are still there, but they have much less impact on the process than they do with attached tables. As you will see in the section on pass-through queries, pass-through queries are not a panacea, although they are very useful. For example, the results of a pass-through query are not updatable. Furthermore, because pass-through queries are written in the back-end's specific SQL dialect, you need to rewrite them if you swap out your back-end. For these reasons, and others, pass-through queries are usually used in combination with other solutions.
Figure 20.5. Access sending a pass-through query to a back-end database.
One additional scenario is available when working with a back-end database server. This involves using the Remote Data Objects (RDO), which ship as part of the Enterprise edition of Visual Basic 4.0. Using RDO, you bypass the Jet engine entirely. SQL statements are written in ODBC SQL. Figure 20.6 illustrates this scenario. Although this might look like a lot of layers, RDO is a very thin wrapper on the ODBC API calls. The SQL statement travels quickly through all the layers to the back-end database. From a performance standpoint, this solution puts Jet to shame. The major advantage of RDO over pass-through queries is that you write the SQL statements in ODBC SQL rather than the back-end-specific SQL. This means that your application is easily portable to other back-end database servers. You can swap out your back-end with little modification to your application. The major disadvantage of RDO is that it cannot be used with bound forms or reports. This means a lot more coding for you. As with pass-through queries, this option can be used in combination with the other solutions in order to gain required performance benefits in mission-critical parts of the application.
Figure 20.6. Access using RDO to communicate to a back-end.
People who talk about client/server use many terms that are unfamiliar to the average database developer. To get a full appreciation of client/server and what it has to offer, you must at least have a general understanding of the terminology.
The following are the most commonly used terms and their definitions:
Many books are devoted solely to client/server technology; one good one is Visual Basic / SQL Server Primer by Heng Tan. Most magazines targeted at developers contain numerous articles on client/server technology: DBMS always contains many excellent articles, and Databased Advisor usually offers numerous articles. Many of the articles are specifically about client/server connectivity using Access as a front-end. Another excellent source of information is the Microsoft Developer Network CD. Offered as a subscription by Microsoft, it includes numerous articles and white papers on client/server technology, ODBC, and using Access as a front-end to a database server.
Suppose your database is using Microsoft Access as both the front-end and back-end. Although an Access database on a file server might have been sufficient for awhile, the need for better performance, enhanced security, or one of the other benefits that a back-end database provides is compelling your company (or your client's company) to upsize to a client/server architecture. The Access tables have already been created and even contain volumes of data. In this scenario, it might make sense to upsize.
Because all the tables have been designed as Access tables, they need to be upsized to the back-end database server. Upsizing means moving tables from a local Access database (or from any PC database) to a back-end database server that usually runs on UNIX, Windows NT Server, OS/2 LAN Server, or as a Novell NetWare NLM.
Another reason that tables are upsized from Access to a back-end server is that many developers prefer to design their tables from within the Access environment. Access offers a more user-friendly environment for table creation than do most server applications.
Regardless of your reasons for upsizing, you need to understand several issues regarding the movement, or upsizing, of Access tables to a database server. Indeed, because of the many caveats in moving tables from Access to a back-end, many people opt to design the tables directly on the back-end. If you do design your tables in Access, you should export them to the back-end and then link them to your local database. As you export your tables to the database server, you need to be aware of the issues covered in the sections that follow.
When exporting a table to a server, no indexes are created. All indexes need to be re-created on the back-end database server.
AutoNumber fields are exported as Long integers. Because most database servers do not support autonumbering, you have to create an insert trigger on the server that provides the next key value. Autonumbering can also be achieved using form-level events, but this is not desirable because the numbering will not be enforced if other applications access the data.
Default values are not automatically moved to the server, even if the server supports default values. You can set up default values directly on the server, but these values do not automatically appear when new records are added to the table unless the record is saved without data being added to the field containing the default value. As with autonumbering, default values can be implemented at the form level, with the same drawbacks.
Validation rules are not exported to the server. They must be re-created using triggers on the server. No Access-defined error messages are displayed when a server validation rule is violated. Your application should be coded to provide the appropriate error messages. Validation rules can also be performed at the form level, but they are enforced if the data is accessed by other means.
Relationships need to be enforced using server-based triggers. Access's default error messages do not appear when referential integrity is violated. You need to respond to, and code for, these error messages within your application. Relationships can be enforced at the form level, but as with other form-level validations, this method of validation does not adequately protect your data.
Security features that you have set up in Access do not carry forward to the server. You need to reestablish table security on the server. When security has been set up on the server, Access becomes unaware that the security exists until the Access application attempts to violate the server's security. Then, error codes are returned to the application. You must handle these errors by using code and display the appropriate error message to the user.
Servers often have much more stringent rules regarding the naming of fields than Access does. When you export a table, all characters that are not alphanumeric are converted to underscores. Most back-ends do not allow spaces in field names. Furthermore, most back-ends limit the length of object names to 30 characters or less. If you have already created queries, forms, reports, macros, and modules that utilize spaces and very long field and table names, these database objects might become unusable when you move your tables to a back-end database server.
Most back ends have many reserved words. It is important that you are aware of the reserved words of your specific back-end. It is quite shocking when you upsize a table to find that field names that you have been using are reserved words on your database server. If this is the case, you need to rename all the fields where a conflict occurs. Once again, this means modifying all the queries, forms, reports, macros, and modules that reference the original field names.
Many back-end databases are case-sensitive. If this is the case with your back-end, you might find that your queries and application code don't process as expected. Queries or code that refer to the field or table name by using the wrong case are not recognized by the back-end database and do not process correctly.
Most properties cannot be modified on remote tables. Any properties that can be modified are lost upon export, so you need to set them up again when the table is exported.
Certain properties and methods that work on Access tables might not work on remote tables. This might necessitate some coding changes after you export your tables.
If you set up your tables and code modules with upsizing in mind, you can eliminate many of the preceding pitfalls. Despite any of the problems that upsizing can bring, the scalability of Access is one of its stronger points. Sometimes resources are not available to implement client/server technology in the early stages of an application. If you think through the design of the project with the possibility of upsizing in mind, you will be pleased at how relatively easy it is to move to client/server when the time is right. In fact, Microsoft provides an Access Upsizing tool, specifically designed to take an Access application and upsize it to Microsoft SQL Server. The Access 95 version of this tool is available on www.msn.com. The Upsizing tool for Access 2.0 performs a lot of the work involved in upsizing a database at the click of a few buttons!
Before you can use Microsoft Access with a database server, you need to load the ODBC drivers. These drivers come with Access, but they do not get installed if you select the standard installation of the product. If you ran a standard installation and want to install the ODBC drivers, you need to rerun setup and choose the Custom installation option. Then select the ODBC checkbox from the Options list box. You also need to load drivers for the specific back-end database servers to which you want to connect. These drivers are usually purchased from the back-end database vendor and often come with a per-seat charge. This means that you must purchase a client license for each user who will connect to the remote data.
An ODBC Data Source is a user-defined name that points to a remote source of data. It contains all the properties of the data source that are necessary to communicate to data stored on a database server.
Before you can access a remote table from Access, you must define it using the ODBC administrator. If you do not define that data source, or if it is not defined correctly, you will be unable to obtain access to the data.
ODBC data sources are set up in the ODBC administrator (see Figure 20.7). Depending on your installation, the ODBC administrator could be a stand-alone application, or it could appear as a control-panel icon. It enables you to create, modify, and delete data sources, and to obtain information about existing drivers. Remember that a data source is simply a user-definable name that stores settings that can be used to access a back-end located on a particular server using a specified driver.
Figure 20.7. The Data Sources window within the ODBC administrator.
When you have entered the ODBC administrator, you should probably set up a new data source. To define a new data source, click on the Add button in the ODBC Administrator dialog. The Add Data Source dialog, where you must select the name of the driver that the data source will use, appears (see Figure 20.8).
Figure 20.8. The Add Data Source dialog.
The list of available drivers varies depending on which client drivers have been installed on the machine. After you select a data source and click OK, you are shown another dialog, which varies depending on which driver you have selected. It enables you to define specific information about the data source that you are creating. An example is the ODBC SQL Server Setup dialog shown in Figure 20.9. As you can see, the ODBC SQL Server Setup dialog enables you to specify information, such as the data source name, server name, network address, and so on. If you click on the Options button, the dialog expands to appear as shown in Figure 20.10.
Figure 20.9. The ODBC SQL Server Setup dialog.
Figure 20.10. The expanded ODBC SQL Server Setup dialog.
The expanded dialog enables you to specify additional information, such as the database name and language name. After you fill out this dialog and click OK, the data source name is added to the list of data sources listed in the ODBC administrator.
After you define a data source, you are ready to connect to it. Four methods can be used to access server data:
The easiest method of accessing data on the server is to link to the external tables. These linked tables act almost exactly like native Access tables. When you link to remote tables, Access analyzes the fields and indexes contained within the tables so that it can achieve optimal performance. It is important to relink the tables if the structures of the remote tables change. The following sections discuss how you can link to remote tables both through the user interface and through code.
To link to a remote table through the user interface, right-click your mouse on the Tables tab of the Database window. Then select Link Tables. Select ODBC Databases from the Files Of Type drop-down list. The SQL Data Sources dialog shown in Figure 20.11 appears.
Figure 20.11. The SQL Data Sources dialog.
You can select an existing data source, or define a new data source, directly from the SQL Data Sources dialog. After selecting a data source, you are prompted for a password. You can't obtain access to the server data unless you have a valid login ID and password. The SQL Server Login dialog is shown in Figure 20.12.
Figure 20.12. The SQL Server Login dialog.
If you successfully log onto the server, you are presented with a list of tables contained within the database that the data source is referencing. Here, you must select the table to which you want to link. The Link Tables dialog is shown in Figure 20.13.
Figure 20.13. The Link Tables dialog.
After you select one or more tables and click OK, you might be prompted with the Select Unique Record Identifier dialog (see Figure 20.14). Selecting a unique identifier for the table enables you to update records on the back-end data source. Select a unique identifier and click OK. The linked tables appear in the Database window (see Figure 20.15). They can be treated like any other table (with a few exceptions that are covered later in the chapter).
Figure 20.14. The Select Unique Record Identifier dialog.
Figure 20.15. The Database window with links to ODBC tables.
You have just learned how you can link to a remote table by using Access's user interface. Now let's take a look at how you can link to the same table by using code. The following subroutine accepts six parameters. They are the name for the Access table, the name of the server database, the name of the server table, the dataset name, the user ID, and the password:
Sub LinkToSQL(strAccessTable, strDBName, strTableName, _ strDataSetName, strUserID, strPassWord) Dim db As DATABASE Dim tdf As TableDef Set db = CurrentDb Set tdf = db.CreateTableDef(strAccessTable) tdf.Connect = "ODBC;Database=" & strDBName _ & ";DSN=" & strDataSetName _ & ";UID=" & strUserID _ & ";PWD=" & strPassWord tdf.SourceTableName = strTableName db.TableDefs.Append tdf End Sub
Here's a sample of how you call the subroutine. The Access table that you are creating is called tblStores. The database name on the server is Pubs. The table to which you are linking is called dbo.Stores, and the dataset name is PublisherData. You are logging in as SA (database system administrator) without a password. The user ID and password could have been supplied as the user logged into your application and could have been stored in variables until needed for logging into the server:
Call LinkToSQL("tblStores", "Pubs", "dbo.Stores", "PublisherData", "SA", "")
This code, and most of the code found in this chapter, can be found in the database called CHAP20EX.MDB.
Views on a database server are like Access queries. They provide a form of security by limiting what rows and columns a user can see. Access is given to the view rather than directly to the underlying table. By default, views are not updatable. You can make a view updatable by including all the fields that comprise the primary key in the view and building a unique index on the primary key. Views can be created in one of two ways:
To create a remote view from Access:
Figure 20.16. Creating a query for a remote view.
Figure 20.17. Error message when creating a remote view.
Once you create a remote view, you can link to it like any other table. If you link to the view, you are prompted with the Select Unique Record Identifier dialog (see Figure 20.18). It is very important to supply Access with a unique index. Otherwise, the results of the view will not be updatable. The view can then be treated as if it were a link to a table.
Figure 20.18. Select Unique Record Identifier dialog after selecting view.
Ordinarily, when you store and execute a query in Access, even if it is running on remote data, Access compiles and optimizes the query. In many cases, this is exactly what you want. On certain other occasions, however, it might be preferable for you to execute a pass-through query because they are not analyzed by Access's Jet engine. They are passed directly to the server, and this reduces the time that Jet analyzes the query and enables you to pass server-specific syntax to the back-end. Furthermore, pass-through queries can log informational messages returned by the server. Finally, bulk update, delete, and append queries are faster using pass-through queries than they are using Access action queries based on remote tables.
Pass-through queries do have their downside. They always return a snapshot, rendering them not updatable. You also must know the exact syntax that the server requires, and you must type the statement into the query window rather than painting it graphically. Finally, you cannot parameterize a query so that it prompts the user for a value.
To create a pass-through query, you can build the query in the Access query builder. To do this, select Query|SQL Specific|Pass-Through. You will be presented with a text-editing window where you can enter the query statement. The SQL statement that you enter must be in the SQL flavor specific to your back-end.
You can also perform a pass-through query using VBA code. In fact, you must create the pass-through query by using VBA code if you want the query to contain parameters that you will pass to the server. Here's one way you can create a pass-through query using VBA code:
The code looks like this:
Sub PassThroughQuery(strDBName As String, _ strDataSetName As String, _ strUserID As String, _ strPassWord As String) Dim ws As Workspace Dim db As DATABASE Dim strConnectString As String strConnectString = "ODBC;DATABASE=" & strDBName & _ ";DSN=" & strDataSetName & _ ";UID=" & strUserID & _ ";PWD=" & strPassWord Set ws = DBEngine(0) Set db = ws.OpenDatabase( "", False, False, strConnectString) db.Execute "Update dbo.Sales Set Qty = Qty + 1", _ dbSQLPassThrough End Sub
The routine is called as follows:
Call PassThroughQuery("Pubs", "PublisherData", "SA","" )
This subroutine uses a connect string that connects to a database called Pubs, with a datasource named PublisherData, a userID of SA, and no password. It then executes a pass-through query that updates the Qty field of each record to Qty+1.
As you saw, one method of executing a pass-through query is to open the database using the OpenDatabase method and then execute the query using the Execute method on the database object. The limitation of this method is that the Execute method does not enable you to execute queries that return data. There is another method of executing a pass-through query that you can use when you want to return records. It involves creating a query definition within the local database and opening a recordset using a pass-through query or a stored procedure as the SQL property for the query definition. This method is covered in the next section.
You can also execute a stored procedure on a back-end database server. A stored procedure is like a query or program stored on the back-end, and it performs some action. An example is the SQL Server 6.0 stored procedure called sp_columns. This stored procedure returns information on the fields in a particular table. Figure 20.19 illustrates how you would execute the sp_columns stored procedure from the Query Design window. As you can see from the figure, you simply type the name of the stored procedure and any parameters that it must receive. Take a good look at the Query Properties window shown in Figure 20.19. If you enter a valid ODBC connect string, the user will not be prompted to log in at runtime. The Return Records property is another important property. In this case, you want to set the value of the property to Yes so that you can see the results of the stored procedure. If the stored procedure does not return records, as is the case with the Create View pass-through query created in the section titled Linking to Views Rather than Tables, it is important to set this property to No. Otherwise, you receive an error message indicating that no rows were returned. Figure 20.20 shows the results of running the stored procedure.
Figure 20.19. Executing a stored procedure from the Query Design window by typing the name of the stored procedure and any parameters that it must receive.
Figure 20.20. Result of running the sp_columns stored procedure.
The following procedure executes the sp_columns stored procedure using code:
Sub StoredProcedure() Dim ws As Workspace Dim db As DATABASE Dim dbAccess As DATABASE Dim qdf As QueryDef Dim rst As Recordset Set dbAccess = CurrentDb Set ws = DBEngine(0) Set db = ws.OpenDatabase("", False, False, _ "ODBC;DATABASE=Pubs;DSN=PublisherData;UID=SA;PWD=") Set qdf = dbAccess.CreateQueryDef("") qdf.Connect = "ODBC;DATABASE=Pubs;DSN=PublisherData;UID=SA;PWD=" qdf.SQL = "sp_columns 'sales'" qdf.ReturnsRecords = True Set rst = qdf.OpenRecordset(dbOpenSnapshot) Do While Not rst.EOF Debug.Print rst!Column_Name rst.MoveNext Loop End Sub
Here's how it works. Because you want to return records, you cannot use the Execute method (covered in the section Executing a Pass-Through Query Using Code, earlier in this chapter). Another way to execute a pass-through query is to first create an Access QueryDef object. In this case, the QueryDef object is temporary (notice the quotation marks). The Connect property is set for the QueryDef object. Rather than specifying a back-end-specific SQL statement, the SQL property of the QueryDef object is set to the name of the stored procedure and any parameters it expects to receive. The ReturnsRecords property of the QueryDef object is set to True. The OpenRecordset method is then issued on the QueryDef object. This returns the SnapShot from the stored procedure. The Do While loop loops through the resulting recordset, printing the Column_Name column of each row returned from the sp_columns stored procedure.
As you saw, the OpenDatabase method of the Workspace object can be used to execute pass-through queries. This is a very valid use of the the OpenDatabase method. This method can also be used in place of linking to tables to access server data directly. This is generally extremely inefficient, because the data structure is not analyzed and maintained in the Access database engine. With linked tables, the fields, indexes, and server capabilities are all cached in memory so that they will be readily available when needed. Regardless, there are times when you might want to open a database directly. One reason is to preconnect to a server so that you will be connected when you need access to the data. The following subroutine shows how you can use the OpenDatabase function to connect to a remote server database:
Sub OpenRemoteDB(strDBName As String, _ strDataSetName As String, _ strUserID As String, _ strPassWord As String) Dim ws As Workspace Dim db As DATABASE Dim tdf As TableDef Dim intCounter As Integer Dim strConnectString As String Set ws = DBEngine(0) strConnectString = "ODBC;DATABASE=" & strDBName & _ ";DSN=" & strDataSetName & _ ";UID=" & strUserID & _ ";PWD=" & strPassWord Set db = ws.OpenDatabase( "", False, False, strConnectString) For Each tdf In db.TableDefs Debug.Print tdf.Name Next tdf End Sub
The routine is called like this:
Call OpenRemoteDB("Pubs", "PublisherData", "SA", "")
The routine uses the OpenDatabase method of the Workspace object to open the database called Pubs with the connect string specified. It then loops through the collection of table definitions, listing all the tables found within the remote server database.
In this chapter, you learned to be concerned with client/server when you are dealing with large volumes of data, large numbers of concurrent users, demand for faster performance, problems with increased network traffic, backup and recovery, security, and a need to share data among multiple front-end tools. You also learned the roles Access can play in the application design model. Some client/server buzzwords were also introduced.