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;

FYI, the prefix sp_ is reserved by Microsoft (for Special Procedures) and should not be used for User Stored Procedures. Is the sp_ prefix still a no-no? — Thom A
– Thom A ♦, Commented Aug 6, 2020 at 13:17
Does this answer your question? Calling stored procedure using VBA — SMor
– SMor, Commented Aug 6, 2020 at 13:25

Parfait · Accepted Answer · 2020-08-06 17:14:07Z

3

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

edited Aug 6, 2020 at 17:14

answered Aug 6, 2020 at 14:08

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mattyd Over a year ago

Thanks for the suggestion! I put it in place and it still kicks back Run-Time Error 3129. Invalid SQL Statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE on the non-Parameterized code

Parfait Over a year ago

Even with the required .Connect line? Error indicates you are not running a pass-through query. Note you are to adjust the parameters, not keywords, of connection string.

Mattyd Over a year ago

The Visual Basic editor is flagging the qdf.SQL = strSQL line

Erik A Over a year ago

You need to assign .Connect before .SQL. Assign .Connect immediately after creating the QueryDef.

Parfait Over a year ago

Indeed. Thanks @ErikA. The ODBC connection defines which SQL dialect to apply for querydef. Mattyd, delete previous query before re-trying.

|

Albert D. Kallal · Accepted Answer · 2020-08-07 01:38:20Z

1

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

answered Aug 7, 2020 at 1:38

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Comments

Mattyd · Accepted Answer · 2020-08-07 11:37:48Z

0

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

answered Aug 7, 2020 at 11:37

Mattyd

192 bronze badges

1 Comment

Albert D. Kallal Over a year ago

Well, if you have existing linked tables, then the PT query also just like tables has a connection string. So, as per my example code, no need to bother with the connection stuff in code. In other words when you link and setup your tables, also link and setup the connection for the PT query you have. That way no connection strings or code in VBA is required. So, not having to deal with connection strings in code is good idea, since if later on you change the linked tables and the database you are connected to? Then you not have to go find VBA code with connection strings either.

Collectives™ on Stack Overflow

Call SQL Stored Procedure using Procedure clause

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related