SQL pass through query

[amjad]

I am using access as front end and using sql pass through query to get data..
is thier any way to call parametrized store procedure using vba not directly
but using pass query method mean first pass the data to tat query and then
execute that query through vba..... Am i rite or any other good idea......
thanks

 

[Douglas J. Steele]

You can rewrite the SQL for the query in code.

Dim dbCurr As DAO.Database
Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1, Field2 FROM MyTable " & _
"WHERE Id = " & Me.txtId & _
" AND Customer = '" & Replace(Me.txtName, "'", "''") & "'" & _
" ORDER BY Field1"
Set dbCurr = CurrentDb()
Set qdfPassthrough = dbCurr.QueryDefs("MyPassthroughQuery")
qdfPassthrough.SQL = strSQL

 

[amjad]

I am using store procedure which has three parameters to pass through 'pass
through query" using vba

 

[Douglas J. Steele]

The SQL of your pass-through query, then, must be referring to the stored
procedure.

You'd change the SQL using the same approach as below.

Dim dbCurr As DAO.Database
Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "exec MySP " & _
"@Id = " & Me.txtId & _
"@Customer = '" & Replace(Me.txtName, "'", "''") & "'" & _
"@OrderDate = " & Format(Me.txtDate, "\'yyyy\-mm\-dd\')
Set dbCurr = CurrentDb()
Set qdfPassthrough = dbCurr.QueryDefs("MyPassthroughQuery")
qdfPassthrough.SQL = strSQL

 

[amjad]

How is possible with ado instead of dao thanks

The SQL of your pass-through query, then, must be referring to the stored
procedure.

You'd change the SQL using the same approach as below.

Dim dbCurr As DAO.Database
Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "exec MySP " & _
"@Id = " & Me.txtId & _
"@Customer = '" & Replace(Me.txtName, "'", "''") & "'" & _
"@OrderDate = " & Format(Me.txtDate, "\'yyyy\-mm\-dd\')
Set dbCurr = CurrentDb()
Set qdfPassthrough = dbCurr.QueryDefs("MyPassthroughQuery")
qdfPassthrough.SQL = strSQL

 

[Douglas J Steele]

While it is possible to use ADOX to do this, this really is a case where it
makes far more sense to use DAO. You're working with Jet objects, and DAO
was developed specifically to work with Jet objects.

Once you've modified the query using DAO, you can run it using ADO.