How to put a variable into SQL code in access

Question

Sorry for my dumb question. I programming in Access (VBA) and I’m trying to send a variable called machine into a sql code and then insert it into a table. But i have no idea how to do it. this is my code for so far:

Private Sub MachineToevoegen_Click()
        Dim SQL As String
        Dim Machine As String
        Machine = Machine_keuze
        SQL = "INSERT INTO Machines ([Machine]) VALUES(Machine)"
        DoCmd.RunSQL SQL
    End Sub

If someone could help me with this it would be great.

Error message (if not add [Option Explicit](http://www.cpearson.com/excel/DeclaringVariables.aspx))? Read on vba set string variable! Read [Bobby Tables](http://www.bobby-tables.com) to understand why you should not concat sql strings, but use params! — ComputerVersteher, Sep 11 '20 at 10:42

score 2 · Accepted Answer · answered Sep 11 '20 at 09:03

Create a query where you pass the Machine as parameter.

For example:

PARAMETERS [prmMachine] Text (255);
INSERT INTO Machines ([Machine])
SELECT [prmMachine] AS _Machine;

Then, call the query in VBA:

With CurrentDb().QueryDefs("YourQueryName")
    .Parameters("[prmMachine]").Value = Machine_keuze 
    .Execute dbFailOnError
End With

score 0 · Answer 2 · edited Sep 11 '20 at 11:27

0

Try below.

Private Sub MachineToevoegen_Click()

    Dim SQL As String
    Dim Machine As String

    Machine = "Machine_keuze"
    SQL = "INSERT INTO Machines ([Machine]) VALUES('" & Machine & "')"
    DoCmd.RunSQL SQL

End Sub

edited Sep 11 '20 at 11:27

Gustav

53,498
7
29
55

answered Sep 11 '20 at 10:41

Harun24hr

30,391
4
21
36

2

Don't show unexperienced users how to concat sql strings as they will get bitten by injections! And look at the string ;( – ComputerVersteher Sep 11 '20 at 10:45
@ComputerVersteher I aggreed but its really hard for beginners to catch advanced queries. – Harun24hr Sep 11 '20 at 10:57
1

Harder than catching why does `"INSERT INTO Machines ([Machine]) VALUES('" & "Machinename containg'Quote" & "')"` (e.g "O'neil capacitor") fail? – ComputerVersteher Sep 11 '20 at 11:12
Saying it's hard is nonsense. Just use a tempvar: `TempVars!Machine = "Machine_keuze"`, and `SQL = "INSERT INTO Machines ([Machine]) SELECT TempVars!Machine"`. 0 added lines, avoids issues with string delimiters so conceptually likely more simple for beginners – Erik A Sep 11 '20 at 11:40

How to put a variable into SQL code in access

2 Answers2