Populating an Access report with SQL Server Data
By Jim Lawrence view Bio
Demo file download sample files Print this Page
In 1997, I
was given a project to connect an Access97 Front database to a SQL 7 server
Backend (SQL Server7 backend might sound better) database. All the main data
would be stored on the new server and copies of the Access Front-end were to
reside there as well. My first approach was to investigate how to use ODBC.
Current
versions of Access can attach recordsets directly to reports. Versions like
Access97 could only attach queries and tables. Although this isn’t an issue
when working with the standard DAO databases, a little more effort is required
to access data on a SQL server. Data from a SQL server can only be acquired
through recordsets.
The Problem
Login
access was setupinstalled on the SQL’s Server; and ODBC access (Do you mean that a login
was created on the SQL Server?) a DNS (DSN ?) was setup on the
development workstation. The connection worked appropriately. A series
of ‘Pass-Through’ queries were then created to provide similar
functionality to the DAO connection processes being replaced. Designing an
application using this method responded more quickly than
the initial DAO topology but considering the performance rating of the new
server that was the least that could be expected.
Development
went smoothly enough but problems started when the application was moved to
another workstation. Each station required a custom setup and drivers and
environments proved inconsistent. It was decided that the client’s site was
liable to have a greater variety of configurations and variations and the
product rollout would prove to be time consuming and expensive.
The next
plan was to use a ADO-OLE (OLEDB ?) configuration directly from within the
application and there by sidestep the whole ODBC issue. The major difference
was that the forms were unbound and locking issues were currently handled
through the SQL Server side recordsets. The performance levels proved to be
exceptional and offset any programming concerns.
Functionality
of the DAO and new ADO method could be duplicated but for one particular
area—the reports. Pass-through queries were left in place. We experimented with
the programming recordsets inside the report, which but that proved to be complex and labour intensive especially when the
users requested changes, which they inevitably do.
One
particular report proved to be a major issue. When a full data set option was
selected the application ground to a near halt for thirty minutes. Running this
report using the DAO-MBD (MDB?) structure also proved to be a daunting task for
any workstation. Processing was done in
two parts and together it would take up to an hour to process. The result was a
bi-monthly report in excess of one hundred and sixty pages. A method for
increasing performance had to be found.
The Solution
The
following is how we solved the problem. The first step was to convert the query that currently populated the
report into a table, and we used the following code:
SELECT tblEmployees.EmployeeCode, [LastName] & ", "
& [FirstName] AS Name,
[Address] & " " & [Street] AS EmpAddress,
Format([WorkDate],"dd mmm yyyy") AS wDate,
IIf(Abs(DateDiff("n",[endtime],[starttime]))>1,
Abs(DateDiff("h",[endtime],[starttime]))+1,
Abs(DateDiff("h",[Endtime],[Starttime]))) AS Hours,
tblJobs.Description, Format([WorkDate],"m") AS mNumber,
Format([wDate],"mmmm") AS mName
FROM tblJobs INNER JOIN (tblEmployees
INNER JOIN tblJobPreformed
ON tblEmployees.EmployeeCode = tblJobPreformed.EmployeeCode)
ON tblJobs.JodCode = tblJobPreformed.JobCode
WHERE (((tblEmployees.EmployeeCode)=[EmployeeCodeNo]))
ORDER BY tblJobPreformed.WorkDate;
|
TThe Parameter, Where, and Order By clauses
can be removed as they are not part of the Table creation and we have concern
as to any data in the new table. The table’s name is then added to the end of
the field list. The resultant query would look something like this:
SELECT tblEmployees.EmployeeCode, [LastName]
& ", " & [FirstName] AS Name, [Address]
& " " & [Street] AS EmpAddress,
Format([WorkDate],"dd mmm yyyy") AS wDate,
IIf(Abs(DateDiff("n",[endtime],
[starttime]))>1,Abs(DateDiff("h",[endtime],
[starttime]))+1,Abs(DateDiff("h",[Endtime],
[Starttime]))) AS Hours, tblJobs.Description,
Format([WorkDate],"m") AS mNumber,
Format([wDate],"mmmm") AS mName
INTO tbl_report_template
FROM tblJobs
INNER JOIN (tblEmployees
INNER JOIN tblJobPreformed
ON tblEmployees.EmployeeCode = tblJobPreformed.EmployeeCode)
ON tblJobs.JodCode = tblJobPreformed.JobCode
WHERE (((tblEmployees.EmployeeCode)=[EmployeeCodeNo]))
ORDER BY tblJobPreformed.WorkDate;
|
The second step is to run the query and
create tbl_report_template. Remove any data from that table.
As a rule this template should be hidden. This can be accomplished by
selecting the new table, a right-mouse click, select the ‘Properties’ item,
from the pop-down menu and checking the Hidden option.
The third step is to adjust the related
report appropriately. Add the new table to the Report’s Record Source property as shown in Figure A. Note that doing this
is just creating a placeholder until later.
Figure A
The
following code is added to the report’s Open event. Substitute the name ‘MyReport’ with the actual
name of the current report. RunReports is the name of a function that
will be written later.
Private Sub Report_Open(Cancel As Integer)
Dim lngEmpNumber As Long
' Must use the Report name because the
' name of the report can not be aquired
' from object until the 'OPEN' event
' has been completed.
' Call the Module function to aquire the
' matching data.
DoCmd.Hourglass True
' Code below is to catch a direct call to the
' report...just a little fun.
If IsLoaded("frm_start") = True Then
lngEmpNumber = Forms![frm_Start]![cmbEmployeeNumber]
Else
lngEmpNumber = 10000 + Int((6 * Rnd) + 1)
End If
' Run function that will fill the report
RunReports [Reports]![Work Report], lngEmpNumber
DoCmd.Hourglass False
End Sub
|
It’s good
form to add something like the following code to the report’s NoData event to avoid the cryptic Microsoft
message if no matching data is found:
Private Sub Report_NoData(Cancel As Integer)
' Handle the possiblity of no data being returned
' from the database call.
MsgBox "There is no matching data for this" & vbCr & _
"report. Please select another criteria", _
vbInformation, "No Data Selected"
Cancel = True
End Sub
|
In the fourth
step we created a public function that exposes the report’s properties
and pass the appropriate set of instructions to the function TableBuild. Only the handling of one report is shown here. A virtually
unlimited number of reports can be handled through the Select Case statement if
the one sample method layout is copy and modified appropriately.
Please
note, these examples include SQL Stored Procedure references that are beyond
the scope of this article and aren’t explained further.
Public Function RunReports(Rpt1 As Report, lngValue As Long)
' The following function is the interface between the
' caller report and function that builds or fills the
' caller reports data source.
Dim strTableName As String
Select Case Rpt1.Name
Case "Work Report"
' Pass all the required parameters to the table builder/filler function
strTableName = TableBuild("tbl_report_template", "StoredProcedure", lngValue)
' If there is length to the temporary table substitute the current
' report's source.
If Len(strTableName) > 0 Then Rpt1.RecordSource = strTableName
Case "NextReport"
' more code
'…
End Select
End Function
|
The fifth step is to enter the main
processing public function. I traditionally pass a group of parameters to this
function through a collections or user-defined-types (UDT). For
simplicity, the references to these controls/objects were removed from the
following:
Public Function TableBuild(lsTableTemplateName As String, _
StoredProcedureName As String, _
lngValue As Long) As String
' This function performs the following tasks after receiving the
' following values:
' 1. The name of the local template table to
' be used for storing data
' 2. The name of the Stored Procedure or in
' this case query to be used for
' deliminating the data.
' 3. The variable(s) used to store any value
' required by the SP or query.
' Note: for more than one value, multiple
' variables can created in the
' function's argument list, a type,
' array could be passed or an open
' form could be connected to.
'
' The Function's tasks:
' 1. Open a connection to the remote DB.
' 2. Create and/or clear a local table that will receive the
' remote data.
' 3. Open a recordset to the local table (DAO).
' 4. Open a recordset for the remote data (ADO).
' 5. Fill the ADO recordset with the remote to the require
' criteria.
' 6. Transfer the data from the ADO recordset into the DAO
' recordset and therefore populate the local table.
' 7. Close the recordsets and exit, passing the newly
' populated table name back to the caller function.
Dim lsTemporaryTable As String
Dim ltabdefTables As TableDef
Dim lbTableExists As Boolean
Dim strSQL As String
Dim db1 As DAO.Database
Dim rs2 As DAO.Recordset
Dim rs1 As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim i As Integer
On Error GoTo Err_TableBuild
TableBuild = ""
lsTemporaryTable = ""
Set objConn = New ADODB.Connection
' Check to see that we still have a connection.
' If not let us force the system to bale...
If Len(gstrConnection) = 0 Then ConnectToDB
objConn.Open gstrConnection
If Len(lsTableTemplateName) > 0 Then
' In some of the Front end (FE) databases the source resides
' on the Server so more than one person could be accessing a
' particular temporary table at the same time. The variable
' gsHostName is a string generated from the station's address
' so there is never a conflict. I will send out the code if
' required. Note; that the names of the temporary tables are
' prefixed with Usys. This will make these tables invisible
' if the 'hidden object' option is not checked in Access's
' general configuration setting.
' lsTemporaryTable = "Usys" &UCase(gsHostName) &
' lsTableTemplateName
lsTemporaryTable = "Usys" & lsTableTemplateName
lbTableExists = False
Set db1 = CurrentDb()
' Catch an empty temporary file name
If Len(lsTemporaryTable) > 0 Then
' First, check to see whether required table exists
With db1
For Each ltabdefTables In .TableDefs
If lsTemporaryTable = ltabdefTables.Name Then _
lbTableExists = True
Next
End With
' If you are using 2000 or 2002 then the above 5 lines
' could read:
' db1.TableDefs(lsTemporaryTable).Name <> "" Then _
' lbTableExists = True
' Second, either clear all data out of or create the
' appropriate table
If lbTableExists = True Then
strSQL = "DELETE " & lsTemporaryTable & _
".* FROM " & lsTemporaryTable
Else
strSQL = "SELECT " & lsTableTemplateName & _
".* INTO " & lsTemporaryTable & _
" FROM " & lsTableTemplateName
End If
db1.Execute strSQL
' Refresh the current MDB's table list.
If lbTableExists = False Then db1.TableDefs.Refresh
'If there is a accompanying Stored Procedure
If Len(StoredProcedureName) > 0 Then
' Third, create a local table defined recordset
Set rs2 = db1.OpenRecordset(lsTemporaryTable, dbOpenDynaset)
' Fourth, populate the ADO recordset with SQL stored procedure
Set objCmd = New ADODB.Command
With objCmd
' Set up the ADO command object to process and receive
' data. The variable gstrConnection holds the connection
' string and is placed in the ActiveConnection 'property.
' CommandType property is the type of string; text, SP etc..
' the connection string
.ActiveConnection = gstrConnection
' the name of the query/Stored Procedure
.CommandText = StoredProcedureName
' type of command
.CommandType = adCmdStoredProc
' This process would be normally handled through a
' table/function call or a passed array or type
' variable but for this demonstration I have tried
' to cut back on the embellishments.
Select Case StoredProcedureName
Case "StoredProcedure"
' Parameter(s) to send to the remote query/Stored Procedure
' Append to the parameter list:
' 1. The name of the receiving variable
' 2. The type of variable; integer, string etc.:
' .Parameters.Append .CreateParameter("UserID", _
' adChar, _
' adParamInput, _
' 15, _
' strUserCode)
' ...If you were passing a string.
' 3. The variable direction; in, out or in and out.
' 4. Required if a string was being passed, the length
' is required.
' 5. The variable holding the informtion to pass to the
' the SP or query.
' Note: If you needed to pass multiple variable you would
' just repeat the parameter code below, one for each
' variable. Another article or two could be written
' on how to handle Procdure call to SP or queries.
.Parameters.Append .CreateParameter("EmployeeCodeNo", _
adInteger, _
adParamInput, , _
lngValue)
Case "NameofMyStoredProcedure02"
' more code ...
End Select
End With
Set rs1 = New ADODB.Recordset
' Must be a client side cursor/recordset
rs1.CursorLocation = adUseClient
' Note: recordset created on the client side default to
' Static and Read-only. I always add the following
' options to the standardize and clarify for future
' programmers.
rs1.Open objCmd, , adOpenStatic, adLockReadOnly
' Fifth, transfer data from ADO recordset to the empty
' recorsdset and temporary table.
With rs1
If .BOF = False Or .EOF = False Then
.MoveLast
.MoveFirst
Do While .EOF = False
rs2.AddNew
For i = 0 To .Fields.Count - 1
rs2.Fields(i).Value = .Fields(i).Value
Next
rs2.Update
.MoveNext
Loop
Else
lsTemporaryTable = "" 'No data
End If
End With
End If
End If
End If
Exit_TableBuild:
' Sixth and last Cleanup but closing all the extra objects.
' In the unlikely event that something is mishandled force
' a complete clean up
On Error Resume Next
rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db1.Close: Set db1 = Nothing
objConn.Close: Set objConn = Nothing
TableBuild = lsTemporaryTable
Exit Function
Err_TableBuild:
MsgBox "Error: TableBuild " & Err.Number & " : " & Err.Description
lsTemporaryTable = "" 'No data
Resume Exit_TableBuild
End Function
|
In Summary; That is about it. The code is in two MS Access
2000 MDBs. One is called ‘PseudoSQL’ and this is suppose to emulate a
SQL server. It is comprised of three tables and one query with a parameter. The
program and running MDB is called ‘Reporting’ and this houses, one
template table, one Form, where everything is operated from, one report to
demonstrate the process and one module for all the code. After the ZIP file has
been downloaded and expanded, all the resultant files must be placed in a
single directory and the ‘Reporting’ MDB should be opened and the form ‘frm_Start’
should then be opened to run everything. The rest should be self-explanatory.
If you have any questions or comments you can address to me,
at accessd@shaw.ca or post any queries to the LIST.
Jim Lawrence ©2002-2003
May be distributed as long as the copyright remains.
