Solved

Execute a SQL Server Store Procedure from MS Access VBA

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

19 Experts available now in Live!

Get 1:1 Help Now