How to use variables in SQL insert statement directly

Question

I have a SQL statement:

Dim Xdb As DAO.Database
Set Xdb = CurrentDb
Dim v1 As Integer
Dim v2 As String
With Me
    v1 = 1
    v2 = Trim(.txt2)
End With
Dim Sql As String
Sql = "Insert Into TblCustomers" _
& " (ID,FName)" _
& " Values " _
& " (1,'"& v2 &"')"

I want to use this variable "V2" in the value into SQL statement but without using the quotes symbol.

Like:

Sql = "Insert Into TblCustomers" _
& " (ID,FName)" _
& " Values " _
& " (1,v2)"

How can I set the "V2" variable correctly to be like that?

What are you opening to achieve? The first way is the way to do it. If you insert it directly in a string, then it's a string not a variable — AAA, Jun 29 '19 at 14:48
`Sql = "Insert Into TblCustomers" & " (ID,FName)" & " Values " & " (1," & v2 & ")"` — Mikku, Jun 29 '19 at 14:50
@Mikku I don't want to use "& V2 &" I want to use the variable as it is just V2 — , Jun 29 '19 at 14:54
@AAA actually I didn't understand you well, could you please help me and explain more — , Jun 29 '19 at 15:07
`Sql = "Insert Into TblCustomers" & " (ID,FName)" & " Values " & " (1, v2 )"` .. Isn't this working ? — Mikku, Jun 29 '19 at 15:17
@M.J .. Then you need to explain in a better way what you want to do. Maybe a screenshot of what you want to see might help. — Mikku, Jun 29 '19 at 15:24
You want to use `v2` as a placeholder in the SQL string. So the SQL needs to know it. That means that you have to introduce it to the SQL as a parameter. See here: https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access/ — AHeyne, Jun 29 '19 at 15:29
Possible duplicate of [Trying to use VBA variable in Access UPDATE Statement](https://stackoverflow.com/questions/38329361/trying-to-use-vba-variable-in-access-update-statement) — C Perkins, Jun 30 '19 at 04:01

score 2 · Accepted Answer · answered Jun 29 '19 at 14:48

You need to use something like this:

Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command

'Create the query
cmdSQLInsert.CommandText = "Insert Into TblCustomers(ID, FName) Values(?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True

'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adInt
    .Value = 1
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 2 (for FName)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "FName"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "FirstNameValue"
End With
cmdSQLInsert.Parameters.Append gParam

'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection

'Execute the command
cmdSQLInsert.Execute

I don't want to use this method, I just wanted to know how I can assign the variable to work. — , Jun 29 '19 at 15:17
@M.J oh well his method shows how to assign the variable to work. — omegastripes, Jun 29 '19 at 16:04

score 0 · Answer 2 · answered Jun 30 '19 at 04:39

This question really is a duplicate of other questions (here and here for example), since many newcomers to Access both expect and want to be able to reference VBA variables directly from within SQL statements. It is essentially the same issue no matter if it's an INSERT, UPDATE, SELECT, etc.

Almost all good answers eventually lead to parameterized QueryDefs or ADO command, and I agree that this is the best-practice approach for various reasons. But very few comments and answers address the issue directly, so here's a different sort of answer ...

Access SQL cannot directly reference VBA module variables. However, there a couple alternatives that can work, but they have their own drawbacks.

Access SQL can directly reference public functions declared in VBA modules. Because a VBA functions can essentially access any other variable or object and return a value, it can certainly return a variable's value that is otherwise not directly accessible to SQL.
- The most critical issue to realize here is that context in which such a function is called must be assured before the query is executed. If a query which references such a function is ever called "out of order" (when the variable is not properly initialized), then it will of course result in expected behavior and/or invalid data.

'* From within a VBA module *
Private moduleVariable As String

Public Sub MySub()
  moduleVariable
End Sub

Public Function GetModuleVariable() As String
  GetModuleVariable = moduleVariable
End Function

'--- Example SQL statement ---
'INSERT INTO TblCustomers (ID,FName) VALUES (1, GetModuleVariable())

A special object that can be accessed is called TempVars (docs here). It really was designed to be used with macros, but Access exposes it as an object directly accessible from within an SQL statement, so it is technically allowed.
- The biggest drawback is that the values are stored as VBA variants, and SQL does not always interpret them correctly. Using explicit conversion functions can resolve this. The example demonstrates this by wrapping the TempVars reference in CDbl().

'* From within a VBA module *
Public Sub MySub()
  TempVars.Add "myVariable", 123.45
End Sub

'--- Example SQL statement ---
'INSERT INTO products (ID, Amount) VALUES (100, CDbl(TempVars!myVariable))

score 0 · Answer 3 · answered Jun 25 '20 at 17:11

I just started using Access, and found the accepted answer to be very helpful. Thank you, @Pablo Santa Cruz.

I was looking for an explanation that would help eliminate SQL injection in my code. When I implemented this code, I ran into problems with unset values in the parameters. I changed my references to use indexes, Parameters(0), etc., and then had datatype mismatches, as I had declared all of my parameters as strings.

When I traced the process, I saw that when assigning the INSERT statement to cmd.CommandText, parameters were automatically added, matching the meta data from the database. So, parameters 2 and 3 had to be changed to match the parameters declared types, of integer and datetime, respectively.

Kinda cool, maybe a little creepy. Here's my example:

' Create table MyTable( sCol Varchar(255), nCol Integer, dCol DateTime )

Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB.1;Trusted_Connection=yes;Server=Thresher;Database=MyDB;"
conn.Open

Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO MyTable( sCol, nCol, dCol ) VALUES (?, ?, ?)"

cmd.Parameters(0).Value = "One"
cmd.Parameters(1).Value = 1
cmd.Parameters(2).Value = #1/1/2001#
cmd.Execute

conn.Close

How to use variables in SQL insert statement directly

3 Answers3