Developing with Access and SQL Server - MS Access

In this section, you will look at some of the changes and design techniques that you might consider using once you have converted your data to SQL Server and relinked your Access database front-end application.

The dbSeeChanges Constant
When you have code that opens a Recordset on data that includes a column with the IDENTITY property (equivalent to an AutoNumber), you will find that you need to add an additional constant called dbSeeChanges as an option on the OpenRecordSet command; otherwise, you get the error shown below.

An Error message indicating the need to add dbSeeChanges.

An Error message indicating the need to add dbSeeChanges.

Pass-Through Queries
With pass-through queries, you can create a query that by-passes the checking that Access normally performs when writing SQL. These queries can be used either to execute an operation (when they don’t return data) or return a data set. If returning data, then the Recordset is always read-only. The SQL written in the query does not support the Query grid because it is written in T-SQL. One of the many uses for pass-through queries is to execute both your own as well as built in stored procedures. These queries can be created by using either DAO code or the query design tool.

To interactively create a pass-through query, start by creating a normal query, but don’t select any tables. Next, on the Design tab, click the Pass-Through icon, shown below.

When creating a pass-through query, you should also display the property sheet, because you will need to set the ODBC Connection String.

When creating a pass-through query, you should also display the property sheet, because you will need to set the ODBC Connection String.

The following list summarizes some of the key properties:

  • ODBC Connect Str Use the builder when you have clicked into this property to pick your connection string or paste in a valid string.
  • Returns Records Set this according to whether the query returns a Recordset.
  • Log Messages Stores messages returned from SQL Server in a user-specific table.
  • ODBC Timeout You might choose to change this to 0 and avoid early timeouts caused by delays in network traffic.
  • Max Records Limits the maximum number of records being returned.

In the example shown in Figure , you call the built-in stored procedure sp_tables to return a list of tables in the database. You could use the alternative syntax including the exec keyword as exec sp_tables (but this is not essential). You are allowed to have comments using -- or /*..*/ added to the body of the SQL, but you can only have one T-SQL command block executed in the pass-through query.

If you need to execute a sequence of operations, you need to place them in your own stored procedure. In the following example, you start by creating a stored procedure in SQL Server that accepts a single input parameter; you can either run this in Management Studio or you can execute the SQL in a pass-through query to create the stored procedure from Access, in which case, you omit the GO keyword and set Returns Records to No, as shown here:

To execute this from Access, you create a pass-through query that contains the following single line of SQL: uspListCustomers For Country 'UK' You could also get away with using uspListCustomersForCountry UK, but this is a bad idea. To see why, insert a space in the text, as shown in the following line (which will then fail): uspListCustomersForCountry U K

The next parameter to give some attention to is dates; SQL is an ANSI standard and as such, it adopts the convention in the United States for date formats. You need to be very careful about passing a date parameter to a database if you live outside the United States. And if you live in the United States and plan to have your application used in other countries, then you also need to be careful about date formats. There is a very simple shortcut to solving this problem, and that is to format your dates to avoid the issue of mm/dd/yyyy or dd/mm/ yyyy, and format your date in an unambiguous way as a text string, such as the following:

dd mmmm yyyy.

To format a date such as in the preceding code line, in VBA, you use the following syntax:

Format(date,"dd mmmm yyyy")

In the following example (StoredProceduresAndParameters.sql), a stored procedure illustrates this point:

Looking at this problem from the Access perspective, you can write a pass-through query to do this, as shown previously, or this can be written in code. Creating an on-the-fly pass-through query in code is very simple; the only real issue to consider is how you get the database connection string. You can get this from a global variable, or a function; but in the following code you will get this from a table that is already linked in your database (if using your sample database, you will need to relink the linked tables to your SQL Server database), as shown in the following example:

INSIDE OUT Using advanced features in stored procedures from Access
In general, linking Access to a stored procedure is very easy, as you saw in the last example on pass-through queries, which is the only technique available for linking to a stored procedure. The more complex question is whether you want to simply utilize the capability of a stored procedure to return a result, or whether you want to utilize some of the more advanced features of stored procedures, such as passing input and output parameters. If you want to dig deeper into stored procedures and utilize more advanced features, you need to take a look at ADO.

