Call SQL Stored Procedure using Procedure clause

Question

I am trying to build a database for IT device inventory. It uses an MS Access Office 365 front-end with a SQL 2017 backend.

In the database, we don't want to delete records, simply archive them to another table. To do this, I created a stored procedure in SSMS and verified that it does the job properly.

I want VBA to call this stored procedure. For this procedure, I need to pass it identifying information. In VBA, I am trying to assign the server name value from a form to a variable that I can pass into a call of the stored procedure. I found examples using the EXEC command but Access tells me I must use the Procedure clause.

Private Sub Command148_Click()
    Dim SrvNameVar As String
    Dim strSQL As String
    Dim strParm As String
    SrvNameVar = Me.SrvName
    strParm = "PARAMETERS [Server Name] As CHAR;"

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    strSQL = strParm & "PROCEDURE dbo.sp_ArchiveServer [Server Name];"
    Set qdf = dbs.CreateQueryDef("SrvArchive", strSQL)

    dbs.Execute ("SrvArchive")
End Sub

The stored procedure that functions properly in SSMS:

CREATE PROCEDURE sp_ArchiveServer @Server nvarchar(30) AS
   BEGIN TRANSACTION;
   INSERT INTO FSC.dbo.Archive_Servers ([SrvID],[SID],[SrvName],[Make],
            [Model],[SN],[SrvIP],[RemoteMgmt],[OSID],[IsDP],[IsIEMRelay],
            [IsGUP],[DatePurch],[WarrantyExp],[RAIDConfig],[PrintSrv],
            [ConnectedToUPS],[VirtHost],[VirtMachine])
   SELECT FSC.dbo.Servers.*
   FROM FSC.dbo.Servers
   WHERE FSC.dbo.Servers.SrvName = @Server;

   DELETE FROM FSC.dbo.Servers
   WHERE FSC.dbo.Servers.SrvName = @Server;

   COMMIT;

Answer 1

Currently, you are conflating MS Access SQL dialect with SQL Server dialect. Only MS Access SQL queries supports PARAMETERS. However, you are attempting to run an SQL Server query, specifically to execute a stored procedure.

MS Access does allow pass-through queries to backend databases so you can adjust your QueryDef (defaults to Access backend) to connect to MSSQL database and then run EXEC command. All pass-through queries should conform to SQL dialect of backend.

Private Sub Command148_Click()
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim SrvNameVar, strSQL As String

   SrvNameVar = Me.SrvName
   strSQL = "EXEC dbo.sp_ArchiveServer @Server='" & SrvNameVar &"'"

   Set dbs = CurrentDb
   Set qdf = dbs.CreateQueryDef("SrvArchive")

   ' ASSIGN ODBC DSN CONNECTION
   qdf.Connect = "ODBC; DATABASE=database; UID=user; PWD=password; DSN=datasourcename;" 
   qdf.SQL = strSQL
   qdf.Execute
End Sub

To effectively use parameterization, consider a different API, namely ADO (extendable to any backend database) instead of DAO (more tailored for Access databases).

Private Sub Command148_Click()
   ' SET REFERENCE TO Microsoft ActiveX Data Object #.# Library
   Dim conn As ADODB.Connection, cmd As ADODB.Command
   Dim SrvNameVar As String

   SrvNameVar = Me.SrvName

   ' OPEN DRIVER OR DSN CONNECTION
   Set conn = New ADODB.Connection         
   conn.Open "DRIVER={SQL Server};server=servername;database=databasename;UID=username;PWD=password;"
   ' conn.Open "DSN=datasourcename"

   ' OPEN AND DEFINE COMMAND OBJECT
   Set cmd = New ADODB.Command     
   With cmd
       .ActiveConnection = conn
       .CommandText = "sp_ArchiveServer"
       .CommandType = adCmdStoredProc

       ' BIND PARAMETERS BY POSITION AND NOT NAME
       .Parameters.Append .CreateParameter("param1", adVarchar, adParamInput, 255, SrvNameVar)
       .Execute
   End With

   conn.close()
   Set cmd = Nothing: Set conn = Nothing
End Sub

Answer 2

Create a pass-though query in the Access designer.

You can type in that command in the query (sql view). So, you have a pass-though query,and it will look like this:

EXEC dbo.sp_ArchiveServer @Server='test'

Save the above query. (make sure it is pass through query).

Ok, now your VBA code will look like this:

With CurrentDb.QueryDefs("qryPass")
   .SQL = "EXEC dbo.sp_ArchiveServer @Server='" & Me.SrvName & "'"
   .ReturnsRecords = False
   .Execute
End With

Answer 3

Thank you everyone for your help! With all of the info that you provided, it is now working. To do it, I followed Albert's example creating the Pass Through query first and then appended his code with the information from Parfait and ErikA regarding the connection string. I then added a simple MsgBox command and a Close Form command to make it a little more "pretty". Here is the final code that worked:

Private Sub Command148_Click()

With CurrentDb.QueryDefs("SrvQryPass")
   .Connect = "ODBC;DSN=ODBC_17;Description=FSC;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=FSC;Network=DBMSSOCN;"
   .SQL = "EXEC dbo.sp_ArchiveServer @Server='" & Me.SrvName & "'"
   .ReturnsRecords = False
   .Execute
End With

MsgBox "Archived!"
DoCmd.Close

End Sub