How to put a variable into SQL code in access [duplicate]

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)? Read on vba set string variable! Read Bobby Tables to understand why you should not concat sql strings, but use params! — ComputerVersteher
– ComputerVersteher, Commented Sep 11, 2020 at 10:42

Kostas K. · Accepted Answer · 2020-09-11 09:03:50Z

2

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

answered Sep 11, 2020 at 9:03

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2020-09-11 11:27:40Z

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, 2020 at 11:27

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

answered Sep 11, 2020 at 10:41

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

4 Comments

ComputerVersteher Over a year ago

Don't show unexperienced users how to concat sql strings as they will get bitten by injections! And look at the string ;(

Harun24hr Over a year ago

@ComputerVersteher I aggreed but its really hard for beginners to catch advanced queries.

ComputerVersteher Over a year ago

Harder than catching why does "INSERT INTO Machines ([Machine]) VALUES('" & "Machinename containg'Quote" & "')" (e.g "O'neil capacitor") fail?

Erik A Over a year ago

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

Collectives™ on Stack Overflow

How to put a variable into SQL code in access [duplicate]

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related