This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Upsize Your Database: Convert Your Microsoft Access Application to Take Advantage of SQL Server 7.0

Michael McManus

This article assumes you're familiar with Visual Basic, Access, SQL Server

Level of Difficulty    1   2   3

What if you need to convert an existing Microsoft Access 97 database application into a true client-server application that is based on a SQL Server back end? If you know a little about Visual Basic and SQL Server, it's easy to make your app take advantage of the power and scalability provided by SQL Server 7.0.
      Using some concrete code examples, this article takes you step by step through converting the native Jet queries in your Access application into stored procedures and pass-through queries that SQL Server can use. You'll also learn how to pass on parameters when your client-server app calls these SQL Server stored procedures and queries. onverting a large application written in Microsoft® Access 97 with an Access back end to a true client-server application with a Microsoft SQL Serverâ„¢ 7.0 back end can be a daunting proposition. I have identified some of the challenges you may faceâ€"and some solutions. By using these techniques, you won't have to attach your SQL Server 7.0 tables to the front end, greatly reducing the size of your application. Many of these concepts are well-known to developers accustomed to working with Visual Basic®, but may be unknown to developers who specialize in Access and Visual Basic for Applications who have never worked with Visual Basic.

Replacing Jet Queries

      Your first priority will be to utilize SQL Server 7.0 stored procedures and pass-through queries to replace most or all of your native Jet queries. Stored procedures allow processing of complex queries to be handled on the server. But once you have replaced your Jet queries with stored procedures on the server, what is the best way to call them, particularly when they require parameters?
      Passing these variables to the SQL property of a pass-through query is not as easy as building them into a saved Jet query. When passed a valid SQL string, the following function will execute a pass-through query or stored procedure that takes the place of Jet action queries:

Function ExecuteSPT(sqlstr As String) As Integer

' Run a SQL Server pass-through query or stored procedure
' Use this function to run action queries because it will not
' return any records.
On Error GoTo spt_err
Dim qdf_SP As QueryDef
Set qdf_SP = gDB.CreateQueryDef("")
qdf_SP.ReturnsRecords = False
qdf_SP.Connect = Gstrconnect
qdf_SP.SQL = sqlstr
qdf_SP.ODBCTimeout = 15
qdf_SP.Execute
qdf_SP.Close

spt_exit:
ExecuteSPT = 1
Exit Function
spt_err:
ExecuteSPT = 0
End Function

This function can be reused throughout your application, and it will eliminate the need to save a large number of queries in your client application.
      Gstrconnect is your connection string and should be a global variable. Utilizing this connection string allows you to use DSN-less connections throughout your application, eliminating the need for a DSN on client workstations. Here is an example of Gstrconnect.

ODBC;DRIVER=SQL Server;SERVER=my-sqlsvr;UID=name;
PWD=;WSID=anywsid;DATABASE=mydb

My-sqlsvr is the name of the SQL Server, name is my SQL Server login ID, anywsid is any valid workstation ID, and mydb is the name of the database on the SQL Server.
      The Gstrconnect value can be hardcoded, but it is better to set it when your application opens, like this:

Dim db as database
Dim tblDef As TableDef
Set db = CurrentDb()
Set tblDef = db.TableDefs("tblMyTable")
gstrconnect = tblDef.Connect

This function can be used to execute action commands (Update, Insert, or Delete) with stored procedures or dynamic SQL statements. I'll show you some examples of each.
      To execute the stored procedure sp_Update_StartDate, which requires the variables @StartDate and @ID that exist in your application as gStartDate and gID, you'd use code like this:

Dim sp_sql as string
sp_sql = "Execute sp_Update_StartDate " & gStartDate &", "& gID
If ExecuteSpt(sp_sql) Then
Else
Goto ErrorHandler
End If

You would use the following code to execute the same command via dynamic SQL: FakePre-0e96fd6ca91448788f01f377f05d2427-7f7c7d44896349c2a2dbec15c09b0b2bThis is handy for executing action stored procedures and dynamic SQL statements since they do not return records.

Returning Records

      A similar procedure can be employed to return records to use stored procedures and dynamic SQL statements in code as the source for recordsets. The following code allows a stored procedure to be used as the record source for a Data Access Object (DAO) recordset:

Dim rs_sp As Recordset              'Set in Global Module
Dim qdf As QueryDef                 'Set in Global Module
Set qdf  = db.CreateQueryDef("")    'Set in Global Module
Dim SP_SQL as string                'Set in Global Module
qdf.Connect = gstrconnect           'Set in Global Module
qdf_SP.ReturnsRecords = True        'Set in Global Module
qdf_SP.SQL = sqlstr                 'Set in Global Module
qdf_SP.ODBCTimeout = 15             'Set in Global Module
qdf.SQL = SP_SQL                    'Set in Global Module

SP_SQL = "Execute " & _
sp_AllNew_Customers " & gID
Set rs_sp = _
qdf.OpenRecordset
qdf.close
rs_sp.MoveFirst
rs_sp.Close

By declaring and setting most of the properties in a global module, you only need to write the final five lines each time a recordset is opened.
      The following code uses a similar technique for ActiveX® Data Objects (ADO) recordsets:

