Query SQL procedure - with results 1

[micang]

Access 2007

Hi All,

I have the code below that runs a stored procedure on an SQL 2008 database from Access.

This bit works great and the stored procedure runs, but now I would like to use the results from the stored procedure in the Access database.

I am stuck at what to add to this VBA code to capture the results and use them in the Access db.

DoCmd.Hourglass True
 
Dim strConnect As String
    Dim strSQL As String
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim strErrMsg As String

           Set dbs = CurrentDb
            
            strConnect = "ODBC;DSN=******;UID=***;PWD=****;"
                         
            Set qdf = dbs.CreateQueryDef("")
            qdf.Connect = strConnect
            strSQL = "exec test_proc" 'name of procedure here
            qdf.ReturnsRecords = True
            qdf.SQL = strSQL
            dbs.QueryTimeout = 2000 'Set timeout to 4 minutes so it doesn't timeout.
            qdf.ODBCTimeout = 1000
            qdf.Execute
            DoCmd.Hourglass False

Any assistance in the correct direction greatly appreciated.

Regards,
Michael

 

[redwoodly]

How do you want to use the results in the db?
For example, do you want to append them to a table? create a table with the results?

 

[micang]

Hi redwoodly,

Thank you for your reply.

I would like to show the results either directly in to a list box on the form or insert them into a table and then take it from there.

No preference really, the easier option will be fine.

Many thanks for your assistance.

Michael

 

[dhookom]

I generally create a saved Pass-Through query. You can use code similar to what you have written to change the connection or SQL or whatever. Then just use the P-T query as the Record Source of anything that has a Record Source or Row Source property.

Duane
Hook'D on Access
MS Access MVP

 

[micang]

Thanks dhookom, I have implemented that very method and works very well for my purposes.

Many thanks.

Michael