Insert Into SQL Statement

INSERT INTO SQL Statement

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"

WWW3 – https://www.w3schools.com/sql/sql_insert.asp

Extract:-

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"

Heading HERE...

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)

…         ..         ..            ..        ..          ..           ..     ..  ..         … 

… …