Passing a QueryDef to a function ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Passing a QueryDef to a function ?

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-07-01 : 08:32:59

I have the following bit of code. I want if possible to be able to pass in the query name (the one in bold) as a variable instead of having it hard coded as shown. Is this possible to do ??

I know i can get around it by constructing a querydef with SQL but it would be easier if there was a way of passing the query name instead. i can then make my procedure completely generic.

many thanks
Paul


Private Sub GenerateRankings(strSelectStatement As String, strUpdateStatement As String)
On Error GoTo ErrorHandler:
''''Generate the rank for each score
    Dim rstOverall As Recordset, qdfOverall As QueryDef, qdfUpdate As QueryDef
    Dim rstMainTable As Recordset, qdfMainTable As QueryDef
    Dim strSQL As String, dblPreviousScore As Double, dblCurrentScore As Double
    Dim intRank As Integer, intNextRank As Integer, booSame As Boolean
    
    Set dbs = CurrentDb
    
    Set qdfOverall = dbs.CreateQueryDef("", strSelectStatement)
    Set rstOverall = qdfOverall.OpenRecordset(dbOpenSnapshot)
    
    Set qdfMainTable = dbs.QueryDefs("qryBreakoutByVoterRegion_Overall")
    Set rstMainTable = qdfMainTable.OpenRecordset(dbOpenDynaset)

Paul

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-01 : 09:19:33

I'm not familiar with this method of creating recordsets....but can you not do??

Set qdfMainTable = dbs.QueryDefs(strqueryname)

where

Private Sub GenerateRankings(strSelectStatement As String, strUpdateStatement As String, strqueryname as String)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-01 : 09:21:19

Paul....only after seeing that this is an Access problem....my advice may be considered "of limited/no use"!

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-07-01 : 12:26:00

Great advice. So obvious i saw striaght through it.

thanks

Paul

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-07-01 : 13:30:45

Check out the Eval function, that should work for you.

*************************
Someone done told you wrong!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources