Solved

Execute a SQL Server Store Procedure from MS Access VBA

Posted on 2011-09-12
1
353 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
[X]
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
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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