Link to home
Start Free TrialLog in
Avatar of Anthony Berenguel
Anthony BerenguelFlag for United States of America

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:
 
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

Open in new window


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") then I get the following error:
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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Do you need to have anything returned from the SP? If not, the quickest way to do this is just calling the SP directly:

CurrentProject.Connection.Execute "EXEC sp_getMasterFileForPDF  @PDFName='" & YourPDFNameValue & "'"
Avatar of Anthony Berenguel

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.
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?

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.
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;Initial Catalog=myDataBase;Integrated 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
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks, LSMConsulting. Got it working.