Options for SQL Server Parameterized Pass Through Queries... 1

[dougcoulter]

Hello everyone - I recently ported several access tables to SQL Server.

A couple of these tables were "accessed" via parameterized Access queries - either for use in reports, or just to view/modify the data.

The users are comfortable with Access as the front end, but I am just not sure of how to handle the parameterized requirement of the data. I can certainly create SQL stored procedures with parameters - but I can't see how to prompt for the parameters without using VBA.

Any suggestions would be greatly appreciated!

Thanks!

 

[dhookom]

Consider using VBA. I would create a single function to change the SQL property of saved queries.

Public Function ChangeSQL(pstrQuery as String, _
        pstrSQL As String) As String
    ChangeSQL = CurrentDb.QueryDefs(pstrQuery).SQL
    CurrentDb.QueryDefs(pstrQuery).SQL = pstrSQL
End Function

Save the above function in a normal module and name the module "modQueries".

You can call this function like:

    Dim strSQL as String
    Dim strQueryName as String
    Dim strPreviousSQL as String
    strQueryName = "qsptMyPassThrough"
    strSQL = "EXEC spMyProc '" & Me.txtStartDate & "', '" & _
         Me.txtEndDate & "'"
    strPreviousSQL = ChangeSQL(strQueryName, strSQL)
    'your pass-through query now contains a new sql property

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sedgely]

Have just come across this thread and it is just what i needed. Have a star Duane

Cheers, Craig
Si fractum non sit, noli id reficere