SQL INSERT INTO USING VBA PROBLEM

Question

I am trying to learn SQL in Access. Currently I have a form with two fields on it: Fname & Lname. When I click button to update the records I want to run sql vba code in a module to update the table (tblDemo) with the values in the form fields. I have been unable to get it to work. Below is the code I have been working with.

"INSERT INTO [tblDemo] ([Fname], [Lname]) " &_

         "VALUES("'" & Me.Fname & "'," & "'" & Me.Fname & "';"

Suggestions are vry much appreciated.

Answer 1

Ken, in answer to your previous question, yes I was getting the Update 1 row msg. Thanks. ScottGem, thanks to you also.

 I included the Me.Requery in the code module and still had a problem. But, I figured out that having the table open when I ran the module didn't display the information until I had closed it and then re-opened it, then I saw that the data was properly inserted into a new row.

Again, thanks to all.

Answer 2

I'd also recommend that you adopt the representation of the literal quotes character by a pair of contiguous quotes characters, as in Vanderghast's second example, rather than by a single quote character:

"INSERT INTO [tblDemo] ([Fname], [Lname]) " &_

         "VALUES(""" & Me.Fname & """,""" & Me.Lname & """)"

Otherwise you'll run into problems with names like mine in its original non-Anglicized form, O'Siridean, and those of other Irish bogtrotters like me.

The closing semi-colon is unnecessary when building an SQL statement as a string expression in VBA.

Answer 3

Thank you all. I have expanded the SQL statement and it has no errors but it does not insert a new row into the table. The first field of the table is an autonumber field. I assumed that inserting data into a new row would generate a new number ind the ID field. Am I wrong in that assumption? Why is the SQL statement not inserting data into the table?

strSQL = "INSERT INTO [tblDemo] ([Fname], [Lname], [Address], [City], [State], [Zip])" & _

" VALUES (""" & Me.Fname & """,""" & Me.Lname & """,""" & Me.Address & """ , """ & Me.City & """ , """ & Me.State & """ , """ & Me.Zip & """)" & ""

 DoCmd.RunSQL strSQL

I do know how to use Recordsets but in this case, trying to better learn SQL is my goal. Thanks again.

Answer 4

Just placing the SQL statement in your VBA module doesn't do anything. You have to issue a command to execute the SQL statement. Both DoCmd.RunSQL and CurrentDB.Execute will do that. The main difference is that CurrentDB.Execute will not display any warning messages. You can suppress the messages using DoCmd.RunSQL by using the DoCmd.SetWarnings method. But I prefer CurrentDB.Execute, its also a little faster.

There was also some errors in your SQL it should be:

"INSERT INTO [tblDemo] ([Fname], [Lname]) " & _

         "VALUES('" & Me.Fname & "', '" Me.Fname & "');"

You need a space between the ampersand and underscore when you use do a line continuation. You can simplify identifying a text string by concatenating in an apostrophe ('). And finally you needed a closing parentheses.

Answer 5

DoCmd.RunSQL " INSERT INTO tblDemo(Fname, Lname) VALUES( FORMS!formNameHere!Fname, FORMS!formNameHere!Lname) "

or

Dim str As String : str= " INSERT INTO tblDemo(Fname, Lname) VALUES( """ & Me.Fname & """, """ & Me.Lname & """)"

' Debug.Print str   ' to see the string that you are ready to use fr debugging purpose

CurrentDb.Execute  str, dbFailOnError