Insert Into SQL Statement

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.

The syntax, the usual suspect, is a minefield, with various different ways of constructing it.

When faced with any challenging piece of code, an excellent strategy is to "Divide & Conquer".

Divide & Conquer the "INSERT INTO"

The "SQL INSERT" is one of the most difficult SQL Statements to get right.

The following exact taken From WWW3 – https://www.w3schools.com/sql/sql_insert.asp

Extract:-

INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

The SQL INSERT INTO Statement is an excellent example of how the "Divide & Conquer" technique can be applied effectively. You should make using this technique a fundamental approach to your MS Access development work.

Instead of trying to cram all of the text, the variables, into one long SQL Statement, separate them out in a function as shown below.

WHY? As you become more proficient at VBA programming you begin to realise that the best way, the most advantageous way, the quickest way to program is to take the counter intuitive approach of dividing your VBA Code up into to the smallest parts possible "Divide & Conquer".

You can do this at many levels, the most basic level is to write separate functions or subroutines. Why? Well, an individual component, the smallest thing you can create is "obvious"… By "OBVIOUS" what I mean is — It can be studied, you can work out what it does "easily"

However developers of all levels have a natural tendency to to make functions bulky, "Clever" as in "Obfuscate". For some reason you believe someone is going to be interested enough in your hard work to steal it. Therefore you make it as difficult as possible for those criminals, (fictitious criminals). There are some cases where this is true, but 90% of the time you are just hiding your VBA Code from yourself!

"YOU" are the poor sod that's going to have to come back and fix it when it goes wrong, so be nice to yourself!

The same goes for an SQL Statement make it "easy" make it "transparent". And, as equally important as making your code transparent you need to follow a pattern, once you've learnt the pattern, then you just "walk it". Your pattern becomes a template. Store the templates somewhere like in the text insert folder in MS Access. When you need the template, download it and within a few minutes you have a new function,  a function that you you already understand in great depth, because it's basically the same one you've used before… 



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 Walk-through of "INSERT INTO"

YouTube Walk-through of "INSERT INTO"

Video 1 (03:33)

The insert INTO SQL Statement can be difficult to master. I demonstrate a methodical approach which simplifies the task. I provided a video by way of explanation — See Video 1 (03:33) and also a sample database you can download from Sellfy Here:- The Notorious INSERT INTO SQL Statement – Nifty Access

The secret is to keep the the SQL Statement as simple as possible by constructing it from individual elements, (separate string variables). Then concatenating these string variables together to form the "Insert INTO SQL Statement". Divide and conquer is the rule, by dividing the potentially complex insert into SQL statement in to separate string variables, you simplify the process, you duplicate the process, in the sense that the process becomes a set of identical steps. This is less prone to error and much easier on the grey matter!

Video 1 (03:33)

Play Video

… …

Formatting an SQL Insert Statement

Formatting an SQL Insert Statement

Video 2 (11:22)

In this video I demonstrate how to turn a complicated SQL Statement into a function. The goal is to remove the references to the Form's text boxes from within the SQL String. The positions held by the text boxes are replaced by variables.

The variables are passed into the function as parameters. This gives you  much more control over the process.

Following this method helps you avoid many of the common mistakes made in building SQL Statements. You also have the ability to examine the contents of the variable and decide how you want to to format the data.  For example, if you happen to pass through a text string containing a  single quote '   like the name "O'Brien" the SQL will not work correctly and will throw an error. It is easy to add add a routine in the function to find and correct any potentially damaging strings.

This video came about in answer to a particular question on Access World Forums (AWF) Runtime Error 3134 

I noticed that the SQL Statement provided by the OP  was very complicated. It would be difficult for me to suss out what's going on in that SQL statement! let alone a beginner! Really, there's no need to get bogged down in the syntax in an SQL Statement. If you follow along with my video and look at the code snippet you will see that you can tackle the problem by breaking things down into smaller parts.

The other thing to do, is to follow a set routine, apply the same routine to the different variables and you have far less chance of making a mistake. I never did find out if my solution worked for the OP as he never deigned to get back to me!

Video 2 (11:22)

Play Video

… …