Mike Gunderloy’s first answer addresses issues around client/server development, particularly the interactions between Access pass-through queries, SQL Server stored procedures, and Access forms. With that out of the way, Mike then shows how to tame some of the new behavior of Access 2000-2003.
As part of my client/server application, I’ve been trying to pass parameters to SQL Server queries. I figured out how to execute a stored procedure using a pass-through query, but now I want to pass a parameter to it. I can do this when I save the parameter in the pass-through query itself, but not when the value is displayed on a form. Help!
When I was out teaching Access courses, this sort of question came up quite frequently. This seems to be one of the areas where it’s hard to put together all of the pieces. And no wonder! There are a lot of pieces to put together. Let’s take a few moments and set up the basic situation, and then walk through the techniques involved. I’ll use Access 2000 and a local copy of the SQL Server engine, but the principles are the same no matter which version of Access you use, or where the data is located.
The SQL Server version of the Northwind database contains a stored procedure named CustOrdersOrders, which returns the orders for a particular customer. Figure 1 shows the definition of this stored procedure within SQL Server. This stored procedure takes a customer ID as an input parameter and returns the orders for that customer.
To display the results of this stored procedure from an Access database, you must create a pass-through query. To create a pass-through query, follow these steps:
- Create a new, blank query (that is, don’t add any tables to the query when you create it).
- Switch the query to SQL view and delete the default text that Access puts in every query.
- From the Access Query menu, choose SQL Specific | Pass-Through to tell Access that you want this query to be executed on the server.
Now that you’ve created the query, you could add the SQL statement to be executed. Before doing that, however, I’ll provide the information about which database server the query is to be sent to. This information is provided through the ODBC connect string:
- Open the property sheet for the query by selecting View | Properties from the menu.
- Click the Build button for the ODBC Connect String.
- Choose the Machine Data Source tab and click on the New button.
In the following forms, take the defaults, with these exceptions:
- Select the SQL Server driver
- Name the data source Local for the “What name do you want to use to refer the data source?” prompt.
- Enter the name of the computer that you want to work with for the “Which SQL Server do you want to connect to?” prompt. If you’re using Access 2000 and have installed the MSDE, enter “(local)” (without the quotes) as the name of the server.
- Enter your user ID and password for the database.
- Select Northwind as the database for this connection.
Save everything, and you’ll be back at the query with the Connect String filled in. It should be something like this:
Now you can finish building the pass-through query by entering the SQL to be executed. This SQL statement will call the stored procedure CustOrdersOrders and display the orders for the customer ALFKI:
If you switch the query to datasheet view, you should see the customer’s records. Save the query as qryCustOrdersOrders.
Notice that you have to use single quotes, not double quotes, to surround strings for SQL Server. This is a key point: When you’re typing SQL for a pass-through query, you’re not typing Access SQL; you’re typing SQL to be sent directly to the server for execution. So, in cases where there are differences of syntax, you need to use the server’s syntax, not the Access syntax.
Now let’s get a step closer to the original question by putting the Customer ID on a form rather than directly in the SQL. In the sample database, I created a form called frmID and placed a text box, named txtID, on it. I also added a command button named cmdShow, whose code will modify the pass-through query and then open it.
Here’s where things get tricky for many people. The query’s SQL statement is stored in the SQL property of the QueryDef object. What the cmdShow code needs to do is open the QueryDef object and make a change to the SQL. For most of us, the first attempt at doing this is to use code like this:
Private Sub cmdShow_Click() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("qryCustOrdersOrders") qdf.SQL = "CustOrdersOrders Forms!frmID!txtID" qdf.Close DoCmd.OpenQuery "qryCustOrdersOrders" End Sub
The idea here is to take the value displayed in the control and use it as the value of the parameter for the stored procedure. It’s the right idea, but the wrong syntax. Figure 2 shows what happens if you try to execute this procedure.
The problem is simple. We’ve sent the server a string of text for execution. That string of text is “CustOrdersOrders Forms!frmID!txtID.” The server has no clue what an Access form is. SQL Server doesn’t know how to resolve an Access form reference, and it’s not about to learn.
To make this technique work, you need to resolve the reference yourself. That is, if the user types BONAP in the control, you need to send the server a string that includes the literal value BONAP, not the name of the control that’s displaying BONAP. Here’s a second try:
Private Sub cmdShow_Click() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("qryCustOrdersOrders") qdf.SQL = "CustOrdersOrders " & Forms!frmID!txtID qdf.Close DoCmd.OpenQuery "qryCustOrdersOrders" End Sub
This version of the procedure uses the ampersand string-concatenation operator to stick the value in the control on the end of the stored procedure name. Suppose the control contains BONAP. In that case, the QueryDef’s SQL statement will be changed to this:
If you’re an experienced Access developer, you’ll look at this version and think that there’s still a problem. Comparing that SQL statement with the original version, it’s apparent that we’re missing the single quotes around the Customer ID. Surely, this won’t work.
Actually, it does work, believe it or not. To understand why, you need to look at the “plumbing” between Access and SQL Server just a bit. Access uses ODBC to connect to SQL Server when you’re writing a pass-through query. When ODBC connects to SQL Server, ODBC automatically sets some connection properties in order to make the communication with the server more compliant with the SQL-92 standard used. One of those settings concerns the treatment of quoted identifiers. With this option set on, there’s no need to put quotes of any sort around literal strings being used as arguments to a stored procedure.
However, there’s certainly nothing wrong with supplying the quotes. Furthermore, supplying them makes the procedure a bit more resilient; it doesn’t depend on a particular version of SQL Server. So, I’d rewrite the procedure just a bit more:
Private Sub cmdShow_Click() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("qryCustOrdersOrders") qdf.SQL = "CustOrdersOrders '" & _ Forms!frmID!txtID & "'" qdf.Close DoCmd.OpenQuery "qryCustOrdersOrders" End Sub
Now the SQL statement being sent to the server looks something like this:
If you look back to the start of the discussion, you’ll see that that’s exactly analogous to the original SQL statement used when I created the pass-through query in the first place.
Normally you wouldn’t want to update the SQL property of a query before executing it. By creating a query, you avoid having to have the query parsed, compiled, and optimized every time you submit the SQL statement that it contains. Changing the SQL statement in the query throws that benefit away. However, since the SQL statement in a pass-through query is going to be submitted to the remote database for execution, Jet isn’t going to do any preparation of the query anyway. Additionally, the benefit of using stored procedures is that SQL Server does prepare the stored procedure for execution just like Jet prepares queries.
I’m not impressed with some of the new features in Access 2000. My menus keep changing, and my forms have property sheets in form view. How do I turn this stuff off?!
I occasionally wonder whether Microsoft has someone on staff whose job it is to just invent new technologies whether we need them or not. That’s not quite fair, of course; they actually do study whether people like new stuff before they release it. Be that as it may, if you’ve been an Access developer for awhile, you might want to make Access 2000 behave a bit more like Access 97.
The menu-changing is part of a feature called IntelliMenus. The idea is that the Access 2000 menus start off by showing only the most common options. If you want to see the less common options, you can click the little chevron character at the bottom of the menu. As you work with Access, IntelliMenus adds options that you actually use to the short form of the menus, and drops options you haven’t used. Figure 3 shows an IntelliMenu before and after expanding, for those who haven’t seen this feature in action.
Some people find that they like this feature after awhile, but most developers I’ve spoken with consider it a nuisance. Fortunately, it’s easy to return to menus that show everything. You might think this would be set with Tools | Options, but you’d be wrong. In fact, to turn IntelliMenus off, select View | Toolbars | Customize, choose the Options tab of the Customize dialog box, and then uncheck the box for “Menus show recently used commands first.” Once you’ve done this, the Access menus will always display every available command as soon as you open them.
Suppressing Property Sheets in form view is a bit harder to work around. If you save a form in Access 2000 with the property sheet open, then the property sheet will open the next time you open the form–even if you open it in form view rather than in design view! Unfortunately, there’s no global option to turn this behavior off. Each form has a property named AllowDesignChanges that can be set to “All Views” or “Design View Only.” If you set this property to “Design View Only,” then the property sheet will only be available when the form is open in design view, as it was in Access 97.
You might want to run a procedure to set this property on every form when you’re ready to distribute a database to end users, so that they don’t face any property sheets. Something like this will do the trick:
Sub HidePropertySheets() Dim frm As Form Dim obj As AccessObject For Each obj In CurrentProject.AllForms DoCmd.OpenForm obj.Name, acDesign Set frm = Forms(obj.Name) frm.AllowDesignChanges = False DoCmd.Save acForm, frm.Name DoCmd.Close acForm, frm.Name Next obj End Sub
Once you’ve set the AllowDesignChanges property to False, the property sheet will stay hidden in form view, and the property sheet toolbar button will be disabled in form view as well.
Your download file is called SA9912AA.ZIP
Sidebar: Foundation Concepts: Client/Server Development and Pass-Through Queries
In both client/server development and desktop database development, Access hands its queries over to the database engine for processing. With desktop database development, the database engine is Jet, which executes on the same computer as Access. With client/server development, the query is passed to a remote database engine (for example, SQL Server) running on a separate computer on the network. The remote database engine processes the query, retrieving data and passing it back over the network to the computer that Access is running on. Efficiency in client/server applications primarily consists of turning as much of the query processing as possible over to the remote database server as possible. With pass-through queries, all that’s passed to the remote database server is the SQL statement, and all that’s returned is just the data that the SQL statement requested. In addition, pass-through statements allow you to access features of the remote database server (like stored procedures) that Jet doesn’t support.
Other Related Pages
Migrating Access (and Outlook Data) to SQL Server