-1

I am trying to launch a SQL Server stored procedure from a button in a MS Access form. The linked server has already been created but I am a little confused about launching the stored procedure from Access. The procedure will update records in SQL Server based on several parameters. This is working as intended, it must just simply run when a button is pressed on a MS Access form. After looking online it appears there are 3 different ways of doing the same thing?!

There is:

EXEC
OPENROWSET
OPENQUERY

Which is best to use and do these even do what I need or am I just confused?

We are using MS Access simply as a front end and I just need a way to be able to press a button on a MS Access form and it run a SQL Server stored procedure

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joshylad
  • 133
  • 1
  • 16
  • OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name. – Vlam Oct 01 '18 at 08:07
  • You can use `EXEC` following the procedure name to run it. – Mikku Oct 01 '18 at 10:10

1 Answers1

2

I managed to get it working with the following VBA script running from the button on the Access form

Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVERNAME;Database=DATABASENAME;Trusted_Connection_YES;"
cn.Open

With cmd
    .ActiveConnection = cn
    .CommandText = "PROCEDURENAME"
    .CommandType = adCmdStoredProc
    cmd.Execute
End With

Set cmd = Nothing

End Sub
Joshylad
  • 133
  • 1
  • 16