Access Pass-Through Abfrage - Aufruf eine Stored Procedure auf SQL Server

[Trombomaniac]

Hallo zusammen,

ich habe eine SQL-Datenbank mit einem Access-Frontend. Die Datenbank erfasst Jobs meiner Abteilung.
Bis jetzt ist es so, dass die Requester zu uns kamen und wir dann den Job eröffnet haben.
Die Requester haben nur Lese-Zugriff auf das SQL-Backend und das kann laut Firmenvorgabe auch nicht geändert werden.

Ich möchte nun aber, dass die Requester ihre Jobs selbst eintragen.
Und zwar soll das so gehen:
Ich habe eine Stored Procedure (SP) auf dem SQL Server erstellt, die den Update-Process übernimmt.
Diese funktioniert auch, wenn ich die SP auf dem Server direkt ausführe.

Code [Auswählen] Erweitern

USE [FMA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Insert_tbl_StD_Data]    Script Date: 20/Nov/2023 13:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Insert_tbl_StD_Data]
@StD_Job_ID INT,
@StD_Requester_ID_F INT,
@StD_Priority_ID_F INT,
@StD_Owner_ID_F INT, 
@StD_Date_Received Datetime,
@StD_ChipGen_ID_F INT,
@StD_ChipID nvarchar(255),
@StD_NumberChips INT,
@StD_SAPProjects_ID_F INT,
@StD_Topic nvarchar(255),
@StD_Description nvarchar(max),
@StD_Edit_Person nvarchar(255),
@StD_Edit_Date Datetime

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO dbo.tbl_StD_Data([StD_Job_ID],[StD_Requester_ID_F], [StD_Priority_ID_F],[StD_Owner_ID_F], [StD_Date_Received], [StD_ChipGen_ID_F],
[StD_ChipID], [StD_NumberChips],[StD_SAPProjects_ID_F],[StD_Topic],[StD_Description],[StD_Edit_Person],[StD_Edit_Date],[StD_EDXfrFac])
SELECT
@StD_Job_ID AS StD_Job_ID,
@StD_Requester_ID_F AS StD_Requester_ID_F,
@StD_Priority_ID_F AS StD_Priority_ID_F,
@StD_Owner_ID_F AS StD_Owner_ID_F,
@StD_Date_Received AS StD_Date_Received,
@StD_ChipGen_ID_F AS StD_ChipGen_ID_F,
@StD_ChipID AS StD_ChipID,
@StD_NumberChips AS StD_NumberChips,
@StD_SAPProjects_ID_F AS StD_SAPProjects_ID_F,
@StD_Topic AS StD_Topic,
@StD_Description AS StD_Description,
@StD_Edit_Person AS StD_Edit_Person,
@StD_Edit_Date AS StD_Edit_Date,
0 AS StD_EDXfrFac

END


Ich habe eine Pass-Through-Abfrage (PT) im Frontend erstellt, mit der ich die SP auf dem Server aufrufe.

Code [Auswählen] Erweitern

exec sp_Insert_tbl_StD_Data
ODBC-Verbindung

Code [Auswählen] Erweitern

ODBC;DSN=DSNSQLSERVER;Trusted_Connection=Yes;DATABASE=XXX

Nun rufe ich diese PT über VBA aus einem Formular auf.
Dabei versuche ich die Formularwerte (die noch in keiner Tabelle gespeichert sind) zu übergeben.

Code [Auswählen] Erweitern

Private Sub cmdSendRequest_Click()
                 Dim db As DAO.Database
                Dim qdf As DAO.QueryDef
               Dim prm1 As DAO.Parameter
               Dim prm2 As DAO.Parameter
               Dim prm3 As DAO.Parameter
               Dim prm4 As DAO.Parameter
               Dim prm5 As DAO.Parameter
               Dim prm6 As DAO.Parameter
               Dim prm7 As DAO.Parameter
               Dim prm8 As DAO.Parameter
               Dim prm9 As DAO.Parameter
              Dim prm10 As DAO.Parameter
              Dim prm11 As DAO.Parameter
              Dim prm12 As DAO.Parameter
              Dim prm13 As DAO.Parameter
             
                       Set db = CurrentDb
                      Set qdf = db.QueryDefs("qpt_Request_Insert")
                     
                     Set prm1 = qdf.Parameters("@StD_Job_ID")
                   prm1.Value = Me.txtJob_ID
                     Set prm2 = qdf.Parameters("@StD_Requester_ID_F")
                   prm2.Value = Me.cboRequester.Column(0)
                     Set prm3 = qdf.Parameters("@StD_Priority_ID_F")
                   prm4.Value = Me.cboPriority.Column(0)
                     Set prm4 = qdf.Parameters("@StD_Owner_ID_F")
                   prm4.Value = Me.cboOwner.Column(0)
                     Set prm5 = qdf.Parameters("@StD_Date_Received")
                   prm5.Value = Me.txtDateReceived
                     Set prm6 = qdf.Parameters("@StD_ChipGen_ID_F")
                   prm6.Value = Me.cboChipGeneration.Column(0)
                     Set prm7 = qdf.Parameters("@StD_ChipID")
                   prm7.Value = Me.txtChipID
                     Set prm8 = qdf.Parameters("@StD_NumberChips")
                   prm8.Value = Me.txtNumberChips
                     Set prm9 = qdf.Parameters("@StD_SAPProjects_ID_F")
                   prm9.Value = Me.cbo_SAP_Project.Column(0)
                    Set prm10 = qdf.Parameters("@StD_Topic")
                  prm10.Value = Me.txtTopic
                    Set prm11 = qdf.Parameters("@StD_Description")
                  prm11.Value = Me.txtDescription
                    Set prm12 = qdf.Parameters("@StD_Edit_Person")
                  prm12.Value = Me.txtEditUser
                    Set prm13 = qdf.Parameters("@StD_Edit_Date")
                  prm13.Value = Me.txtEditDate
                  qdf.Execute
                  qdf.Close
