Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 10th, 2007, 03:18 PM
Registered User
Join Date: Aug 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Running stored procedure from .adp module

I'm trying to run a Do.Cmd open stored procedure that takes two arguments. The arguments are global variables that were set in Access during an Event procedure. I can't seem to get the syntax of this method. The SQL Server arguments are named the same as VBA Globals. Without the arguments it runs just fine, but with it's a different story. Thank you in advance

DoCmd.OpenStoredProcedure "spEVA_Export @EndDate='" & EndDate & "', @Account1='" & Account1 & "'"

Old August 13th, 2007, 12:17 PM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

The problem probably has to do with taking the values into the string at run time. SQL Server doesn't like this. Try this:

Dim dtEnd As Date
Dim sAccount As String
Dim sSQL As String

dtEnd = Me.EndDate
sAccount = Me.Account

sSQL = "spEVA_Export @EndDate='" & EndDate & "', @Account1='" & Account1 & "'"

DoCmd.OpenStoredProcedure sSQL

This might work. Sorry for the duplicate code.


Similar Threads
Thread Thread Starter Forum Replies Last Post
running scrolling text from stored procedure in VS cJeffreywang ASP.NET 2.0 Basics 1 August 14th, 2007 03:47 PM
Error When Running Stored Procedure Dwizz SQL Server 2000 4 May 15th, 2007 09:07 AM
ADP Update Stored Procedure ashg657 Access 0 June 2nd, 2006 05:29 AM
Stored procedure as the rowsource for Report (ADP) Tero Access 1 May 12th, 2004 08:22 PM
Use of "IF" in a ADP Stored Procedure Mickey123 Access 0 December 5th, 2003 01:53 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.