asked on

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

ASKER CERTIFIED SOLUTION

adraughn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

adraughn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dagome

ASKER

vba pukes at dim for ADODB.Command
what am i missing?

Dagome

ASKER

Compile error
User-defined type not defined

adraughn

From VBA, go to Tools - References and check to see if you have a reference for MS ADO.

adraughn

you will see it listed in references as:

Microsoft ActiveX Data Objects 2.x

Dagome

ASKER

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

adraughn

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.

adraughn

This thread shows how to pass parameters to a stored procedure using a pass thru query:

https://www.experts-exchange.com/questions/23311019/Access-2003-adp-and-SQL-Server-2000-connection-issues.html?sfQueryTermInfo=1+10+adraughn+changesql+pass+thru

Dagome

ASKER

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

adraughn

do you need to pass a parameter from a form to the stored procedure or just run it?

Dagome

ASKER

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.

adraughn

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;

Dagome

ASKER

thank you, i have turned this over to our SQL guy in corporate office. thank you for your help.