End Sub
Leider gibt es beim Test dann immer eine Fehlermeldung
Fehlermeldung "3265" - Element in Auflistung nicht gefunden.

Ich habe keine Ahnung, warum dieser Fehler kommt, noch wie ich diesen löse.
Kann mir jemand helfen?
Falls Info's fehlen, liefere ich diese gerne nach.

Danke im Voraus und Gruss

[PhilS]

Ich habe eine Stored Procedure (SP) auf dem SQL Server erstellt, die den Update-Process übernimmt.

In der Stored Procedure solltest du noch ein EXECUTE AS ... ergänzen, sonst hilft dir die nicht wirklich weiter.

Leider gibt es beim Test dann immer eine Fehlermeldung
Fehlermeldung "3265" - Element in Auflistung nicht gefunden.

Ich glaube nicht, dass du die Werte über die Parameters Collection an die Pass-Through-Abfrage übergeben kannst. Du wirst den kompletten SQL-String der Pass-Through-Abfrage mit den Werten aufbauen müssen.

[Trombomaniac]

Vielen Dank für die schnelle Antwort.

In der Stored Procedure solltest du noch ein EXECUTE AS ... ergänzen, sonst hilft dir die nicht wirklich weiter.

OK, Wo genau muss ich das denn eingeben?
Ich kenne mich mit Stored Procedures leider noch nicht gut aus.
Die aktuelle wurde von unserer IT-Abteilung erstellt.

Ich glaube nicht, dass du die Werte über die Parameters Collection an die Pass-Through-Abfrage übergeben kannst. Du wirst den kompletten SQL-String der Pass-Through-Abfrage mit den Werten aufbauen müssen.

Ich hatte gelesen (tatsächlich hatte ich ChatGPT gefragt    ), dass das per VBA gehen soll. OK, ich probiere das mal mit dem kompletten SQL-String.

Danke

[PhilS]

OK, Wo genau muss ich das denn eingeben?

Ich kenne mich mit Stored Procedures leider noch nicht gut aus.
Die aktuelle wurde von unserer IT-Abteilung erstellt.

Code [Auswählen] Erweitern

CREATE PROCEDURE [dbo].[sp_Insert_tbl_StD_Data]
      @StD_Job_ID INT,
      @...
   WITH EXECUTE AS ...Gefolgt von OWNER oder einem konkreten Benutzernamen.
Siehe: EXECUTE AS Clause

[markusxy]

@Trombomaniac,
ist das jetzt nur zum Üben?
Welchen Sinn hat eine Prozedur, die nur ein simples Insert Statement ausführt, wenn ansonsten überhaupt keine Aktionen folgen?

Edit: die Rechte habs gesehen.
Also wenn dann eürde ich ado statt dao verwenden.

[PhilS]

Welchen Sinn hat eine Prozedur, die nur ein simples Insert Statement ausführt, wenn ansonsten überhaupt keine Aktionen folgen?

Das hatte Trombomaniac im Einganspost erklärt.
Die SP soll es Benutzern ermöglichen Daten in eine Tabelle zu schreiben, auf die sie keine Schreibrechte haben.

[Trombomaniac]

Hallo zusammen,

ich wollte nur nochmal meine Lösung zeigen, die ich nun verwende und die auch funktioniert.
Ich bin dann doch auf ADODB umgestiegen:

Code [Auswählen] Erweitern

  ' Declare variables for the ADO objects
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
   
    ' Set up the connection to the database
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=MSOLEDBSQL19;Server="YOURSERVER";Database=YOURDB";Integrated Security=SSPI;Use Encryption for Data=False;"

    'Set up the command object to call the stored procedure
    Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
             cmd.CommandType = adCmdStoredProc
             cmd.CommandText = "dbo.sp_Insert_tbl_StD_Data"
   
    'Add the parameters to the command
      With cmd
          .ActiveConnection = cnn
               .CommandText = "dbo.sp_Insert_tbl_StD_Data"
               .CommandType = adCmdStoredProc
            .CommandTimeout = 0
          .Parameters.Append .CreateParameter("@StD_Job_ID", adInteger, adParamInput, , Me.txtJob_ID)
         'more parameter similar...
          .Execute
      End With
Dadurch ist dieses Problem für mich gelöst.
Danke an alle für die Hilfe.
Gruss

[PhilS]

.CommandTimeout = 0

Das solltest du noch ändern, denn sonst kann dort alles unendlich lange hängen bleiben.