Solved

error in insert into statement

Posted on 2008-06-19
8
635 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

691 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