MSDN Magazine > Issues and Downloads > 2000 > June >  Upsize Your Database: Convert Your Microsoft Ac...
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.
MIND

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. converting 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:
Dim sp_sql as string
sp_sql = "UPDATE tblStartDates set [StartDate] = '" & 
    gStartDate & _"' WHERE [ID] = " & gID
If ExecuteSpt(sp_sql) Then
Else
Goto ErrorHandler
End If
This 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:
http://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.

Page view tracker