Solved

Execute a SQL Server Store Procedure from MS Access VBA

Posted on 2011-09-12
1
333 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

770 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