Using QueryDefs for Calling Stored Procedures on SQL Server

[Guest]

Hello Friends,

I have the following code on the click event of a command button. It tries
to run a stored procedure on SQL Server from Access 97 and uses QueryDefs for
the same:

Dim Mydb As Database
Dim MyQ As QueryDef
Dim sWinNTUser As String
Dim rsTable As DAO.Recordset
Set Mydb = CurrentDb()
sWinNTUser = Environ("Username")

Set rsTable = Mydb.OpenRecordset("select * from Reconsolidation where
Selected = -1")
' Create a temporary QueryDef object that is not saved.
Set MyQ = Mydb.CreateQueryDef("")
' Connect string using the appropriate values for the server.
MyQ.Connect = "ODBC;DSN=ABC16;UID=" + sWinNTUser +
";Trusted_Connection=Yes"
' Set ReturnsRecords to false in order to use the Execute method.
MyQ.ReturnsRecords = False
' Set the SQL property and concatenate the variables.
MyQ.SQL = "exec StoredProc " + "'" + TargetTable + "'" + ", '020823',"
+ "'" + RCTable + "'" + "," + "'" + ChartTable + "'"
Debug.Print MyQ.SQL
MyQ.Execute --- Fails here
MyQ.Close
Mydb.Close

It is failing on the line 'MyQ.Execute' with the error message 'ODBC - Call
Failed"

I have checked the DSN configuration and everything looks OK. Am I missing
to specify some connection parameter here?

The sql works fine from Query Analyzer. Any help on this will be appreciated.

Thanks,
Hemil.

 

[Brian]

Hello Friends,

I have the following code on the click event of a command button. It tries
to run a stored procedure on SQL Server from Access 97 and uses QueryDefs for
the same:

Dim Mydb As Database
Dim MyQ As QueryDef
Dim sWinNTUser As String
Dim rsTable As DAO.Recordset
Set Mydb = CurrentDb()
sWinNTUser = Environ("Username")

Set rsTable = Mydb.OpenRecordset("select * from Reconsolidation where
Selected = -1")
' Create a temporary QueryDef object that is not saved.
Set MyQ = Mydb.CreateQueryDef("")
' Connect string using the appropriate values for the server.
MyQ.Connect = "ODBC;DSN=ABC16;UID=" + sWinNTUser +
";Trusted_Connection=Yes"
' Set ReturnsRecords to false in order to use the Execute method.
MyQ.ReturnsRecords = False
' Set the SQL property and concatenate the variables.
MyQ.SQL = "exec StoredProc " + "'" + TargetTable + "'" + ", '020823',"
+ "'" + RCTable + "'" + "," + "'" + ChartTable + "'"
Debug.Print MyQ.SQL
MyQ.Execute --- Fails here
MyQ.Close
Mydb.Close

It is failing on the line 'MyQ.Execute' with the error message 'ODBC - Call
Failed"

I have checked the DSN configuration and everything looks OK. Am I missing
to specify some connection parameter here?

The sql works fine from Query Analyzer. Any help on this will be appreciated.

Thanks,
Hemil.

I suggest examining the DBEngine.Errors collection to see if there is any
further information, and using the SQL Server SQL Profiler to see what, if
anything, is arriving at the server.

I can't see why you would need to specify the user name in the connection
string for a trusted connection. Might be worth trying without it.

 

[Guest]

Hi Brian,

On checking the Errors collection, I found that SQL server was returning an
error (related to 'Table already exists') which the access application wasn't
able to handle. I added an ErrorHandler to deal with it and now it works
fine.

Thank you very much for your help.

Regards,
Hemil.