Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Execute a SQL Server Store Procedure from MS Access VBA

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 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