INSERT INTO SQL Statement
I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. And the syntax, the usual suspect, is a minefield, with various different ways of constructing it.
I’m not going to go into the details of the syntax here, I’m going to avoid the “Syntax” issue, by not discussing it and also by removing much of it from the SQL Statement.
When faced with any challenging piece of code, an excellent strategy is to “Divide & Conquer”.
Dividing & Conquering "INSERT INTO"
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
is actually one of the most difficult to get right.
The SQL INSERT INTO Statement is an excellent example of how this technique can be applied effectively. You should make using this technique a fundamental tool of your MS Access toolbox so to speak.
Instead of trying to cram all of the text, the variables, into one long SQL Statement, separate them out into a function like this:-
Private Function fAddRec(txtFld1 As String, txtFld2 As String, txtFld3 As String, txtFld4 As Integer) 'Add Text Delimiters - Chr(34) = " txtFld1 = Chr(34) & txtFld1 & Chr(34) txtFld2 = Chr(34) & txtFld2 & Chr(34) 'Add Date Delimiters - Chr(35) = # txtFld3 = Chr(35) & txtFld3 & Chr(35) Dim strSQL0 As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "INSERT INTO tblSimple_INSERT_INTO_SQL(fld1, fld2, fld3, fld4) " strSQL2 = "Values(" & txtFld1 & ", " & txtFld2 & ", " & txtFld3 & ", " & txtFld4 & ")" strSQL0 = strSQL1 & strSQL2 CurrentDb.Execute strSQL0 End Function 'fAddRec
YouTube Walkthrough of "INSERT INTO"
Video 1 (03:33)
I have provided this function in a sample database which you can download for free:- (SEE BUTTON BELOW) using this coupon code “XXXXXXXX”
Video 1 (03:33)
… .. .. .. .. .. .. .. .. …