-4

I need to execute a stored procedure in MS Access 2013. I use Access as front end interface which is connected to two databases. One database has a few stored procedures. I need to execute those with push of button in the Access.

Database is connected through SQL Server authentication. I started a button in Access and here is the VBA code behind this button. It's empty and just simply need more code to get it running:

Private Sub UpdateItems_Click()     
End Sub

Here are the parameters to establish the connection and stored procedure name:

Server Name: BOX\SQL2014
User Name: sa
Password: 123456PS
Database: SixBit
Stored procedure name: spRefreshItems

Please don't refer me to similar question. I don't work with VBA Code and i wasn't able to mimic something from slightly different but similar example.

Vasily
  • 5,707
  • 3
  • 19
  • 34
theAccountant.py
  • 133
  • 2
  • 9
  • 2
    Also, SO isn't here to get people to write your code for you. It is to help teach you how to fix an issue you're having. So if you aren't willing to learn from similar examples this isn't going to go very well. – Mike D. Jul 07 '15 at 00:48
  • Do **not** repost the same question. **Edit** your existing post to add any new information to it. – Matt Jul 07 '15 at 07:09

1 Answers1

1

try this:

Sub ExecSP()
    Dim connection As Object: Set connection = CreateObject("ADODB.Connection")
    Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
    With connection
        .ConnectionString = "DRIVER=SQL Server;SERVER=BOX\SQL2014;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=SixBit;User ID=sa;Password=123456PS"
        .CommandTimeout = 0
        .Open
    End With
    '<<Replace SchemeName by Scheme name of the SP>>'
    Set rs = connection.Execute("EXEC SchemeName.spRefreshItems") 
    connection.Close: Set rs = Nothing: Set connection = Nothing
    MsgBox "Done!"
End Sub
Vasily
  • 5,707
  • 3
  • 19
  • 34