January 29, 2014 at 2:06 pm
I'm using Access as a front-end application and SQLServer on the back-end. There is one particular report that locks up Access when I try to run it and so I would like to change the queries to pass-through queries. One of my queries needs to use parameters selected by a user on a form. Does anyone know how to pull the Access parameter into SQLServer?
I have written the following code in a pass-through query. But instead of the hard-coded dates, I need the it to look for the control on the Access form.
SELECT
f.IA_FIRM_NAME
, Count(CLAIM_NUMBER) AS Reviewed
FROM dbo.IA_ESTIMATE AS e
,dbo.IA_FIRM AS f
where e.IA_FIRM_ID = f.IA_FIRM_ID
and e.DATE_APPROVED_BY_IA BETWEEN '2013-01-01' AND '2013-12-01'
GROUP BY f.IA_FIRM_NAME
If I were to access the form control in Access, the 'Where' clause would look like this:
WHERE dbo_IA_ESTIMATE.DATE_APPROVED_BY_IA Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartDate] And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndDate]
I've spent several days now researching this online, but have not found a workable solution. Any help is much appreciated.
January 29, 2014 at 2:22 pm
dany.donaldson (1/29/2014)
I'm using Access as a front-end application and SQLServer on the back-end. There is one particular report that locks up Access when I try to run it and so I would like to change the queries to pass-through queries. One of my queries needs to use parameters selected by a user on a form. Does anyone know how to pull the Access parameter into SQLServer?I have written the following code in a pass-through query. But instead of the hard-coded dates, I need the it to look for the control on the Access form.
SELECTf.IA_FIRM_NAME
, Count(CLAIM_NUMBER) AS Reviewed
FROM dbo.IA_ESTIMATE AS e
,dbo.IA_FIRM AS f
where e.IA_FIRM_ID = f.IA_FIRM_ID
and e.DATE_APPROVED_BY_IA BETWEEN '2013-01-01' AND '2013-12-01'
GROUP BY f.IA_FIRM_NAME
If I were to access the form control in Access, the 'Where' clause would look like this:
WHERE dbo_IA_ESTIMATE.DATE_APPROVED_BY_IA Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartDate] And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndDate]I've spent several days now researching this online, but have not found a workable solution. Any help is much appreciated.
You haven't found a solution because what you are describing is impossible. SQL server cannot see your Access form. It does not know or care about your form. You either need to build a pass through query in Access and then execute that string or create a stored procedure with a parameter in sql server and execute that stored proc from Access. I would highly recommend the stored proc approach.
Your procedure might look something like this.
create procedure ThisIsMyProcedure
(
@StartDate datetime,
@EndDate datetime
) as
SELECT
f.IA_FIRM_NAME
, Count(CLAIM_NUMBER) AS Reviewed
FROM dbo.IA_ESTIMATE AS e
,dbo.IA_FIRM AS f
where e.IA_FIRM_ID = f.IA_FIRM_ID
and e.DATE_APPROVED_BY_IA BETWEEN @StartDate AND @EndDate
GROUP BY f.IA_FIRM_NAME
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2014 at 2:43 pm
Thank you Sean, i've been reading similar ideas but none have that clear. I ran the code and it failed - "CREATE PROCEDURE permission denied in database" Does that mean I don't have the permissions needed?
January 29, 2014 at 2:49 pm
dany.donaldson (1/29/2014)
Thank you Sean, i've been reading similar ideas but none have that clear. I ran the code and it failed - "CREATE PROCEDURE permission denied in database" Does that mean I don't have the permissions needed?
That is correct. It sounds that you do not have authority to create a procedure in that database. You may have to push this to your DBA.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2014 at 2:53 pm
Ok thanks. My next question will be once the procedure is created, how does the query know to get the StartDate and EndDate from the active form?
January 29, 2014 at 3:18 pm
dany.donaldson (1/29/2014)
Ok thanks. My next question will be once the procedure is created, how does the query know to get the StartDate and EndDate from the active form?
Again the query doesn't know about your application. You will have to add the parameters with values in Access. Then you need to execute that proc from Access.
Think of it like methods when programming. Your stored procedure is like a method in a class that resides in a compiled dll. Your Access is an application that wants to use that method. In access you can call that method and pass it parameters right? This is the same thing but instead of a dll it is through a database connection.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2014 at 3:29 pm
So sorry. I don't understand.
I'm not a programmer and therefore don't know what you mean by methods or DII. I know how to create MS Access database, but through the user interface and very little through code.
Creating a pass-through query is completely new to me.
I do understand that SQL can't pull any data from Access but that Access needs to send it to SQL (right?).
I'll try to get with one of our DBA's tomorrow and work on creating the procedure. Afterwards I'll be back for clarification, if you wouldn't mind explaining in a different way. Thank you.
January 29, 2014 at 4:23 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply