Anthony Berenguel
asked on
Access VBA calling stored procedure issues
Hello,
I've read the threads about how to call stored procedures within access vba and I took an example and tried to make it work for me. However, I keep getting errors messages. I'm hoping an expert can sort me out.
Here's my code:
I get the following error message when i try to run the code:
However, if I add a dot before my stored precudre name (".sp_getMasterFileForPDF" ) then I get the following error:
I've never tried calling a stored procedure before, so I am very new to the concept. Any help is appreciated.
thanks!
Anthony
I've read the threads about how to call stored procedures within access vba and I took an example and tried to make it work for me. However, I keep getting errors messages. I'm hoping an expert can sort me out.
Here's my code:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "sp_getMasterFileForPDF "
.Parameters.Append .CreateParameter("@PDFname", adVarChar, adParamInput, 200, PDF_NAME)
.Execute
End With
I get the following error message when i try to run the code:
The Microsoft Jet database engine cannot find the input table or query 'sp_getMasterFileForPDF'. Make sure it exists and that its name is spelled correctly.
However, if I add a dot before my stored precudre name (".sp_getMasterFileForPDF"
Expected query name after EXECUTE.
I've never tried calling a stored procedure before, so I am very new to the concept. Any help is appreciated.
thanks!
Anthony
ASKER
Remember I am really new to SQL and stored procedures.
Anyway, the store procedure is doing a select query, and i'd like to use the results of that select query to populate a recordset.
Anyway, the store procedure is doing a select query, and i'd like to use the results of that select query to populate a recordset.
ASKER
LSMConsulting,
i tried you statement and received the following,
"The Microsoft Jet database engine cannot find the input table or query 'sp_getMasterFileForPDF'. Make sure it exists and that its name is spelled correctly."
I know for sure that is what the store procedure is called. Am I not connecting to SQL correctly?
i tried you statement and received the following,
"The Microsoft Jet database engine cannot find the input table or query 'sp_getMasterFileForPDF'. Make sure it exists and that its name is spelled correctly."
I know for sure that is what the store procedure is called. Am I not connecting to SQL correctly?
Is your database connected to the SQL Server (i.e. linked tables)? If it's not, you'll have to build a connection and connect to that server.
ASKER
Yes, my db has linked tables from the SQL server.
Note to return a Recordset, you'd do this:
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Data Source=myServerAddress;Ini tial Catalog=myDataBase;Integra ted Security=SSPI;"
Set rst = New ADODB.Recordset
Set rst = con.Execute("EXEC sp_getMasterFileForPDF @PDFName='" & YourPDFNameValue & "'", , acCmdStoredProc )
Connection String examples here: http://www.connectionstrings.com/sql-server-2008
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Data Source=myServerAddress;Ini
Set rst = New ADODB.Recordset
Set rst = con.Execute("EXEC sp_getMasterFileForPDF @PDFName='" & YourPDFNameValue & "'", , acCmdStoredProc )
Connection String examples here: http://www.connectionstrings.com/sql-server-2008
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, LSMConsulting. Got it working.
CurrentProject.Connection.