Solved

error in insert into statement

Posted on 2008-06-19
8
630 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:robinu
ID: 21820914
Maybe some of the fields are too large?

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

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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now