Solved

error in insert into statement

Posted on 2008-06-19
8
634 Views
Last Modified: 2013-12-25
I am using an excel form to insert data into a db.mdb
When I click to record the form into the db I keep getting "syntax error in INSERT INTO statement".  I have gone through the statement and cannot see the error.  Could somebody help with what I am sure will be very simply for you guys, but obviously not for a bozo like me !!

The code is:

Option Explicit

Const stDB As String = "mypath\db1.mdb"

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & stDB & ";"

Dim cnt As ADODB.Connection
Dim stSQL As String

'Instantiate the ADODB-object.
Set cnt = New ADODB.Connection

stSQL = "INSERT INTO LM1FL500(Time,Date,Machine,Operator,JobType,Fault,Priority,Partsrequired,Eng1,Engwd1,wdtime1,Eng2,Engwd2,wd2time,Eng3,Engwd3,wd3time,WorkComplete) VALUES('" & Format(Now, "HH:MM") & "','" & Format(Now, "MM/DD/YY") & "','" & TextBox9.Value & "','" & ComboBox3.Value & "','" & ComboBox1.Value & "','" & TextBox1.Value & "','" & ComboBox4.Value & "','" & TextBox7.Value & "','None','None','None','None','None','None','None','None','None','No');"
                       
With cnt
    'Open the connection
    .Open stCon
    'Insert the values
    .Execute (stSQL)
    'Close the connection
    .Close
End With

'Release objects from memory
Set cnt = Nothing

MsgBox "Record Added to Dbase", vbOKOnly


Thats it
0
Comment
Question by:sltech8
8 Comments
 
LVL 3

Expert Comment

by:inxni
ID: 21820904
I'm not an expert in excel but is that semicolon I am seeing at the end of your query.
0
 
LVL 16

Expert Comment

by:Robin
ID: 21820914
Maybe some of the fields are too large?

Can you print out the value of stSQL?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 21820930
reserved keywords are Time and Date, you need to use [] around those:
stSQL = "INSERT INTO LM1FL500([Time],[Date],Machine,Operator,JobType,Fault,Priority,Partsrequired,Eng1,Engwd1,wdtime1,Eng2,Engwd2,wd2time,Eng3,Engwd3,wd3time,WorkComplete) VALUES('" & Format(Now, "HH:MM") & "','" & Format(Now, "MM/DD/YY") & "','" & TextBox9.Value & "','" & ComboBox3.Value & "','" & ComboBox1.Value & "','" & TextBox1.Value & "','" & ComboBox4.Value & "','" & TextBox7.Value & "','None','None','None','None','None','None','None','None','None','No');"

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sltech8
ID: 21821013
Well, I tried renaming the date and time fields whilst I was waiting for a reply and that did not fix the problem, however I tried angelIII solution with [ ] fields around date and time and that seems to have got around that problem.  It has created another small problem in that I now get the fault "data type mismatch in criteria expression".
I have now changed all table data types to text except time, date and ID(primary key).  The primary key is an autonumber field, which correct me if I am wrong but i do not need to express the ID field in my query?
With regard to date and time fields, you can see from the code I have expressed the format for them.  On the table side, I have set them as short time and short date data types.

Any ideas?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21821031
for date and time fields, you have to use # instead of ' to delimit the value.
0
 

Author Comment

by:sltech8
ID: 21821046
Actually, I think I just got it.  I had the last column "workcompleted" set to enter a default of no, on the table I set this column as a yes/no column, but I think using a yes/no column it still needs to see a value of zero or one?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21821064
>but I think using a yes/no column it still needs to see a value of zero or one?
yes, that's correct.

another note: for text fields, if any values can have a quote in it, you need to duplicate it for the query, ie:


'" & ComboBox3.Value & "'
would change to:
'" & replace(ComboBox3.Value, "'", "''") & "'

Open in new window

0
 

Author Comment

by:sltech8
ID: 21821102
Thanks for the extra note, I did not know that and will amend my code accordingly.  Thanks for helping with this problem
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question