PDA

Vollständige Version anzeigen : Zugriff von Access auf SQL-Server über Stored Procedures und Jobs


Geronimo2002
06.07.2007, 15:56
Hier (wahrscheinlich) die Lösung:

Also -so lautete die Empfehlung eines Kollegen- starte aus Access heraus eine Stored Procedure (SP=Gespeicherte Prozedur) auf dem SQL-Server, die ihrerseits wieder einen Job startet, der die Auswertung durchzieht und das Ergebnis in eine neue Tabelle schreibt. Von dort holst du das Ergebnis per ADO-Command- und Recordset-Objekt wieder nach Access (Tabelle, Liste, Kombifeld etc.).

Um die SP aus Access heraus anzusprechen, könntest du folgenden Code benutzen:
Code:
Option Compare Database
Option Explicit

Public oParam(4)
'--------------------------------------------------------------------
' Stored Procedures auf SQL-Server ausführen
' Übergeben werden bis zu 21 Parameter:
' -strStProcName = Name der gespeicherten Prozedur
' -ParamXX/ParamXXTyp = Name/Datentyp des Paramters
' -ParamXXValue/ParamXXInOut = Wert/Art des Parameters
' Die Integer-Variablen erhalten den numerischen ADO-Wert aus
' -"DateTypeEnum" bzw.
' -"ParameterDirectionEnum"
' Soweit vorhanden werden Output-Parameter in öffentliche Variablen
' vom Typ Variant gestellt. Diese werden dann abgefragt und geleert
'
Public Function StProc_Starten( _
ByVal strStProcName As String, Optional ByVal Param01 As String, _
Optional ByVal Param01Typ As Integer, _
Optional ByVal Param01Len As Integer, _
Optional ByVal Param01InOut As Integer, Optional ByVal Param01Value, _
Optional ByVal Param02 As String, Optional ByVal Param02Typ As Integer, _
Optional ByVal Param02Len As Integer, _
Optional ByVal Param02InOut As Integer, Optional ByVal Param02Value, _
Optional ByVal Param03 As String, Optional ByVal Param03Typ As Integer, _
Optional ByVal Param03Len As Integer, _
Optional ByVal Param03InOut As Integer, Optional ByVal Param03Value, _
Optional ByVal Param04 As String, Optional ByVal Param04Typ As Integer, _
Optional ByVal Param04Len As Integer, _
Optional ByVal Param04InOut As Integer, Optional ByVal Param04Value, _
Optional ByVal Param05 As String, Optional ByVal Param05Typ As Integer, _
Optional ByVal Param05Len As Integer, _
Optional ByVal Param05InOut As Integer, _
Optional ByVal Param05Value) As Boolean
On Error GoTo StProc_Starten_Err
Dim strServer, strDatabase, strSQL, strConnection
Dim cmd As ADODB.Command, tmpParam As ADODB.Parameter
Dim Ausdruck As String

strServer = "<Dein Servername>"
strDatabase = "<Dein Datenbankname>"
' Username und Passwort kannst du entweder hier reinschreiben
' oder wie ich in öffentliche Variablen stellen.
strConnection = "Provider=SQLOLEDB.1;Password=" & strPasswort & _
";Persist Security Info=True;User ID=" & strUser & _
";Initial Catalog=" & strDatabase & _
";Data Source=" & strServer & ";"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = strConnection
cmd.CommandText = strStProcName
cmd.CommandType = adCmdStoredProc
If Len(Param01) > 0 Then
Set tmpParam = cmd.CreateParameter(Param01, Param01Typ, _
Param01InOut, Param01Len, _
Param01Value)
cmd.Parameters.Append tmpParam
If Len(Param02) > 0 Then
Set tmpParam = cmd.CreateParameter(Param02, Param02Typ, _
Param02InOut, Param02Len, _
Param02Value)
cmd.Parameters.Append tmpParam
If Len(Param03) > 0 Then
Set tmpParam = cmd.CreateParameter(Param03, Param03Typ, _
Param03InOut, Param03Len, _
Param03Value)
cmd.Parameters.Append tmpParam
If Len(Param04) > 0 Then
Set tmpParam = cmd.CreateParameter(Param04, Param04Typ, _
Param04InOut, _
Param04Len, _
Param04Value)
cmd.Parameters.Append tmpParam
If Len(Param05) > 0 Then
Set tmpParam = cmd.CreateParameter(Param05, _
Param05Typ, _
Param05InOut, _
Param05Len, _
Param05Value)
cmd.Parameters.Append tmpParam
End If
End If
End If
End If
End If
' Kommando ausführen
cmd.Execute
If Len(Param01) > 0 Then
If Param01InOut = 2 Then
oParam(0) = cmd.Parameters(Param01).Value
End If
End If
If Len(Param02) > 0 Then
If Param02InOut = 2 Then
oParam(1) = cmd.Parameters(Param02).Value
End If
End If
If Len(Param03) > 0 Then
If Param03InOut = 2 Then
oParam(2) = cmd.Parameters(Param03).Value
End If
End If
If Len(Param04) > 0 Then
If Param04InOut = 2 Then
oParam(3) = cmd.Parameters(Param04).Value
End If
End If
If Len(Param05) > 0 Then
If Param05InOut = 2 Then
oParam(4) = cmd.Parameters(Param05).Value
End If
End If
StProc_Starten = True
Set cmd = Nothing
Exit Function
StProc_Starten_Err:
MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description, , "Fehler"
StProc_Starten = False
End Function
Die Funktion läuft auch ohne Parameter. Wichtig ist (wie erläutert), dass du die numerischen Werte für DateTypeEnum (z. B. Integer = 3) bzw. ParameterDirectionEnum (1=InputParameter, 2=Outputparameter) übergibst. Die Deklaration der öffentlichen Variable oParam ist ebenfalls unabdingbar.

