Excel->VBA-> SQL-> AS400

[Spezlerin]

Habe eine kleine Abfrage in VBA erstellt und möchte Daten aus der AS400 erhalten und an die AS400 senden.
Funktioniert auch aber die Schnelligkeit???!!!
so benötige ich für ein Update von 4 Daten in einer Tabelle etwa 1 Minute. Das macht bei etwa 100 neuen Daten....
Vieleicht hat ja jemand eine Idee?

Private Sub CommandButton1_Click() 'Ändern (Update Datum)
Dim Cnn As ADODB.Recordset
Dim strSQL, temp As String
Dim nummer As Long
Dim intCol As Long
intCol = Range("D65536").End(xlUp).Row
ProgressBar1.Min = 1
ProgressBar1.Max = intCol
ProgressBar1.Value = 1
Set Cnn = New ADODB.Recordset
For n = 2 To intCol
nummer = Cells(n, 4)
temp = Cells(n, 5)
strSQL = "UPDATE TMT.AUH A SET A.TEF = '" & temp & "' WHERE A.NL = 11 and A.SN= '" & nummer & "' "
Cnn.Open strSQL, "Provider=A400;Data Source=192.168.4.100;User ID='ich';Password='ich';"
ProgressBar1.Value = n
ActiveWorkbook.RefreshAll
Next
End Sub

[andreaspr@aon.at]

Ich bin jetzt zwar kein VBA Profi, aber ich glaube du machst zuviel innerhalb der Schleife.

*) Das Open gehört auf jeden Fall außerhalb der Schleife
(Cnn.Open strSQL, "Provider=A400;Data Source=192.168.4.100;User ID='ich'; )

*) Muss ActiveWorkbook.RefreshAll innerhalb der Schleife sein?

lg Andreas

[Spezlerin]

Ich dachte das "open.." beginnt den Abruf(Update) erst. Der "strSQL" macht doch alleine keinen Abruf !?

und ActiveWorkbook.RefreshAll ist für den ProgressBar zur Aktualisierung nach jedem
Datensatz also nach jedem Schleifendurchlauf da.

[Fuerchau]

Für ADO ist ein anderes Vorgehen erforderlich:

Eine Verbindung benötigt man nur 1 mal:

dim MyCnn as new ADODB.Connection
MyCnn.ConnectionString = "Provider=A400;Data Source=192.168.4.100;User ID='ich';"
MyCnn.Open

Für Kommandos benötigt man ein Command:

Dim MyCmd as new ADODB.Command
MyCmd.Commandtext = "UPDATE TMT.AUH A SET A.TEF = ? WHERE A.NL = 11 and A.SN= ?"
set MyCmd.ActiveConnection=MyCnn

Man beachte die "?", die ergeben dann Parameter:

dim xRecords as long

For n = 2 To intCol
MyCmd(1) = Cells(n, 4)
MyCmd(0) = Cells(n, 5)
MyCmd.Execute, xRecords, adExecuteNoRecords
if xRecords>0 then
' Update erfolgreich
endif
next

MyCnn.Close

Den Laufbalken würde ich mir sparen bzw. nur alle 100 Sätze mal refreshen.

Ansonsten empfehle ich gerne mein Uplaod/400 für Excel.

[Spezlerin]

Dim MyCmd as new ADODB.Command
MyCmd.Commandtext = "UPDATE TMT.AUH A SET A.TEF = '" & temp1 & "' WHERE A.NL = 11 and A.SN= '" & temp2 & "'"
set MyCmd.ActiveConnection=MyCnn

Man beachte die "?", die ergeben dann Parameter:

dim xRecords as long

For n = 2 To intCol
temp1 = Cells(n, 4)
temp2 = Cells(n, 5)
MyCmd.Execute, xRecords, adExecuteNoRecords
if xRecords>0 then
' Update erfolgreich
endif
next

Ich habe die Variablen hier eingesetzt , so macht er leider kein Update. Der Groschen ist leider noch nicht gefallen.

[Fuerchau]

MyCmd.Commandtext = "UPDATE TMT.AUH A SET A.TEF = ? WHERE A.NL = 11 and A.SN= ?"

Parameter werden bei AS/400-Kommandos automatisch der Parameters-Auflistung eines Command's hinzugefügt (man kann das auch selber).
Der Zugriff erfolgt über:

Langform:
MyCmd.Parameters(n).Value = Wert

Kurzform:
MyCmd(n) = Wert

Die "?" werden von 0 beginnend durchnummeriert.
MyCmd(0) = 1. ?
MyCmd(1) = 2. ?
usw.

Deine Variablen temp1 und temp2 werden nicht mehr benötigt, siehe mein Beispiel.
Im Debugger kannst du dir die Inhalte dann auch ansehen.

[Spezlerin]

AHA!!!
Habe ich bisher nicht gewusst. Aber verstanden. Danke.

Trotzdem Fehlermeldung:
"Laufzeifehler: SQL0363: Wert für erweiteret Anzeigevariable für Wert 2 nicht gültig...nicht innerhalb des Bereichs.... muss zwischen 0 und 7 liegen???"

Der meint doch Parameter 2 oder?
und der ist doch (1)!???

[Fuerchau]

"Anzeigevariable" werden auch als NULL-Anzeiger verstanden.
Standarmäßig sind NULL-Werte für Parameter nicht erlaubt.

Dein Problem sind die Zuweisungen an die Parameter.
Der Wert muss genau dem Typ und der Länge des Zielfeldes entsprechen und ist daher ggf. zu konvertieren (CInt, CDbl, CStr, usw).

Angenommen, dein SN in der Datei AUH ist numerisch, dann weise per

MyCmd(1) = CLng(Cells(n, 5))

zu.
Ich nehme mal an, dass du ggf. leere Zellen hast. In diesem Fall musst du einen Default setzen oder die Excel-Zeile ignorieren.

Analog kannst du alle VBA-Konvertierungen verwenden um das Zielformat anzupassen.

Bei Zeichenvariablen musst du ggf. per Left(Cell(n, m), Lg) abschneiden.

Übrigens kann man sich im Direktfenster die Parameter, Typ und Länge auch ansehen.