Passing param with pass through query

[RAF]

Hi All,
It has been a long time since I have worked with Access and I really need
some help.
I have a form, on which I have an image. The image is a picture of the
client. I created a stored procedure that returns the image based on the
parameter sent.
My question is how do I do this? I tried the On Paint event but the code did
not fire. If the DataSourse property is used I do not know how to code it.
I could really use some help and I will be most appreciativ

 

[Mark Andrews]

If you have a sql server stored procedure and you want to call it with a
pass-thru query with sql something like:
"exec mystoredproc @myparam=50000" then you just recreate the
query with the sql you want at runtime. Below is some general code that
recreates a query at runtime.

HTH,
Mark
RPT Software
http://www.rptsoftware.com


Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting

Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef

If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"

Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub

Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel

End Sub

Everything I have done with pass-thru queries in the past I always recreated
the sql of the query

 

[RAF]

Thanks Mark,
IS there any reason you recreate the
sql statement instead of calling the stored procedure>
--
Thanks,
RAF

If you have a sql server stored procedure and you want to call it with a
pass-thru query with sql something like:
"exec mystoredproc @myparam=50000" then you just recreate the
query with the sql you want at runtime. Below is some general code that
recreates a query at runtime.

HTH,
Mark
RPT Software
http://www.rptsoftware.com


Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting

Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef

If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"

Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub

Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel

End Sub

Everything I have done with pass-thru queries in the past I always recreated
the sql of the query

 

[Mark Andrews]

I'm not sure what you mean? I just recreate the pass-thru query in Access.
The pass-thru query then calls the stored procedure when it runs.

This is in just a regular mdb file (in access 2003 or earlier).

There might be other ways, this is just the approach I have used in the
past.

Mark

Thanks Mark,
IS there any reason you recreate the
sql statement instead of calling the stored procedure>