?
Solved

how to call stored procedure from VBA access

Posted on 2009-04-22
14
Medium Priority
?
912 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Dagome
  • 8
  • 6
14 Comments
 
LVL 13

Accepted Solution

by:
adraughn earned 2000 total points
ID: 24209016
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
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 2000 total points
ID: 24209063
0
 

Author Comment

by:Dagome
ID: 24209663
vba pukes at  dim for  ADODB.Command
what am i missing?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Dagome
ID: 24209668
Compile error
User-defined type not defined
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24209745
From VBA, go to Tools - References and check to see if you have a reference for MS ADO.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24209776
you will see it listed in references as:

Microsoft ActiveX Data Objects 2.x
0
 

Author Comment

by:Dagome
ID: 24210107
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24215750
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24215875
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
 

Author Comment

by:Dagome
ID: 24216016
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24216070
do you need to pass a parameter from a form to the stored procedure or just run it?
0
 

Author Comment

by:Dagome
ID: 24216191
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
 
LVL 13

Expert Comment

by:adraughn
ID: 24218763
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
 

Author Closing Comment

by:Dagome
ID: 31573499
thank you, i have turned this over to our SQL guy in corporate office. thank you for your help.  
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question