Solved

error in insert into statement

Posted on 2008-06-19
8
633 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

825 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