Vba Variablen in SQL-Abfrage

[Hpseel]

Hallo,
In einem VBA Code zu einem Form Control möchte ich in einem Sql Kommando den Befehl:
instr(table.feld1, my_variable) = 1
verwenden.

Er kann aber my_variable nicht auflösen, obwohl sie im Programm deklariert ist und ein String-Text zugewiesen wurde.

Wer weiss eine Lösung?

Vielen Dank HPS

[PhilS]

Schau dir Mal mein SQL-String Tutorial an.
https://codekabinett.com/rdumps.php?Lang=1&targetDoc=vba-sql-string-tutorial

Darin findest du, zumindest indirekt, die Antwort auf diese Frage. Viel wichtiger aber, auch die Grundlagen dazu.

[FredFred]

Dein SQL String weiß nichts von der deklarierten Variable.
Zur weiteren Klärung solltest du mindestens auch deinen SQL-String zeigen, den du gebastelt hast.

[FredFred]

Dein SQL String weiß nichts von der deklarierten Variable.
Zur weiteren Klärung solltest du mindestens auch deinen SQL-String zeigen, den du gebastelt hast.
Hier mal ein Code, so wie ich deine Problemstellung verstanden habe.

Code [Auswählen] Erweitern

Public Sub test()
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("A1")
qdf.Parameters(0) = "k"
Set rs = qdf.OpenRecordset

MsgBox rs(0)
End Sub

'SELECT InStr([Feld1],[my_variable])=1 FROM T1;

[Knobbi38]

Wobei der Code

Code [Auswählen] Erweitern

instr(table.feld1, my_variable) = 1erstmal in einem Select so nicht viel Sinn ergibt. Hier wäre dann schon zu hinterfragen, was eigentlich gewünscht wird.

Knobbi38

[Hpseel]

Hier das Code-Beispiel:

Dim Str_Ort as string
Dim SQLStr as string

'Ziel: Es sollen nur Datensätze mit "Passau" oder Anfangsteile dieses Strings eingefügt werden.

Str_Ort = "Passau"  'alternativ Str_Ort = "Pa"

SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, STR_Ort) = 1;"

funktioniert nicht, ebenso nicht:

SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, " & STR_Ort & ") = 1;"


Letzteres sollte laut Antwort #1 eigentlich funktionieren...

VG HPS

[MartinHan]

Hi,

ich habe mal Chatgpt bemüht, die Antwort find ich gut erklärt:

In Access-SQL (also Jet/ACE SQL) funktioniert InStr() zwar, aber nur mit konkreten Werten oder Strings, nicht direkt mit VBA-Variablen innerhalb des SQL-Textes.

Wenn du schreibst:

SQLstr = "... where instr(KOST.ORT, STR_Ort) = 1;"

→ Dann denkt Access, dass STR_Ort eine Feldname ist, nicht deine VBA-Variable.
Daher kommt kein Treffer.

Du musst den Wert der VBA-Variable als Textliteral in den SQL-String einsetzen — also in einfache Hochkommas ' ' einbetten.

So geht's richtig:

Dim Str_Ort As String
Dim SQLstr As String

Str_Ort = "Passau"  ' oder "Pa"

SQLstr = "INSERT INTO KOST_ERW " & _
         "SELECT KOST.* FROM KOST " & _
         "WHERE InStr(KOST.ORT, '" & Str_Ort & "') = 1;

Falls "Passau" immer an Anfang steht, könnte man noch einfacher mit "Like" arbeiten.

Martin

[Knobbi38]

Hallo,

anstatt mit Like oder Instr() = 1 könnte man gleich einfach Left() verwenden.

Stellt sich nur die Frage, wofür so eine Kopieraktion notwendig ist und warum die Tabelle nicht normalisiert ist, denn dann wäre so eine Krücke nicht notwendig.

Knobbi38

[PhilS]

SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, " & STR_Ort & ") = 1;"


Letzteres sollte laut Antwort #1 eigentlich funktionieren...

Du hast ein Detail übersehen. Zeichenfolgen müssen in Begrenzer eingeschlossen werden.
D.h.:

Code [Auswählen] Erweitern

SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, '" & STR_Ort & "') = 1;"Vor und hinter den ", die den String in VBA begrenzen, ist jeweils noch ein ', um den verketteten Text-Wert in dem resultierenden SQL-String zu begrenzen.

Ich würde hier auf das INSTR verzichten und stattdessen LIKE verwenden, weil das unmittelbar Teil des SQL-Standards ist und keine externe Funktion benötigt.

