|
|
ACCESS DATABASE
Fundamentals of Client-Server Development in Access
Here are different techniques for implementing client-server technology in Access.
By Alison Balter
Many people think placing an Access database on a file server magically transforms it into a client-server database. This is far from true! Imagine a customer table with 100,000 rows and the Access application end-user wants to retrieve the five customers located in Alaska. People don't realize that when data is stored in an Access database, all 100,000 rows may be returned to the workstation depending on the design of the table. If you modify this scenario so you use Access as a front-end to data stored in a SQL Server database, you guarantee only the five rows you need are returned.
These concepts weren't important when there was a clear demarcation between personal computer applications and mainframe applications. Today, as PCs take on tasks previously relegated to mainframes, it's imperative you understand how Access can play into the client-server model. In this article, I explore the options for using Access 97 and 2000 in a client-server environment. I cover the techniques you need to know to make your client-server endeavors successful.
In a traditional Access application, the application objects, such as forms, are stored in an Access database (.MDB). The data is also stored in an Access database, either on the local machine, or, most often, located on a file server. The problem is that an Access database can never act as a true back end because no data processing occurs except on the machine running Jet. Access uses the Microsoft Jet Engine, running on the user's workstation, to communicate with data stored in an Access MDB database file stored on the file server. This results in significant network traffic and processing overhead on the client machine. Client-server technology can help solve this problem.
To help you understand where Access and client-server technology meet, here are three different roles Access can take in an application design. You can use Access as a front end:
- Using links to communicate to a back end database server
- Using SQL pass-through to communicate to a back end
- Using Access Data Project as front end
Let's take a look at each option in detail.
Use linked ODBC tables
In this scenario, the application links to back-end tables stored on a database server such as Microsoft SQL Server. You can implement this using both Access 97 and Access 2000. After the back-end tables are linked to the front-end application database, for the most part, they're treated like any other linked tables. Access uses ODBC (Open Database Connectivity) to communicate with the back-end tables stored on the database server. ODBC lets an application access heterogeneous data in a standard way.
When you use linked tables, 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. Furthermore, ODBC is quickly being replaced by the ADO/OLE DB technology. With the new ADO/OLE DB technology, you can use native drivers to communicate to back-end data sources, such as Microsoft SQL Server. This means the additional layer of ODBC translation is eliminated. That's why using SQL pass-through queries or ADPs might be better solutions.
The easiest method of accessing data on the server is to link to the external tables. These linked tables act like native Access tables. When you link to remote tables, Access analyzes the fields and indexes in the tables so it can achieve optimal performance. It's important to re-link the tables if the structures of the remote tables change. This is because Access only knows about changes to the structure if the link is recreated when the structure changes.
To link to a remote table, right-click within the Database window and select Link Tables. From the Files Of Type drop-down list, select ODBC Databases. The Select Data Source dialog appears. This dialog has two tabs: File Data Source and Machine Data Source. You use the File Data Source tab to select from the file DSNs that have been defined. These are the data sources available to all users on all machines. You use the Machine Data Source tab to select from the user and system data sources you've defined. A User data source is available to just one user and just one machine, whereas a System data source is available to all users of a machine.
You can select an existing data source or define a new data source directly from the Select Data Source dialog. After you select a data source, you're prompted with a Login dialog. You can't obtain access to the server data unless you have a valid login ID and password.
If you successfully log onto the server, you're given a list of tables contained in the database that the data source is referencing. Here, you select the table(s) you want to link to.
After you select one or more tables, click on OK. Linked tables appear in the Database window.
Use pass-through queries
One of the bottlenecks of linked tables is the translation of the Access SQL statement by Jet to ODBC SQL. The ODBC SQL is then translated by the appropriate ODBC driver to a back-end specific SQL statement.
This translation can be slow, plus you might want to bypass the translation process because:
- Access SQL might not support some operation that's supported by the native query language of the back end.
- Either the Jet Engine or the ODBC driver produces a SQL statement that isn't optimized for the back end.
- You want a process performed in its entirety on the back end.
The pass-through query (available in Access 2000 and 97) is written in the syntax specific to the back-end database server. Although the SQL statement passes through the Jet Engine, neither Jet nor ODBC performs any translation on the statement. The ODBC Manager sends the query directly to the ODBC driver. The ODBC driver passes the query to the back end without performing any translation. In other words, what was sent from Access is exactly what the SQL database receives. The Jet Engine, the ODBC Manager, and the ODBC Driver aren't eliminated entirely: They're still there, but have much less impact on the process than with the attached tables. 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. This is because all processing is done on the server, and no data is sent over the network wire. For this reason, I try to perform all batch updates using pass-through queries.
Although pass-through queries are useful, they aren't a panacea. The results of a pass-through query aren't updateable, for example. Furthermore, because pass-through queries are written in the back end's specific SQL dialect, you must rewrite them if you swap out your back end. Also, you can't parameterize a query so it prompts the user for values. For these reasons and others, you generally use pass-through queries in conjunction with other solutions.
Create a pass-through query in an MDB file
You can use the Access query builder to help create a pass-through query. Choose Query > SQL Specific > Pass-Through. This brings up a text-editing window where you can enter the query statement. The SQL statement you enter must be in the SQL-flavor specific to your back end.
Execute a pass-through query using code
You can execute a pass-through query using DAO or ADO code. To execute a pass-through query using ADO code:
1. Create a connection to the SQL Server database.
2. Use the Execute method of the Connection object or the Command object to execute the SQL statement on the back-end database server. The Command object is more flexible and powerful than the Connection object, particularly when you're sending parameters to the stored procedure. As with a SQL statement created using the user interface, the statement you create must be in the syntax specific to your particular back end.
Here's the code to execute the query:
Sub PassThroughQuery()
Dim cmd As ADODB.Command
Dim strConnectString As String
strConnectString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Initial Catalog=pubs"
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnectString
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE dbo.Sales SET Qty = Qty + 1"
cmd.Execute
End Sub
This subroutine uses a connect string that connects to a database (initial catalog) named Pubs. It uses the native OLE DB SQL Server Provider to optimize performance, and NT integrated security to prevent the user from having to log on twice. The example executes a pass-through action query that updates the Qty field of each record to Qty+1.
As you saw, one method of executing a pass-through query is using the Execute method of the Command object without the use of a recordset object. However, this method doesn't let you execute queries that return data. Using a Command object in conjunction with a Recordset object, you can execute a pass-through query that returns records. Here's how:
Sub PassThroughQueryResults()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnectString As String
strConnectString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Initial Catalog=pubs"
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnectString
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM dbo.Sales WHERE Qty > 5"
Set rst = cmd.Execute
Do Until rst.EOF
Debug.Print rst!Stor_id, rst!Qty
rst.MoveNext
Loop
End Sub
The benefits of using Command objects become more apparent when you execute parameterized stored procedures. In the next section, you see how a command object is used to return the results of a parameterized stored procedure into a recordset.
Execute a stored procedure
You can execute stored procedures on a back-end database server. A stored procedure is similar to a query or program, stored and run on the back end. An example is the SQL Server 7.0 system stored procedure named sp_columns, which returns information on the fields in a particular table. To execute sp_columns from the Query Design window, simply type the name of the stored procedure and any parameters it must receive. If you enter a valid connect string, the user isn't prompted to log on at runtime. The Returns Records property is also important. In this case, you set the value of the property to Yes so you can see the results of the stored procedure. If the stored procedure doesn't return records, you want to set this property to No. Otherwise, you receive an error message indicating no rows were returned.
This code executes the sp_columns stored procedure using ADO code:
Sub StoredProcedure()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnectString As String
strConnectString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Initial Catalog=pubs"
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnectString
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_columns(‘sales')"
Set rst = cmd.Execute
Do While Not rst.EOF
Debug.Print rst!Column_Name
rst.MoveNext
Loop
End Sub
Here's how the code works. It creates and opens a Command object, then uses a Recordset object to hold the results of executing the stored procedure. Notice that the sp_columns stored procedure receives a parameter, and the parameter is passed within parentheses. The adCmdStoredProc constant, when designated as the CommandType of the Command object, tells ADO the CommandText parameter for the Execute method is a stored procedure name.
When multiple parameters are used, it's best to specify each parameter separately as part of the Parameters collection, rather than as part of the CommandText property. Here's an example:
Sub StoredProcedureResults(intPercentage As Integer)
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnectString As String
strConnectString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Initial Catalog=pubs"
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnectString
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "byRoyalty"
cmd.Parameters("@Percentage") = intPercentage
Set rst = cmd.Execute
Do Until rst.EOF
Debug.Print rst!Au_ID
rst.MoveNext
Loop
End Sub
The parameters collection of the Command object designates the value for the parameter called "percentage". If additional parameters are required, you can easily designate them, each in a separate line of code.
Use an ADP to work with client-server data
In Access 2000, you have an additional option when working with a back-end database server. You can use an Access Data Project (ADP), which bypasses Jet entirely. An Access project contains only code-based objects, such as forms, reports, data access pages, macros, and modules. All tables, views, database diagrams, and stored procedures are stored in a SQL Server database. After you've connected with a SQL Server database, you can easily view, create, modify, and delete SQL Server objects. In this scenario, neither the Jet engine nor ODBC is involved.
Access projects have advantages over the other methods of accessing client-server data I've covered:
- They provide direct access to a SQL Server database.
- You can easily create and modify SQL Server tables, views, database diagrams, and stored procedures, all from the Access development environment.
- You can backup and restore the SQL Server database, maintain SQL Server security, and perform SQL Server replication tasks.
- You can build forms, reports, data access pages, and modules that access SQL Server data, without involving Jet.
- The client side requires fewer resources.
- The server is responsible for all query processing.
- You can use functionality not available when accessing ODBC via Jet.
- You can execute asynchronous queries -- you don't have to wait for a query to complete execution before you begin another operation.
- You can perform batch updates -- You can cache changes locally and then submit them to the server as a batch.
- You can run queries that return multiple result sets.
- You can easily limit the number of records returned in a result set.
- You can easily monitor messages and errors generated by the remote data source.
Access Data Projects also have several disadvantages:
- You can't create local tables. This means the row source of static combo boxes and list boxes, for example, must be SQL Server tables. This results in unnecessary network traffic being generated as these combo boxes and list boxes are populated.
- You can't create local queries. What were tables in your Access database (.MDB) must be created as views or stored procedures in your Access data project (.ADP). Even the most experienced query experts may find it challenging and time-consuming to rebuild complex Access queries as views and stored procedures.
- The process of building an application using an ADP file involves a learning curve.
Create an ADP
Whereas an Access database uses the Jet engine and ODBC to access client-server data, Access Data Projects use OLE DB. ADP files can directly access data stored in a Microsoft SQL Server 7.0 database, a MSDE database, or a Microsoft SQL Server 6.5 database with Service Pack 5 installed. Using SQL Server 7.0 linked servers, you can access data stored in any format that Microsoft SQL Server 7.0 can access.
If you use an Access database (.MDB) to access client-server data, you must use linked tables and SQL pass-through queries to access and manipulate the data. If you need to modify the structure of tables stored on the server, you must do so within the SQL Server environment.
To create an Access Data Project:
1. Select File > New. The New dialog appears.
2. Two options let you build an Access Data Project. The first lets you create an ADP file using an existing SQL Server database. The second lets you build an ADP file based on a new SQL Server database. If you choose to create a project based on an existing database, the File New Database dialog appears. Enter the name and location of the ADP file and click on Create.
The Data Link Properties dialog lets you specify connection information about the SQL Server database that the ADP file is associated with.
3. Enter the server name, security information, and the database name to which you want to connect.
4. Click on the Advanced tab to enter advanced options or the All tab to view or modify any connection options.
5. When you're finished specifying all options, click on the Connection tab and click on Test Connection to ensure all settings have been specified correctly.
6. Click on OK to create the Access Data Project.
Figure 1 shows an ADP file. Notice the tables appear as if they were contained within the ADP file. In fact, they're stored in the SQL Server database. The important point here is the tables aren't linked! Although they're stored on the database server, they can be manipulated as if they're part of the ADP.
Figure 1: Looks are deceiving -- It looks like these tables are contained in an ADP file, but they're stored in a SQL Server database.
The ADP file shows you the tables, views, database diagrams, and stored procedures contained in the SQL Server database.
As you can see in figure 1, the database window looks slightly different for an ADP file than it does for an MDB file. The Objects list contains Tables, Views, Database Diagrams, and Stored Procedures, rather than Tables and Queries. As mentioned earlier, no local tables or queries can be stored in an ADP file.
When working with an ADP file, remember any changes you make within Access are immediately reflected within the associated SQL Server database.
For example, to create a stored procedure, select Stored Procedures from the objects list. Double-click on "Create Stored Procedure in Designer." The stored procedure editor appears. Type the syntax to create the stored procedure. When you're done, click on Save.
Creating and working with forms, reports, macros, and modules in an ADP file is similar to working with the same objects in an Access database. It's important to recognize the tables, views, database diagrams, and stored procedures are stored on the server, and the forms, reports, macros, and modules are stored within the ADP file.
Get started
Access can play one of several roles in the client-server design model. Access databases (.MDB files) use ODBC to Access client server data. Although it's an older technology, using proper techniques to limit data sent over the network wire, you can create robust client-server applications using Access databases. Access data projects (.ADP files) offer features unavailable in Access databases. Although providing the ability to create and modify server objects, ADP files not only lack important features, they add to the learning curve involved in client-server development.
Alison Balter is an independent trainer and consultant, who specializes in Windows database languages. She's a Microsoft Solution Provider, Certified Professional, and Certified Solution Developer. Alison is the author of Alison Balter's Mastering Access 2000 Development, and more than 300 computer training videos from KeyStone Learning Systems. She's also a regular speaker for Application Developer Training Company. alison@mcgpc.com. Keyword Tags: Client-Server, Code, Database, Database Development, Microsoft, Microsoft Access, Microsoft Jet, Microsoft SQL Server, ODBC (Open Database Connectivity), Querying
ADVISORAMA The ideal age for a boy to own a dog is between 45 and 50. By this time the boy ought to have attained full growth and, provided he is ever going to, ought to know more or less what he wants to make of himself in life. -- Robert Benchley, writer (1889-1945)
|
ARTICLE INFO
Web Edition: 2000.04.20, Doc #04708
 FREE ACCESS
|
SUBSCRIPTION STATUSYou are not signed-in. If you are a subscriber to this publication, sign-in above to access locked articles. To subscribe or renew go to www.AdvisorStore.com.
|
Get it all -- every current and past ADVISOR tech/business publication, now all-in-one subscription, with new articles and a huge reference library packed with expert advice, how-to and downloads. Subscribe now to get it all.![]()
Read the advanced guide to creating custom business database solutions with FileMaker software. Subscribe now to gain access to all the archives and downloads.![]()
Learn the fundamentals of using FileMaker Pro software. Every issue gives you step-by-step instructions on creating the databases you need. Subscribe now!![]()
![]()
![]() ![]()
Submit your tips, techniques and advice and let Advisor promote your business and build your career. Show the world what you know!![]() ![]()
|
|