Du kannst nicht mehr als 5 Parameter übergeben. Denk dran, dass die Outputparameter erst bei der oParam-Dimension beginnen, bie der die Input-Parameter aufhören.

Beispiel für einen Aufruf:
Code:
...retwert = StProc_Starten("sp_rsa_DMPnbEdJf", "@bisDatum", 133, , 1, Me.txtDatumBis)...
Hier wird der SP sp_rsa_DMPnbEdJf der Inputparameter @bisDatum als Datum (133) ohne Längenangabe mit dem Datumswert aus einer Form übergeben.

In der SP wird dann nur noch die job_id aus der Tabelle sysjobs der Datenbank "msdb" per T-SQL ermittelt, der Parameter in einer Tabelle zwischengelagert und schließlich der Job gestartet (sp_start_job @job_id = <Ermittelte Job-ID>). Dieser holt den Parameter-Wert wieder aus der Tabelle und lässt die SQL laufen.

Das Ergebnis schreibt er in eine Ergebnistabelle, die dann wiederum mit einem ähnlichen Code wie diesem abgerufen werden kann.
Code:
Public Function StProc_SavedTables() As Boolean
Dim strServer, strDatabase, strSQL, strConnection
Dim rs1 As ADODB.Recordset, cmd1 As ADODB.Command
Dim rs2 As ADODB.Recordset, cmd2 As ADODB.Command
Dim tmpParam2 As ADODB.Parameter
Dim rsInt As ADODB.Recordset
Dim zl As Long
Dim varString As Variant
Dim arrRecS() As Variant
Dim i As Long, j As Long

StProc_SavedTables = True
zl = 0
strServer = "<Dein Servername>"
strDatabase = "<Dein Datenbankname>"
' Für Benutzername und Paswort s. oben
strConnection = "Provider=SQLOLEDB.1;Password=" & strPasswort & _
";Persist Security Info=True;User ID=" & strUser & _
";Initial Catalog=" & strDatabase & _
";Data Source=" & strServer & ";"
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = strConnection
Set cmd2 = CreateObject("ADODB.Command")
cmd2.ActiveConnection = strConnection
cmd1.CommandText = "sp_rsa_saved_tables_1"
cmd1.CommandType = adCmdStoredProc
cmd2.CommandText = "sp_rsa_saved_tables_2"
cmd2.CommandType = adCmdStoredProc
Set rs1 = cmd1.Execute
If rs1.EOF Then
StProc_SavedTables = False
Exit Function
End If
Set rsInt = CreateObject("ADODB.Recordset")
rsInt.ActiveConnection = CurrentProject.Connection
rsInt.Source = "SELECT * FROM tblFiles"
rsInt.LockType = adLockOptimistic
rsInt.Open
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblFiles"
DoCmd.SetWarnings True
Do While Not rs1.EOF
Set tmpParam2 = cmd2.CreateParameter("@bisDatum", adVarChar, _
adParamInput, 100, _
rs1("TabellenName"))
cmd2.Parameters.Append tmpParam2
Set rs2 = cmd2.Execute
If rs2.EOF Then
StProc_SavedTables = False
Exit Function
End If
rsInt.AddNew
rsInt!FileName = rs2("Dateiname")
rsInt!FileErstDat = rs2("ErstDt")
rsInt!FileArt = rs2("Dateiart")
rsInt!FileKS = rs2("Kasse")
rsInt!FileSA = rs2("sa")
rsInt!FileZR = Trim(rs2("Zeitraum"))
rsInt!FileSelDat = rs2("Selektionsdatum")
rsInt!FileStatus = "Vorbereitung"
rsInt.Update
cmd2.Parameters.Delete 0
rs1.MoveNext
Loop
rsInt.Close
rs1.Close
rs2.Close
End Function
Ich lass das jetzt einfach mal so stehen. Du müsstest natürlich deine eigenen Namen für SP, Parameter, Felder und Tabellen verwenden.

Bei Fragen ...