Code [Auswählen] Erweitern

SQLstr = "insert into KOST_ERW Select KOST.* from KOST where KOST.ORT LIKE '*" & STR_Ort & "*';"

anstatt mit Like oder Instr() = 1 könnte man gleich einfach Left() verwenden.

Reines SQL (LIKE) ohne Funktionsaufruf (Left oder Instr) dürfte effizienter sein.

[Hpseel]

Hallo. vielen Dank für die Tipps!

>> "Like" liefert mir zu viele Ergebnisse, wenn nur wenige Buchstaben als Suchstring eingegeben werden.

Die anderen Vorschläge haben bei mir (Access 2016) nicht funktioniert.

Es wird wohl schon so sein, dass das VBA Programm den SQL-String an den Parser der Jet-Engine übergibt und dieser den Variablenbezug nicht auflösen kann ...

Ich habe mich gefragt, was er denn auflösen kann und bin auf eine Lösung mit TempVars gekommen:

Im Code:
  TempVars.Add "Ort", my_Var     ' also Zuweisung meiner Variablen an eine TempVar

  ...

  SQLStr = " Insert ......where instr(KOST.Ort, TempVars![Ort] = 1;"

Dieser "Umweg" über TempVars funktioniert und müsste auch bei anderen eingebauten Funktionen gehen.

Vielen Dank nochmal für die Tipps und weiter frohes Schaffen ! (Programmieren !)

HPS

[Knobbi38]

@Hpseel

Deiner Argumentation mit Like kann ich nicht folgen. Instr() liefert doch exakt die gleiche Anzahl DS - dann doch besser Like().

"TempVars.Add "Ort", my_Var     ' also Zuweisung meiner Variablen an eine TempVar"
Damit wird der Tempvars Auflistung eine neues Tempvar-Objekt zugewiesen. Bei einer wiederholten Zuweisung wird zwar kein Fehler gemeldet, aber eigentlich wird für eine Zuweisung die Value Eigenschaft verwendet. Das wäre dann konsequent und besser lesbar.

Unabhängig davon sollten Eingaben nicht direkt ungeprüft verwendet werden. Besser ist es, dafür eine Parameterabfrage mit Like zu nutzen.

Knobbi38

[Hpseel]

Hallo Knobbi,
du hast sicher recht, LIKE entspricht mehr dem SQL Gedanken.
Ich bin wohl nach mehr als 30 Jahren Excel VBA Programmierung noch recht mit der "instr" Funktion verwachsen ...;-) Vermutlich habe ich sie einige tausend mal verwendet...

Einen kleinen Unterschied gibt es wohl: Bei LIKE muss noch der * hinten drangesetzt werden, bei instr genügt es, einfach die Buchstaben zu nehmen, die ab Position 1 erscheinen sollen, oder die bei instr(...) <> 0 irgendwo im Prüfstring vorkommen.

Meine ursprüngliche Frage "VBA Variablen im SQL String" ist glaube ich mit dem TempVar Ansatz zufriedenstellend gelöst. Du hast recht, wenn die Prozedur mehrfach durchlaufen wird, sollte man am Sub-Ende die TempVariable wieder löschen.
VG HPS

[Knobbi38]

Hier mal ein Lösungsvorschlag mit einer Parameterquery:

SQL für die Parameterquery "qryAppend":

Code [Auswählen] Erweitern

PARAMETERS pPattern Text ( 255 );
INSERT INTO tblDataNeu ( Vorname, Nachname )
SELECT Vorname, Nachname
FROM tblData
WHERE Nachname Like [pPattern];

und dann der VBA Code zum aufrufen:

Code [Auswählen] Erweitern

Sub AppendRecords()
  Dim qdf As QueryDef
 
  Set qdf = DBEngine(0)(0).QueryDefs("qryAppend")
  qdf.Parameters("pPattern") = "A*"
  qdf.Execute dbFailOnError
 
  Debug.Print qdf.RecordsAffected & " Datensätze angefügt."
 
  qdf.Close : Set qdf = Nothing
End Sub

Knobbi38

[Hpseel]

Prima Knobbi!
Habe es ausprobiert und es funktioniert.

Dann gibt es also (mindestens) 2 Lösungsansätze!

VG HPS

[Beaker s.a.]

Dann gibt es also (mindestens) 2 Lösungsansätze!

Ich kenne noch einen dritten, - öffentliche Property/Function statt TempVar.