how to call stored procedure from VBA access

experts, please save the noob once again :)

i have made a stored procedure as shown in SQL 2005 backend, how do i call it using VBA in access 2007 ?


thank you
ALTER PROCEDURE [dbo].[GenesisWO] 
	-- Add the parameters for the stored procedure here
	@projectID int = 0 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
INSERT INTO [Intecdb].[dbo].[Work_orders]([ProjectID],[Description])
VALUES (@projectID, 'Genesis WO')
END

Open in new window

DagomeAsked:
Who is Participating?
 
adraughnCommented:
Read my accepted answer here from a prior thread:

*********************************************
Pass-Thru query directions:
*********************************************
1. Create a query in design view in Access
2. From query menu, choose sql specific, pass thru query
3. Type execute upYourSprocName
4. From the View menu, select Properties, and put this line (modify it) in the ODBC Connect Str property:
ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServer_IP_Address;Trusted_Connection=Yes;



http://www.experts-exchange.com/Database/Miscellaneous/Q_23319798.html?sfQueryTermInfo=1+10+adraughn+pass+procedur+store+thru
0
 
adraughnCommented:
0
 
DagomeAuthor Commented:
vba pukes at  dim for  ADODB.Command
what am i missing?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DagomeAuthor Commented:
Compile error
User-defined type not defined
0
 
adraughnCommented:
From VBA, go to Tools - References and check to see if you have a reference for MS ADO.
0
 
adraughnCommented:
you will see it listed in references as:

Microsoft ActiveX Data Objects 2.x
0
 
DagomeAuthor Commented:
following code gives run-time error:

 the microsoft office access database engine connot find the input table or query 'genesisWO'.  make sure it exixts and that its name is spelled correctly


i am pretty sure the procedure exists and spelled correctly


i am missing another piece of the puzzle here.


thank you
Sub test()
 
   Dim abc As ADODB.Command
   Set abc = New ADODB.Command
   With abc
        .CommandType = adCmdStoredProc
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "GenesisWO"
        .Parameters.Append .CreateParameter("@projectID", adInteger, adParamInput, , 4418)
        .Execute
        
   End With

Open in new window

0
 
adraughnCommented:
ok. before we go thru troubleshooting this, is there a reason why you don't want to use a pass thru query to execute the stored procedure? It's a lot less code and it is very easy.
0
 
adraughnCommented:
This thread shows how to pass parameters to a stored procedure using a pass thru query:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/ADP/Q_23311019.html?sfQueryTermInfo=1+10+adraughn+changesql+pass+thru
0
 
DagomeAuthor Commented:
good morning adraughn, thanks for bearing with me so far :)

I do not have a good reason not to use the pass thru query ( I was even able to make it work)  but I do not know how to trigger it inside a form.
there is a button on a form that triggers some VBA code right there is where I need to add this SP.

I guess if there is a way for a button to run some VBA and then run a pass thru query...

yes I am very new to this :)


thanks
0
 
adraughnCommented:
do you need to pass a parameter from a form to the stored procedure or just run it?
0
 
DagomeAuthor Commented:
i need to pass ProjectID to the procedure
the vba function that runs at the button press creates the record in projects table with the ProjectID i want. thats why i wanted to just append that function with the procedure call.


0
 
adraughnCommented:
ok. so first create the pass thru query as instructed here:
(Let me know once you have done that and send me the PT query name and copy syntax and I will walk you thru the rest)
*********************************************
Pass-Thru query directions:
*********************************************
1. Create a query in design view in Access
2. From query menu, choose sql specific, pass thru query
3. Type execute upYourSprocName
4. From the View menu, select Properties, and put this line (modify it) in the ODBC Connect Str property:
ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServer_IP_Address;Trusted_Connection=Yes;
0
 
DagomeAuthor Commented:
thank you, i have turned this over to our SQL guy in corporate office. thank you for your help.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.