Set ado_Conn = New ADODB.Connection    'Set in Global Module

strServer = "my-sqlsvr"                'Set in Global Module
strDbase = "myDb"                      'Set in Global Module
ado_Conn.ConnectionTimeout = 15        'Set in Global Module
ado_Conn.Provider = "SQLOLEDB"         'Set in Global Module
ado_Conn.Properties("Data Source").value = strServer
'Set in Global Module
ado_Conn.Properties("Initial Catalog").value = strDbase
'Use this for trusted connections
ado_Conn.Properties("Integrated Security").value = "SSPI"
'Use this for SQL Security
ado_Conn.Properties("User ID").value = "myUID"

'Set in Global Module
ado_Conn.Properties("PassWord").value = "myPassWord"
ado_Conn.Open                             'Set in Global Module
Dim rs_sp As ADODB.Recordset              'Set in Global Module
Set rs_sp = New ADODB.Recordset           'Set in Global Module
SP_SQL = "Execute sp_AllNew_Customers " & gID
rs_sp.Source = SP_SQL
Set rs_sp.ActiveConnection = ado_Conn
rs_sp.Open
rs_sp.Close

Again, most properties can be set once in a global module.

Counter Variables

      Access 97 databases written with DAO or ADO often require a counter variable to be returned after executing an AddNew operation. This method does not work well (or efficiently) with SQL Server tables. I will therefore show you how to execute this same functionality without using ADO or attaching the SQL Server tables to your database.
      You need to write a stored procedure that returns the @@Identity value. The following code can be used throughout your Access function to insert new records into a SQL Server 7.0 table and return the counter value of the new record:

Function ReturnValue(sp_name As String)
Dim rs_sp As Recordset
Dim qdf As QueryDef
Dim ret As Long
Dim SP_SQL as String
Set qdf = gDB.CreateQueryDef("")
qdf.Connect = gstrconnect

qdf_SP.ReturnsRecords = True

qdf_SP.ODBCTimeout = 15

SP_SQL = "declare @retval int "
SP_SQL = SP_SQL & "declare @val int "
SP_SQL = SP_SQL & "execute @retval = " & sp_name &
" @val output "
SP_SQL = SP_SQL & "select @val as x"
qdf.SQL = SP_SQL
Set rs_sp = qdf.OpenRecordset
ret = rs_sp![X]
ReturnValue = ret
rs_sp.Close
End Function

This code can be used to execute Insert commands with a stored procedure when the new counter value needs to be returned.
      You can insert a new record by executing the stored procedure sp_Insert_StartDate with the variable @StartDate and return a new ID value that exists as an Identity field within the SQL Server 7.0 table:

Dim sp_sql as string
sp_sql = "sp_Insert_StartDate " & gStartDate
If ExecuteSpt(sp_sql) Then
Else
Goto ErrorHandler
End If

SQL Server Views

      Another option to using stored procedures and pass-through queries is SQL Server views. Views are created in the SQL Server database and can be used by Access to present data. Access treats a SQL Server view as a linked table when attached. Views can be used in code as a source for dynamic SQL statements, and you can create views in SQL Server from code in an Access application.
      This code is an example of the proper syntax for creating views from your application:

SQL= "If exists (select * from sysobjects where id = " & _
"object_id(N'[dbo].[vqryYE_Sched_first]') and
OBJECTPROPERTY(id," & _"N'IsView') = 1) "
SP_SQL = SQL & "drop view [dbo].[vqryYE_Sched_first] "
Call ExecuteSPT(SQL)

SP_SQL = "create view vqryYE_Sched_first as "
SP_SQL = SQL & "SELECT tblLease.LeaseName, tblLease.TenType,"
& _tblLease_YrEnds.*, tbl_cbo_GLOASeq.seq "
SP_SQL = SQL & "FROM tblLease INNER JOIN (tblLease_YrEnds LEFT
JOIN " & _"tbl_cbo_GLOASeq ON tblLease_YrEnds.Meas = " & _
"tbl_cbo_GLOASeq.type) ON tblLease.Counter = " & _
"tblLease_YrEnds.CLease "
SQL = SQL & "WHERE (((tblLease.TenType) <> 'other') And " & _
"((tblLease_YrEnds.Code) = '" & gcode & "') And " & _
"((tblLease_YrEnds.year) = '" & _
gYear & "') And ((tblLease.CProp) = " & gProjNum & ")) "
Call ExecuteSPT(SP_SQL)

      Hopefully these techniques will make it easier to transition an Access-only application to a joint Access and SQL Server application taking advantage of the power and scalability of SQL Server 7.0.

For related articles see:
https://msdn.microsoft.com/library/techart/sql7authstorproc.htm
Michael McManus is an MCSD with experience developing applications in Microsoft Access, Visual Basic, and SQL Server. Currently he's an in-house developer for the Mills Corporation, developing and supporting a property management system which serves as data repository for all tenant- and property-related data. You can reach him at mmcmanus@millscorp.com.

From the June 2000 issue of MSDN Magazine.