Stored Procedures and Temporary Tables In Access, when you need to perform a sequence of complex operations to deliver a dataset for users to interact with on a form, you might need to create temporary results table, which you then clear down before populating with data with a sequence of queries. For example, in an order processing system, you might want to show past orders, payments, and future orders all in a single list. This involves consolidating data from many parts of the system.

When linking to SQL Server, you have a number of different design strategies from which to choose: you can keep using the existing local temporary tables, or you could put the temporary table in SQL Server and add a column to distinguish between data belonging to different users by using the SUSER_SNAME() function.

One elegant solution to this problem is to utilize SQL Server temporary tables inside a stored procedure; for example, you can create the following stored procedure (Stored ProcedureAndTemporaryTable.sql):

Handling Complex Queries
Converting queries that have complex calculations (we are going to show a simple calculation to explain this feature) can be a big issue when moving to SQL Server. Consider the following Access query:

This technique of referring to calculated columns inside other expressions is a very common technique in Access. Unfortunately, SQL Server does not support this, and most migration tools are unable to perform a successful conversion of the SQL. You have several options when converting this SQL. The simplest option is to restate the calculations in full for each calculated field.

The second option is to create SQL Server functions for parts of the calculation, and then modify the calculation to use these functions.The third option is to create a nested query; you then order the calculations so that they are only referenced by an out level of the nesting. The following code demonstrates the three solutions (ComplexCalculation.sql):

INSIDE OUT Case statements
At this point, you should appreciate that T-SQL takes writing SQL in Access to a more sophisticated level, and one feature that you don’t want to miss is the CASE statement (often used to replace IIF structures). There are two forms for this, SIMPLE and SEARCHED. To illustrate this, the following code creates a very simple table that lists the months in a year, demonstrating the two forms of this statement (CASE.sql):

Performance and Execution Plans
Access is a bit like a black box in that you can extract some information on what it is doing behind the scenes, (but unfortunately, not a lot of information). By comparison, SQL Server is a much more open environment, and one way to investigate and resolve performance problems is the use of execution plans.

To begin, you should appreciate that not all of the information that you see in the plans is easily understood, simply because it is very comprehensive. But within this complex environment is some very valuable information that can help you to solve serious performance issues. In this section, you will construct examples to show how you dig into the strategies used by SQL Server when processing your SQL. You start by creating a copy of some existing data in NorthWind, as shown in the following script (Indexing.sql):

Below it presents part of the Toolbar in Management Studio when working with a query window. This has several options for obtaining more information on the execution of the SQL.

The Include Actual Execution Plan option for obtaining information. You can also include an estimated execution plan and client statistics.

The Include Actual Execution Plan option for obtaining information. You can also include an estimated execution plan and client statistics

Click the Include Actual Execution Plan icon. You can then execute a piece of SQL and view the plan output, as shown below..

This table has no indexing, so SQL Server reads through all of the data by using a Table Scan.

This table has no indexing, so SQL Server reads through all of the data by using a Table Scan.

Executing the following T-SQL creates a CLUSTERED INDEX on the table; all tables should have a clustering key as this is used by other indexes, which are called NONCLUSTERED indexes. The clustered index controls the physical ordering of the data; this can impact the speed of retrieval on searches by the key. When you create a table and add a primary key, SQL Server makes that the clustered index (unless you already have another clustered index on the table). The SQL is shown in the following code, with the results:

-- Next create a clustered index CREATE CLUSTERED INDEX idxTestOrdersClustered ON TestOrders(OrderID) GO

SQL Server uses the clustering key to perform an Index Scan.

SQL Server uses the clustering key to perform an Index Scan.

In the next example, you will be more selective about the rows to retrieve. The result is:.

This is a lot more efficient because you have specified criteria, so SQL Server searches the index by using an Index Seek.

This is a lot more efficient because you have specified criteria, so SQL Server searches the index by using an Index Seek.

