This article describes a method to generate scripts to perform mass-updates of data. The purpose is to document the updates that are done, for future reference.
Let's say you have a table of customers, and each is assigned to a region based on the state being east or west of the Mississippi River. Therefore, Illinois is assigned to the East. However, in order to balance the workload, the users have asked us to move all Illinois cities to the West region. The table looks like this:
ID int identity
Customer_Name nvarchar(50)
ST char(2)
Region nvarchar(50)
You could write a simple update query like this:
UPDATE Customer
SET Region = 'West'
WHERE ST = 'IL'
However there will be no record of exactly what was updated; if new Illinois cities are added they might be incorrectly placed in the old region, and we might wonder if the update failed for that city. Or, the users might ask which cities had been moved. The solution is to build a SELECT statement that generates one update statement for each record, like this:
SELECT
'UPDATE Customer
SET Region = "West"
WHERE ID = ' +
CAST(ID AS varchar) +
' AND ST = "IL"
AND Region = "East" '
FROM
Customer
WHERE
ST = 'IL'
AND Region = 'East'
..when you run this query, you will get one "record" per city to be updated; each line being similar to this:
UPDATE Customer SET Region = "West" WHERE ID = 2266403 AND ST = "IL" AND Region = "East"
Now, you would copy the output from the results window into a new query window, replace the double-quotes " with single-quotes ' and you can execute the update & save the code to have a permanent record of precisely what was changed.
This technique can be applied to more complex scenarios. For example, you might export a spreadsheet so users can easily back fill missing data, and then import that back into the database. In that situation, you would not be able to reconstruct the changes made unless you keep a copy of the imported data in an extra table somewhere. By scripting the updates you can document precisely what changed.
One final note - this technique will work reliably only if you execute the "SELECT" query to the results window; if you select Results to Text there is a limit to how many characters are displayed, so longer statements may be truncated.
Saturday, February 06, 2010
Monday, January 25, 2010
Office 2010, part 1
I have been working with the Office 2010 Beta for about a week now. I downloaded the Professional Plus version on January 17 and installed on my test PC which is an old Pentium III with 512 RAM running Windows Server 2003. The install was quite similar to the Office 2007 install.
The program launches faster than 2007. Gone is the Office Button, in favor of a File tab on the ribbon which brings up the Backstage area which has basically the same content as the button. For the beta there are two smiley icons in the system tray, Send a Smile and Send a Frown (I am not kidding) for feedback to Microsoft.
My first real test today was with Access 2010, using a time & billing program that I use for consulting work. The front-end is Access 2000 format, and the back-end is Access 97. I run the app using Access 2003.
When I first opened the file, even while holding the Shift key, I got an error message
The command or action '' isn't available now.
I went back to 2003 and decompiled the app & then compiled & saved, since this is oftentimes a quick fix for unexplained errors; that did not help. The app has an AutoExec macro that opens a form and then maximizes the window; once the file is open, I can run the macro, or open the form, without any error message. Obviously Access 2010 is doing something I don't know about. I visited the Trust Center & enabled the macros, and also set the ActiveX to be wide open but the error message still shows upon opening the file. This test PC does not have any anti-virus software installed, so that is not a factor either.
The app has a popup form with a Calendar 2.0 control, but alas 2010 does not support it. Whilst browsing the dev info for hints, I noted that data access pages (DAP's) are on the way out - never used them myself - 2010 does not support creation but apparently still runs them.
There are a couple of other things that don't seem right. When you have the Navigation Pane open and the ribbon is at the Home tab, you can select a form or report & the Design button shows, but it does not show for the other objects tables queries etc. But you can right-click on the object to get into design view.
Also, if the ribbon is minimized & you preview a maximized report, there is no close button or "x" so you need to either right-click and Close, or press Ctrl-F4 which still works. You can add the "Close Preview" button to the Quick Access Toolbar, but it takes 2 clicks to close the report - 1st click shows a red Close Preview button, you have to click that to close the report.
Otherwise the app seems to work OK. Next I will try some design changes & see how that all shakes out. . .
The program launches faster than 2007. Gone is the Office Button, in favor of a File tab on the ribbon which brings up the Backstage area which has basically the same content as the button. For the beta there are two smiley icons in the system tray, Send a Smile and Send a Frown (I am not kidding) for feedback to Microsoft.
My first real test today was with Access 2010, using a time & billing program that I use for consulting work. The front-end is Access 2000 format, and the back-end is Access 97. I run the app using Access 2003.
When I first opened the file, even while holding the Shift key, I got an error message
The command or action '' isn't available now.
I went back to 2003 and decompiled the app & then compiled & saved, since this is oftentimes a quick fix for unexplained errors; that did not help. The app has an AutoExec macro that opens a form and then maximizes the window; once the file is open, I can run the macro, or open the form, without any error message. Obviously Access 2010 is doing something I don't know about. I visited the Trust Center & enabled the macros, and also set the ActiveX to be wide open but the error message still shows upon opening the file. This test PC does not have any anti-virus software installed, so that is not a factor either.
The app has a popup form with a Calendar 2.0 control, but alas 2010 does not support it. Whilst browsing the dev info for hints, I noted that data access pages (DAP's) are on the way out - never used them myself - 2010 does not support creation but apparently still runs them.
There are a couple of other things that don't seem right. When you have the Navigation Pane open and the ribbon is at the Home tab, you can select a form or report & the Design button shows, but it does not show for the other objects tables queries etc. But you can right-click on the object to get into design view.
Also, if the ribbon is minimized & you preview a maximized report, there is no close button or "x" so you need to either right-click and Close, or press Ctrl-F4 which still works. You can add the "Close Preview" button to the Quick Access Toolbar, but it takes 2 clicks to close the report - 1st click shows a red Close Preview button, you have to click that to close the report.
Otherwise the app seems to work OK. Next I will try some design changes & see how that all shakes out. . .
Monday, November 09, 2009
SQL Data Scripts
A standard practice in SQL Server development is to make any changes to objects or data using a script and apply it to the DEV system; once verified, it is applied to QA; once verified, it is finally applied to production.
You can take this one step further, and write your scripts to be deterministic - i.e. if the script is run multiple times, the outcome will be the same.
For example, let's say you have a lookup table tblSettings like this:
ID int IDENTITY (1,1),
ItemText nvarchar(50)
the table holds two records with ID's of 1 and 2, and you wish to add a third. You could simply write
INSERT tblSettings ( ItemText ) SELECT 'application author'
but if you execute that script more than one time, you get multiple records with that same text.
Now, this is a back-end table, and you have verified that the next ID will be 3, using this:
SELECT IDENT_CURRENT('tblSettings') + IDENT_INCR('tblSettings')
The solution is to test if that record exists before you insert the record, like this:
IF (SELECT 1 FROM tblSettings WHERE ID = 3 ) IS NULL
BEGIN
INSERT tblSettings ( ItemText ) SELECT 'application author'
END
in this way, the script can be run multiple times in DEV, QA or even production, and the outcome will always be that the table holds exactly those three records.
Now, let's say that you need to change the record just added to say 'application developer'. You could simply write
UPDATE tblSettings
SET ItemText = 'application developer'
WHERE ID = 3
but why "update" a record to the same value? The solution here is simply to include the old value in the criteria, like this:
UPDATE tblSettings
SET ItemText = 'application developer'
WHERE ID = 3
AND ItemText = 'application author'
The benefit of these methods is to touch the data only where necessary, and to ensure that the outcome is precisely what is expected.
You can take this one step further, and write your scripts to be deterministic - i.e. if the script is run multiple times, the outcome will be the same.
For example, let's say you have a lookup table tblSettings like this:
ID int IDENTITY (1,1),
ItemText nvarchar(50)
the table holds two records with ID's of 1 and 2, and you wish to add a third. You could simply write
INSERT tblSettings ( ItemText ) SELECT 'application author'
but if you execute that script more than one time, you get multiple records with that same text.
Now, this is a back-end table, and you have verified that the next ID will be 3, using this:
SELECT IDENT_CURRENT('tblSettings') + IDENT_INCR('tblSettings')
The solution is to test if that record exists before you insert the record, like this:
IF (SELECT 1 FROM tblSettings WHERE ID = 3 ) IS NULL
BEGIN
INSERT tblSettings ( ItemText ) SELECT 'application author'
END
in this way, the script can be run multiple times in DEV, QA or even production, and the outcome will always be that the table holds exactly those three records.
Now, let's say that you need to change the record just added to say 'application developer'. You could simply write
UPDATE tblSettings
SET ItemText = 'application developer'
WHERE ID = 3
but why "update" a record to the same value? The solution here is simply to include the old value in the criteria, like this:
UPDATE tblSettings
SET ItemText = 'application developer'
WHERE ID = 3
AND ItemText = 'application author'
The benefit of these methods is to touch the data only where necessary, and to ensure that the outcome is precisely what is expected.
Wednesday, November 04, 2009
Reusable code
My definition of reusable code: modular code that is flexible enough that it can be used in more than one way.
For example, let's say that you have a table Orders like this:
ID int identity,
OrderDate datetime,
Quantity int
and you want the total orders for one year. Fine. All you need is something like this:
CREATE PROC Test
(
@Year int
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) = @Year
which answers the immediate need.
But if you write it like this, you can execute the sp without a parameter & get the current year:
CREATE PROC Test
(
@Year int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
Another approach is to add a 2nd parameter for the month, like this:
CREATE PROC Test
(
@Year int = 0,
@Month int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
AND
(
MONTH(OrderDate) = @Month
OR
@Month = 0
)
which allows you specify a single month, or use a zero to get the whole year.
Granted, these are very basic examples, but if you build in little hooks like these at the outset, you can save time later on.
For example, let's say that you have a table Orders like this:
ID int identity,
OrderDate datetime,
Quantity int
and you want the total orders for one year. Fine. All you need is something like this:
CREATE PROC Test
(
@Year int
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) = @Year
which answers the immediate need.
But if you write it like this, you can execute the sp without a parameter & get the current year:
CREATE PROC Test
(
@Year int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
Another approach is to add a 2nd parameter for the month, like this:
CREATE PROC Test
(
@Year int = 0,
@Month int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
AND
(
MONTH(OrderDate) = @Month
OR
@Month = 0
)
which allows you specify a single month, or use a zero to get the whole year.
Granted, these are very basic examples, but if you build in little hooks like these at the outset, you can save time later on.
Saturday, October 24, 2009
Tally tables
I first learned about tally tables from the forums at SqlServerCentral which is great resource for information about SQL Server. (Free registration is required to view the content.)
A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:
The benefit of a tally table is that it can be used to implement set-based solutions, thus avoiding cursors or WHILE loops.
Example 1: Let's say that you need to generate a list of all dates from 05/12/2008 to 06/02/2008 inclusive. You might use a temp table or table variable, and construct a loop that inserts records for each date in the range. By using a tally table, all you need is this (using variables to make it flexible):
Example 2: You have an Orders table like this:
ID int IDENTITY (1,1,) NOT NULL,
ProductID int,
OrderDate datetime,
Quantity int
and you need the total orders for one product, for one year, by month. The basic query might look like this:
However, this will not return a row for any month that did not have
an order. You can use a tally table to ensure that you have
totals for all 12 months, even for months without an order:
There is one more example that I'd like to share, where you can
insert multiple rows from a single edit record, but it is too long
to post in the blog format, so I will assemble this post into
a Word document and add the URL when complete.
EDIT: The full article with examples is here.
A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:
SELECT TOP 11000
IDENTITY(int,1,1) AS N
INTO
dbo.Tally
FROM
Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
The benefit of a tally table is that it can be used to implement set-based solutions, thus avoiding cursors or WHILE loops.
Example 1: Let's say that you need to generate a list of all dates from 05/12/2008 to 06/02/2008 inclusive. You might use a temp table or table variable, and construct a loop that inserts records for each date in the range. By using a tally table, all you need is this (using variables to make it flexible):
DECLARE
@start datetime,
@end datetime
SELECT
@start = '05/12/2008',
@end = '06/02/2008'
SELECT
DATEADD(d, (N - 1), @start)
FROM
Tally
WHERE
N BETWEEN 1 AND DATEDIFF(d, @start, @end) + 1
Example 2: You have an Orders table like this:
ID int IDENTITY (1,1,) NOT NULL,
ProductID int,
OrderDate datetime,
Quantity int
and you need the total orders for one product, for one year, by month. The basic query might look like this:
SELECT
MONTH(o.OrderDate) AS OrderMonth,
SUM(o.Quantity) AS Total
FROM
Orders o
WHERE
YEAR(o.OrderDate) = 2007
AND ProductID = 49
GROUP BY
MONTH(o.OrderDate)
ORDER BY
MONTH(o.OrderDate)
However, this will not return a row for any month that did not have
an order. You can use a tally table to ensure that you have
totals for all 12 months, even for months without an order:
SELECT
T.N AS OrderMonth,
SUM
(CASE
WHEN MONTH(o.OrderDate) = T.N
THEN o.Quantity
ELSE 0 END
) AS Total
FROM
Orders o
CROSS JOIN Tally T
WHERE
YEAR(o.OrderDate) = 2007
AND T.N BETWEEN 1 AND 12
AND ProductID = 49
GROUP BY
T.N
ORDER BY
T.N
There is one more example that I'd like to share, where you can
insert multiple rows from a single edit record, but it is too long
to post in the blog format, so I will assemble this post into
a Word document and add the URL when complete.
EDIT: The full article with examples is here.
Thursday, October 15, 2009
SQL Lookups
I am working with an Access app that was converted to use a SQL Server database, and gradually replacing Access queries with T-SQL or stored procedures for better performance.
If you have used Access for a while, you know that the so-called Domain Aggregate functions such as DCount, DLookup, and DSum are very expensive in terms of resource usage. Even if you write your own VBA function to perform the lookup, the fact is that the Jet database engine is not efficient when looking up a single value.
My challenge was that my form needed to pull data from 3 different queries. However, after binding a query to the form to get the 1st value, using DCount for the 2nd value & then DLookup for the 3rd, the form would open quite slowly. In fact, you could observe the various controls on the form being painted as the 3 values were pulled in.
The solution was to create a stored procedure to look up the values, create a pass-thru query to execute the sp, and then open a DAO recordset in the Form_Open event & grab the values from the pass-thru query.
Each of the Access queries / lookups were re-written using T-SQL. Then we declare 3 variables, use the SQL queries to get the values, and write a SELECT statement to return the values; something like this:
DECLARE
@value_1 int,
@value_2 int,
@value_3 int
SELECT @value_1 = (first query converted to T-SQL)
SELECT @value_2 = (second query converted to T-SQL)
SELECT @value_3 = (third query converted to T-SQL)
SELECT
@value_1 AS value_1,
@value_2 AS value_2,
@value_3 AS value_3
If you have used Access for a while, you know that the so-called Domain Aggregate functions such as DCount, DLookup, and DSum are very expensive in terms of resource usage. Even if you write your own VBA function to perform the lookup, the fact is that the Jet database engine is not efficient when looking up a single value.
My challenge was that my form needed to pull data from 3 different queries. However, after binding a query to the form to get the 1st value, using DCount for the 2nd value & then DLookup for the 3rd, the form would open quite slowly. In fact, you could observe the various controls on the form being painted as the 3 values were pulled in.
The solution was to create a stored procedure to look up the values, create a pass-thru query to execute the sp, and then open a DAO recordset in the Form_Open event & grab the values from the pass-thru query.
Each of the Access queries / lookups were re-written using T-SQL. Then we declare 3 variables, use the SQL queries to get the values, and write a SELECT statement to return the values; something like this:
DECLARE
@value_1 int,
@value_2 int,
@value_3 int
SELECT @value_1 = (first query converted to T-SQL)
SELECT @value_2 = (second query converted to T-SQL)
SELECT @value_3 = (third query converted to T-SQL)
SELECT
@value_1 AS value_1,
@value_2 AS value_2,
@value_3 AS value_3
Friday, September 25, 2009
DATEPART and DATEFIRST
I am working on a query that groups data by the week-ending date.
In MS-Access, you can use DatePart to get the day of the week ( 1 to 7 ) and then add 6 thru 0 days to your test date to calculate the week-ending date. For a normal Sunday - Saturday workweek, if your date field is TheDate you would use this:
DateAdd("d", 7 - DatePart("w", TheDate), TheDate)
...and if your workweek if different, e.g. Saturday - Friday you can use the optional argument to specify the 1st day of the week:
DateAdd("d", 7 - DatePart("w", TheDate, vbSaturday), TheDate)
Now, if you are working in SQL Server, the basic Sunday - Saturday expression is similar:
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
However, in T-SQL there is no optional parameter for the 1st day of the week - you would need to do this:
SET DATEFIRST 6
SELECT
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
FROM TheTable
...but the DATEFIRST is a global setting, so it might affect other code that depends on the default setting.
One possible solution is this:
IF @@DATEFIRST != 7
SET DATEFIRST 7
SELECT
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
FROM TheTable
...which forces the 1st day to the default, and then uses 6 instead of 7 to calc the offset days. This will work in a stored procedure, but not in a view.
OTOH if you are certain that the DATEFIRST never varies from 7, you could simply use
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
which does give the correct result for the Saturday - Friday workweek & can be used in sp's and views.
In MS-Access, you can use DatePart to get the day of the week ( 1 to 7 ) and then add 6 thru 0 days to your test date to calculate the week-ending date. For a normal Sunday - Saturday workweek, if your date field is TheDate you would use this:
DateAdd("d", 7 - DatePart("w", TheDate), TheDate)
...and if your workweek if different, e.g. Saturday - Friday you can use the optional argument to specify the 1st day of the week:
DateAdd("d", 7 - DatePart("w", TheDate, vbSaturday), TheDate)
Now, if you are working in SQL Server, the basic Sunday - Saturday expression is similar:
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
However, in T-SQL there is no optional parameter for the 1st day of the week - you would need to do this:
SET DATEFIRST 6
SELECT
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
FROM TheTable
...but the DATEFIRST is a global setting, so it might affect other code that depends on the default setting.
One possible solution is this:
IF @@DATEFIRST != 7
SET DATEFIRST 7
SELECT
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
FROM TheTable
...which forces the 1st day to the default, and then uses 6 instead of 7 to calc the offset days. This will work in a stored procedure, but not in a view.
OTOH if you are certain that the DATEFIRST never varies from 7, you could simply use
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
which does give the correct result for the Saturday - Friday workweek & can be used in sp's and views.
Thursday, August 27, 2009
SSMS - INSERT script
SSMS allows you to quickly generate table scripts, such as INSERT, SELECT, and UPDATE, which can save lots of time when writing T-SQL.
However, I noticed that if you have an IDENTITY column the INSERT script will leave that out.
Granted, most of the time you won't be inserting a value into that column, and in fact you need SET IDENTITY_INSERT MyTable ON if you do wish to insert into that column. But I would prefer to see that column in the script, so I can remove it myself according to my needs.
However, I noticed that if you have an IDENTITY column the INSERT script will leave that out.
Granted, most of the time you won't be inserting a value into that column, and in fact you need SET IDENTITY_INSERT MyTable ON if you do wish to insert into that column. But I would prefer to see that column in the script, so I can remove it myself according to my needs.
Sunday, August 09, 2009
SQL script encoding
During development work with SQL Server, I will generally write a series of change scripts that need to be applied in a specific order. For example, one script might add a new table and another creates a stored procedure that uses that new table. Therefore, when I name the scripts I will prefix them with a sequential number e.g. 01_tblCustomer.sql, 02_prcGetCustomer.sql and so forth. This has several advantages: each script can be written & tested separately; the scripts can be applied in the correct order; the dependencies are recorded correctly in SQL Server.
Originally I would pass the group of scripts to the DBA to be applied to the production database, but as the number of scripts grew the chance for error grew as well.
I wrote a small VBA program to loop thru the script folder & build up a change script, so the DBA only has to apply a single large script. This worked quite well when each individual script was started from a blank query window, but when I generated a Modify script using SSMS there were extra characters at the beginning of the SSMS-generated script; specifically there was an ASCII 255, 254 at the beginning of the first line.
It turns out that when you start a script from a new query window & save it, the default encoding is
Western European (Windows) - Codepage 1252
which corresponds to ANSI or plain text. However, when you use SSMS to generate a script, the default encoding is
Unicode - Codepage 1200
which is the reason that VBA was reading the extra characters from the top of the script file.
So then, when saving the SSMS-generated scripts, all I had to do was to use the Save button option Save with Encoding... and select the 1252 encoding.
Originally I would pass the group of scripts to the DBA to be applied to the production database, but as the number of scripts grew the chance for error grew as well.
I wrote a small VBA program to loop thru the script folder & build up a change script, so the DBA only has to apply a single large script. This worked quite well when each individual script was started from a blank query window, but when I generated a Modify script using SSMS there were extra characters at the beginning of the SSMS-generated script; specifically there was an ASCII 255, 254 at the beginning of the first line.
It turns out that when you start a script from a new query window & save it, the default encoding is
Western European (Windows) - Codepage 1252
which corresponds to ANSI or plain text. However, when you use SSMS to generate a script, the default encoding is
Unicode - Codepage 1200
which is the reason that VBA was reading the extra characters from the top of the script file.
So then, when saving the SSMS-generated scripts, all I had to do was to use the Save button option Save with Encoding... and select the 1252 encoding.
Sunday, July 19, 2009
Pass-Thru Solutions
Six months ago we converted an Access 2003 application to use MSSQL 2005 as the database, and are gradually converting reports to use pass-thru queries, with either T-SQL or stored procedures, to take advantage of the speed & power of the SQL Server.
There are many complex reports, some of which have embedded summary subreports, and therein lies the snag - Access does not allow you to use a stored procedure for subreports.
The main reports that have user criteria are easy - create a stored procedure that accepts parameter(s) and the pass-thru query is re-written in the button click event. So the VBA code for that might look like this:
CurrentDB.QueryDefs("MyQuery").SQL =
"EXEC dbo.prcMyQuery '" & Forms!MyForm!MyDate & "' "
and the query executes this:
EXEC dbo.prcMyProcedure '07/19/2009'
thus returning the desired records.
But what to do about the subreport? That query is often far more complex than the main query, since it provides a summary with aggregates. . .
The solution is to create a View, link to the view (which Access treats as an attached table) and then create a simple Access query to apply the filter criteria. So if the view is attached as
viwMyView
then the Access query might look like this:
SELECT viwMyView.*
WHERE viwMyView.DateField = Forms!MyForm!MyDate
which then applies the same filter to the subreport, that is applied to the main report. We still get the power of SQL Server to build up the summary data, and the number of rows is small enough that the Access query overlay does not impact the performance.
There are many complex reports, some of which have embedded summary subreports, and therein lies the snag - Access does not allow you to use a stored procedure for subreports.
The main reports that have user criteria are easy - create a stored procedure that accepts parameter(s) and the pass-thru query is re-written in the button click event. So the VBA code for that might look like this:
CurrentDB.QueryDefs("MyQuery").SQL =
"EXEC dbo.prcMyQuery '" & Forms!MyForm!MyDate & "' "
and the query executes this:
EXEC dbo.prcMyProcedure '07/19/2009'
thus returning the desired records.
But what to do about the subreport? That query is often far more complex than the main query, since it provides a summary with aggregates. . .
The solution is to create a View, link to the view (which Access treats as an attached table) and then create a simple Access query to apply the filter criteria. So if the view is attached as
viwMyView
then the Access query might look like this:
SELECT viwMyView.*
WHERE viwMyView.DateField = Forms!MyForm!MyDate
which then applies the same filter to the subreport, that is applied to the main report. We still get the power of SQL Server to build up the summary data, and the number of rows is small enough that the Access query overlay does not impact the performance.
Subscribe to:
Posts (Atom)