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
– acr_scout, Commented May 6, 2018 at 0:08

Erik A · Accepted Answer · 2018-05-03 12:46:50Z

3

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.

edited May 3, 2018 at 12:46

answered May 3, 2018 at 12:29

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

1 Comment

acr_scout Over a year ago

Suggest CurrentDb.Execute sql, dbFailOnError so you can trap any errors at runtime

Harun24hr · Accepted Answer · 2018-05-03 13:27:44Z

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 3, 2018 at 13:27

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

6 Comments

Vityata Over a year ago

Have you heard what has happened about an year ago with the Bank of Phillipines Islands?

Harun24hr Over a year ago

No. Can you please explain?

Harun24hr Over a year ago

Then what happened? If it is not right way then what is the best practice? I will change the codes as best practice.

Vityata Over a year ago

Well, they managed to fix the problem quite fast. The code is quite opened to a classic SQL Injection - see this xkcd.com/327

Harun24hr Over a year ago

Very risky! So can you please give some links on how write sql statement securely in access vba?

|

Collectives™ on Stack Overflow

Running an SQL string in VBA?

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related