Next, you create an index on the OrderDate field, which is used for searching. The SQL is shown in the following code, with the results are:

CREATE INDEX idx_TestOrders_OrderDate ON TestOrders(OrderDate) GO

SQL Server can use the new index to look up the cluster keys, and then use the clustered index to retrieve the record. This is called a Key Lookup.

SQL Server can use the new index to look up the cluster keys, and then use the clustered index to retrieve the record. This is called a Key Lookup.

We mentioned that the clustering key is stored in the NON-CLUSTERED indexes, and it is only the CLUSTERED index that references the actual physical data. This strategy means that often a search requires a Key Lookup to locate the data. The big advantage of this strategy is that if the physical data needs to be moved by SQL Server, for example, to store more data in the record, then only the CLUSTERED index needs to be altered to record where the data row has been moved; there is no impact on all the other indexes. As updates are very common in databases, this strategy minimizes the impact of updates.

In the following example, rather that retrieve all the columns, you only want to retrieve the OrderId..

In this example, because all the columns selected and searched are contained in the index, SQL Server does not need to retrieve the actual rows of data. The index covers all the required fields and this search will be very efficient.

In this example, because all the columns selected and searched are contained in the index, SQL Server does not need to retrieve the actual rows of data

INSIDE OUT Tips on efficient SQL
Writing your SQL efficiently and giving some thought to what you want to index will improve your applications performance. Here are some tips for how to do that:

  • Add indexing to Foreign Keys (Access does this automatically; SQL Server does not). This improves join performance.
  • Select only the required fields in your SQL and avoid using * to select all fields.
  • Make your WHERE clauses as specific as possible.
  • Don’t over-index the database; indexes have a cost as SQL Server needs to maintain the data in the indexes.
  • Choose to index columns that are often searched and contain many different values (they are selective). If your index is not selective, SQL Server might avoid using the index.
  • Run maintenance plans to rebuild indexes with space for new growth.

The ability in SQL Server to analyze specific queries and look for where indexing is not being used can help you plan your indexes. It is a good idea to start with very few indexes and then gradually add them to improve performance. SQL Server uses a costbased optimizer that maintains statistics on the indexes, which can then be used to decide on a minimum I/O cost based strategy for returning your data.

SQL Server Profiler
The SQL Server Profiler is a tool with which you can monitor the communications between your applications and the SQL Server database. Earlier in this chapter, on page 554, we looked at the TIMESTAMP columns and showed the resulting communications between Access and SQL Server when data is updated. When you start the Profiler.

Click the New Trace icon (the first one), and then connect to your SQL Server.

Click the New Trace icon (the first one), and then connect to your SQL Server.

When you create a new trace, a Run button appears at the lower-left of the window that you use to start the trace. The trace window can result in displaying a large amount of information, and you can stop and then restart the trace by using the Toolbar icons.

Start a trace only when you are ready to perform a specific action that you want to monitor.

Start a trace only when you are ready to perform a specific action that you want to monitor.

With the trace, you can then open various parts of your application and monitor performance; if you take a form bound to a large number of records and perform a search for data (Ctrl+F), you will see the large amount of activity that this causes in the Profiler window. Remember to discontinue the trace once you are finished.

The MSysConf Table
After opening your Access application, when Access starts to communicate with SQL Server for example, if you click on a linked table you see the following action in the trace window:

SELECT Config, nValue FROM MSysConf

Access looks to see if it can find a table called MSysConf in your database. If you create a table with this name in your database, you can enter values to control how Access interacts with SQL Server. If you create this table, it’s important to correctly set any values in the table. The following script (MSysConf.sql) creates this table and includes examples of setting values in the table:

Having set the previous option to prevent saving passwords in linked tables, when connecting to SQL Server the check box option to allow saving user names and passwords is no longer displayed (remember to close and then re-open your database if testing this). It is also worth pointing out that when we have tested option 103 (which controls the number of rows that should be retrieved), we have not been able to see any easily observable changes when monitoring traffic by using the Profiler.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics