Solved

Execute a SQL Server Store Procedure from MS Access VBA

Posted on 2011-09-12
1
327 Views
Last Modified: 2012-05-12
Hi Everyone,

I have an Access form that populates some backend tables on SQL Server. What I want to do is call a stored Procedure from Access VBA.

I heard this can be done through the use of a Pass-Through query.

-----------------------------------------------------------------------------------------------------------------
 More specifically here are some of the details:

The name of the database is:                                'NewTestPhase'

The server name is:                                               'PLMSSQL1'

The name of the stored procedure to be called is:         'dbo.aa_table_maintenance'

Finally, the stored procedure takes two parameters when called. For which I want to use the variables:   IDContactsGUD and IDAddressGUID.

The ODBC DSN is:                                                    'NewPhase_Test'

--------------------------------------------------------------------------------------------------------

(PS: ALL NAMES ARE FICTIONALLY ALTERED FROM THE REAL NAMES)

Thank you everyone,

Jon
0
Comment
Question by:jonsuns7
1 Comment
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 36528437
Hi Jon

There are several ways to do this, all involve some coding. The way I'd suggest is to use ADO and here is an example of this which I've tried to customise for your requirements.


Function RunSproc(sIDContactsGUID as string, sIDAddressGUID as string  ) as boolean

    Dim conADO As New ADODB.Connection
    Dim cmdADO As New ADODB.Command
    Dim paramTemp As ADODB.Parameter
    
    conADO.Open "DSN='NewPhase_Test'"
    cmdADO.CommandType = adCmdStoredProc
    cmdADO.CommandText = "aa_table_maintenance"
    Set paramTemp = cmdADO.CreateParameter("ContactGUID", adVarWChar, adParamInput, 50, sIDContactsGUID)
    cmdADO.Parameters.Append paramTemp
    Set paramTemp = cmdADO.CreateParameter("AddressGUID", adVarWChar, adParamInput, 50,sIDAddressGUID)
    cmdADO.Parameters.Append paramTemp
    
    cmdADO.ActiveConnection = conADO
    cmdADO.Execute
    
    Set cmdADO = Nothing
    Set conADO = Nothing
    runSproc=True

End Function

Open in new window


You will need to add a module, copy this code in, and make sure you have a reference set to "Microsoft ActiveX Data objects" in the Tools/references menu. Choose the latest version if you have more than one.

Finally, to run this you simply call the function with the values to use, eg

if RunSproc(IDContactsGUD, IDAddressGUID) = True then debug.print "Success!"

Open in new window


Hope that helps.

Regards
John
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now