I am porting an Access Front End/Access Back End application into an Access Front End/SQL Server Back End application.
One thing that got me stumped for a while was a report which had an embedded query as a subreport. This query was complicated in that it had many joins, group by clauses on inner selects that were parametrised and quite complicated functions to work out what to display. It took a long time to run.
Ran a check on the SQL being sent to the server and soon realised that in order to conduct the joins the enquiry was returning all the records in one of our major tables. This was to produce an output of just a few lines long. There was only one thing for it, this query would have to be executed on the server.
Because the query was parametrised and these parameters were used to limit records in a GROUP BY inner select, a simple View would not work. I would have to implement it via a Stored Procedure. After a few false starts failing to convert Access SQL to SQL Server SQL I soon had a Stored Procedure that produced the correct results and ran a lot faster than the query before.
The question now became – how do I embed the results from this query. This, it turns out is harder than I thought. I had to dynamically create the query because I needed to set the parameters for the Stored Procedure before it was executed. But when I tried to attach to it to the recordsource of the sub-report I got an error message saying that this was only supported for ADP’s, and that is NOT what I have here.
But there is a way round it – here is what I did
- Create a form which is only displayable in datasheet mode.
- Create controls on the form to display each of the fields returned by the stored procedure
- Create an “onLoad” event procedure to connect the results of the stored procedure to the forms recordset property
- Embed the form within the report in place of the previously embedded query.
It works great.
For those following this, the only slightly complex piece is the code to run in the forms onLoad event. Here it is (frmStatsByDate is a form holding to date parameters, getStrConn is a function that returns my current connection string to the backend database)
Private Sub Form_Load()
Dim sd As String, ed As String
Dim conn As ADODB.Connection
If CurrentProject.AllForms("frmStatsByDate").IsLoaded Then
sd = Replace([Forms]![frmStatsByDate]![start_date],"’","")
ed = Replace([Forms]![frmStatsByDate]![end_date],"’","")
sd = "1/1/2011"
ed = "31/3/2011"
Set conn = New ADODB.Connection
conn.Open = getStrConn
Set Me.Recordset = conn.Execute("EXEC dbo.qryCostPerWidget ‘" & sd & "’,’" & ed & "’;")
Set conn = nothing