Running an SQL string in VBA?

Question

Iv created the SQL string sql in ms access vba but when it runs it prints the string in the debug window but doesn't actually run the string to add a record to the table like I want it to.

Public Sub EmpoyeesTable_Click()

    Dim sql As String

    sql = "INSERT INTO Employees " & _
    "VALUES " & "(1, 'James', 'Dan', 'n6 indro Rd', 0943747, 30.24);"

    Debug.Print sql
End Sub

Ultimately I want to use SQL strings to take input from a form when submit is clicked and add it to a table? Is this even the right approach?

How about building a static parameter query and then use it to add your records? — acr_scout, May 06 '18 at 00:08

Erik A · Answer 1 · 2018-05-03T12:46:50.190

There are many ways to run SQL strings in VBA. Each have their own advantages, and disadvantages. The most common ones are:

DoCmd.RunSQL sql

Runs the SQL just as it would if you executed a query. Popup will occur when you add, delete or modify records. You can use UDFs and form parameters

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Disables warnings, then runs the SQL like in the previous way, then sets warnings back on.

CurrentDb.Execute sql

Executes the SQL over a DAO connection to the current database. You can't use UDFs and form parameters here. No warnings are shown. It just executes the SQL.

CurrentProject.Connection.Execute sql

Executes the SQL over an ADO connection to the current database. Very similar to the DAO connection, but there are subtle differences. For example, you can execute DDL statements that contain the Decimal data type, and set Check constraints in this way, while both are not allowed in any of the other ways.

You can read about using parameters with these different ways here. That's strongly recommended if you are going to insert values that aren't constant, to avoid bugs and SQL injection.

Suggest `CurrentDb.Execute sql, dbFailOnError` so you can trap any errors at runtime — acr_scout, May 06 '18 at 00:01

score 0 · Answer 2 · answered May 03 '18 at 13:27

0

If you think simply then just change your Debug.Print sql to DoCmd.RunSQL (sql)

Private Sub Command0_Click()
Dim sql As String

    sql = "INSERT INTO Employees " & _
    "VALUES " & "(1, 'James', 'Dan', 'n6 indro Rd', 0943747, 30.24)"

    DoCmd.RunSQL (sql)
End Sub

If you want take values from form then refer each value from form control like text box. See the below codes.

Private Sub Command0_Click()
Dim sql As String

    sql = "INSERT INTO Employees VALUES (" & _
    "'" & Me.Text1 & "'," & _
    "'" & Me.Text2 & "'," & _
    "'" & Me.Text3 & "'," & _
    "'" & Me.Text4 & "'," & _
    "'" & Me.Text5 & "'," & _
    "'" & Me.Text6 & "');"

    DoCmd.RunSQL (sql)
End Sub

If the field value is number type the you can remove singe quote (') from code for those field.

answered May 03 '18 at 13:27

Harun24hr

30,391
4
21
36

Have you heard what has happened about an year ago with the Bank of Phillipines Islands? – Vityata May 03 '18 at 14:14
No. Can you please explain? – Harun24hr May 03 '18 at 14:16
Then what happened? If it is not right way then what is the best practice? I will change the codes as best practice. – Harun24hr May 03 '18 at 14:20
Well, they managed to fix the problem quite fast. The code is quite opened to a classic SQL Injection - see this https://xkcd.com/327/ – Vityata May 03 '18 at 14:22
1

Very risky! So can you please give some links on how write sql statement securely in access vba? – Harun24hr May 03 '18 at 14:36
Just google "sql injection vba". The first 2 pages should be quite ok. – Vityata May 03 '18 at 14:37

Running an SQL string in VBA?

2 Answers2