Advertisement

02.08.2006 at 09:44PM PST, ID: 21729703
[x]
Attachment Details

Passing parameters via VBA to a stored procedure

Asked by dsstao in Microsoft Access Database

Tags: vba, stored, procedure, parameters

Hi all,

Normally I would consider putting this directly under the MS SQL category, but I'm pretty certain the problem I'm having lies with VBA/Access.

I am attempting to pass values from from as parameters to a stored procedure using the following:

Private Sub Command8_Click()

Dim SPCommand As ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set SPCommand = New ADODB.Command

SPCommand.CommandText = "spMarkAsInvoiced"
SPCommand.CommandType = adCmdStoredProc
SPCommand.CommandTimeout = 300
SPCommand.ActiveConnection = CurrentDb.Connection
SPCommand.Parameters("@InvNum").Value = Me.Text6
SPCommand.Parameters("@CID").Value = Me.ClientID
SPCommand.Parameters("@StartDate").Value = Me.StartDateForForm
SPCommand.Parameters("@StopDate").Value = Me.StopDateForForm
SPCommand.Execute

End Sub

However, when I attempt execution, I get "Run-time error '91': Object variable or With block variable not set" and after clicking "Debug" it highlights this line: SPCommand.ActiveConnection = CurrentDb.Connection from above.

Any thoughts on what I am doing wrong, or is there a better approach to execute this stored procedure while passing parameters that I'm unaware of?

Thanks!Start Free Trial
[+][-]02.08.2006 at 10:23PM PST, ID: 15910332

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.09.2006 at 06:57AM PST, ID: 15913157

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.09.2006 at 11:07AM PST, ID: 15915545

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.09.2006 at 03:51PM PST, ID: 15918001

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.09.2006 at 05:33PM PST, ID: 15918721

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 08:09AM PST, ID: 15923715

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 08:19AM PST, ID: 15923842

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02.10.2006 at 08:22AM PST, ID: 15923872

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Microsoft Access Database
Tags: vba, stored, procedure, parameters
Sign Up Now!
Solution Provided By: flavo
Participating Experts: 3
Solution Grade: A
 
 
[+][-]02.10.2006 at 08:23AM PST, ID: 15923878

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 08:44AM PST, ID: 15924101

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 08:58AM PST, ID: 15924249

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 09:31AM PST, ID: 15924643

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 11:08AM PST, ID: 15925513

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 12:19PM PST, ID: 15926228

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]02.10.2006 at 12:32PM PST, ID: 15926339

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32