asked on

error in insert into statement

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

inxni

I'm not an expert in excel but is that semicolon I am seeing at the end of your query.

Robin Uijt

Maybe some of the fields are too large?

Can you print out the value of stSQL?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sltech8

ASKER

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?

Guy Hengel [angelIII / a3]

for date and time fields, you have to use # instead of ' to delimit the value.

sltech8

ASKER

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?

Guy Hengel [angelIII / a3]

>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

sltech8

ASKER

Thanks for the extra note, I did not know that and will amend my code accordingly. Thanks for helping with this problem