Stored Procedures

[Keith Greeley]

I have created an ODBC connection 'conPubs',
and I have successfully managed to use the CreateQueryDef to use a SQL string to extract the info. It says in the documentation that you can substitute this SQL string with a Stored Procedure name. So I wrote a basic stored procedure in SQL Enterprise, added it to the Database, and tried to run this sp name instead of the SQL string from the client. I got an error message 3146 ODBC call failed.
The stored procedure name is 'pr_vb_test'
the code is:

strSQL = "pr_vb_test"
Set qdfTemp=conPubs.CreateQueryDef("",strSQL)
Set rsTemp=qdfTemp.Openrecordset

Can anyone help please?

[Roy H]

You cannot do what you are trying, I think you have misunderstood the documenation. CreateQueryDef( "", strSQL ) is trying to create a temporary QueryDef and excepts strSQL to be a valid SQL statement which in your case it is not, it's 'pr_vb_test' so the call will fail.

In fact you do not even need a QueryDef for what you are trying to do. Just open the recordset passing the name of the query like this:

Dim rsTemp as Recordset
Set rsTemp = conPubs.OpenRecordSet( "pr_vb_test" )