May 10, 2005 at 10:20 am
Hi
I need to select a dataset - currently a linked view - in a SQL Server 2000 database, according to chosen values in an Access 2000 form.
The full dataset produced by the view is too large to be easily managed by the Access 2000 link. I need to thin it down by selecting YEAR and COSTCODE etc. I could pass these parameters to a stored procedure and create the view at this point, but am uncertain whether this would be a good idea in a multi-user environment.
Has anybody any ideas how I might best do this?
Thanks
Paul
May 11, 2005 at 2:34 am
You could create a passthrough Access query, build up an SQL string according to the parameters on your form and then update the SQL property of the querydef, along the lines of
Dim qd as QueryDef, SQLtxt as String
set qd=db.QueryDefs("passthrough1")
SQLtxt="SELECT * FROM table WHERE Costcode='" & me!costcode
qd.SQL=SQLtxt
qd.close
Scott
--
Scott
May 11, 2005 at 3:25 am
Hi Scott
I'm pretty new to this so have been struggling to come to grips with what sounds a great solution.
I've created a Pass-Through query using Access query design view - which builds the connection string for you. This works well.
What I will need to do is to run a report using the parameters specified on a form. How do I use the querydef? I guess I need it to be the recordsource of the report?
Many thanks for your help
Paul
May 11, 2005 at 3:34 am
Paul
That's right. On your form, you could put a command button with code behind it which:
1 Checks that all necessary parameters have been entered and validates them
2 Builds the SQL of the passthrough query (as in my first response) and amends the querydef accordingly
3 Opens a report based on the passthrough query
Remember that the SQL string must be built according to SQL Server syntax, not Access i.e. ' not " etc. - I've spent many happy hours pasting SQL strings into Query Analyzer to see where I went wrong!
Scott
--
Scott
May 11, 2005 at 3:58 am
Hi Scott
I tried the following code on the On Open event of the report and it returned a mismatch. I guess I am in error here. I have tried to pare it down to the minimum just to test it. Pass_tblTeamFin_MP is the name of the Pass-Through query I created in query design.
Private Sub Report_Open(Cancel As Integer)
Dim qd As QueryDef, SQLtxt As String
Set qd = db.QueryDefs("Pass_tblTeamFin_MP")
'SQLtxt = "SELECT * FROM table WHERE Costcode='" & Me!CostCode
'qd.SQL = SQLtxt
'qd.Close
Me.RecordSource = qd
End Sub
I don't really understand the QueryDef usage....
Many thanks again
Paul
May 12, 2005 at 2:30 am
If I were you I'd put the code that amends the querydef in the form where you are entering the costcode. That way your report never has to change, as its recordsource is just set to Pass_tblTeamFin_MP. Your SQLtxt looks like it needs amending - if CostCode is a character field it should be
"SELECT * FROM table WHERE Costcode='" & Me!CostCode & "'"
if it is numeric use
"SELECT * FROM table WHERE Costcode=" & Me!CostCode
If you're using ADO I think you'd need to add
Dim db As Database
Set db=currentdb
after your existing Dim statement.
Access Help is not very good with the QueryDef object. Google is your friend here.
--
Scott
May 12, 2005 at 3:05 am
Hi Scott
The penny has eventually dropped - and it works, Fantastic. And it's really impressive.
Many thanks for all your time and trouble.
Paul
May 12, 2005 at 3:09 am
No problem. I'm really getting into passthrough queries - I often see a query run up to 10 times faster this way!
Glad to help
--
Scott
May 13, 2005 at 2:13 am
Yes, it's the speed which has impressed me.
Thanks, again Scott
Paul
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply