MS-Access / Getting Started

---

Developing with Access and SQL Server

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.

Sub modSQL_dbSeeChanges()
' Examples illustrating how code needs to be altered
' Adding dbSeeChanges
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("dbo_Products", dbOpenDynaset)
' Corrected code below
 ' Set rst = db.OpenRecordset("dbo_Products", dbOpenDynaset, dbSeeChanges)
    rst.Close
    db.Close
End Sub

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.

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.

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:

CREATE PROC uspListCustomersForCountry @Country NVARCHAR(20)
AS
BEGIN
    SELECT * FROM Customers
    WHERE Country = @Country
END

To execute this from Access, you create a pass-through query that contains the following single line of SQL:

uspListCustomersForCountry '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:

CREATE PROC usp_OrdersByDate @Thedate DATETIME
AS
BEGIN
    SELECT * FROM Orders
    WHERE RequiredDate = @Thedate
END
GO

exec usp_OrdersByDate '1996-08-16'
GO
exec usp_OrdersByDate '16 August 1996'
GO

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:

Sub modSQL_PassingDatesInPassthrough()
    ' example of passing a date which
    ' avoids ANSI formating
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Dim dt As Date
    dt = #8/16/1996#
    Set qdef = db.CreateQueryDef("")
    qdef.ReturnsRecords = True
    ' The following line comes before assigning the SQL property
    ' as the Access will not check the SQL after the connect property
    ' is assigned
    qdef.Connect = db.TableDefs("Orders").Connect
    qdef.SQL = "usp_OrdersByDate '" & Format(dt, "dd mmmm yyyy") & "'"
    Dim rst As Recordset
    Set rst = qdef.OpenRecordset()
    Do While Not rst.EOF
	Debug.Print rst(0)
	rst.MoveNext
    Loop
End Sub

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.