...should add something. The statement you use in sqlserver to execute the storedprocedure is the sql statement you should use in the passtroughquery (ptq). In this ptq check the properties because you need the right (ODBC) connection to the database.
What and how depends on your environment. What version of Access and SQL Server? Also, may need to do something different depending on using within a Form, Report, Recordset, or VBA code. To get the best information give an example of how you intend to use.
CREATE PROCEDURE dbo.gensp_GetSearchResults1 (@p_vkeyword varchar(128)
AS
declare @SQL varchar (400)
Set ...
exec (@SQL)
GO
Based on SQL Syntax Checking, the above code has no error.
The code below, I made in Access Code 2000
Dim adoConnection As ADODB.Connection
Dim adoCommand As ADODB.Command
Dim adoParm As ADODB.Parameter
Dim rsSet As ADODB.Recordset
Dim strName As String
Set adoConnection = New ADODB.Connection
adoConnection.Open "ODBC;DATABASE=cdiSeiko;UID=sa;PWD=;DSN=cdiSeiko"
The ADO connection has an Error collection. Set up a loop ON ERROR to go through this collection since there can be multiple errors and you will get the bottom line description not as in this case something ODBC probably is giving you and does not get to the problem.
One more thing. Prefix the objects with dbo. otherwise when a non database owner runs there will be permissions problem.
Here is an example of error handling in a function.
Public Function SomeFunction()
On Error GoTo ErrHandler
Your code to deal with ADO recordsets etc.......
Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Function
Thank you very much cmmrfrds and to all of you who help to solve this problem. I will try your suggestions and will be back to inform you how I solve this problem. Once again, Thank you and have a nice day!
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.