Solved

INSERT INTO SQL works in Access query editor, but not in VBA using ADO

Posted on 2010-11-08
3
424 Views
Last Modified: 2012-05-10
Hi Experts,

I am writing a query to insert a record into a Session table.

I can run the query from the Access SQL interface, but when I run it from VBA I get a "Syntax error in INSERT INTO statement".

Here is the srting from the immediate window: "INSERT into [SESSIONS] ([Sess_Start], [Sess_End], Username, UserID, Sess_Data, LanguageID, Locale, LastScreen, Usage, Success) VALUES (#08/11/2010 1:26:46 AM#, #08/11/2010 1:27:22 AM#, 'FinFlon', 9, 'SESSION_DATA_DUMP_08/11/2010 1:27:22 AM:SysUsage=OBSERVATION:Username=9:UserID=0:ObsSpecies=10:ObsCriteria=:ObsCentroidMap=:ObsDateDay=:ObsDateMonth=:ObsDateYear=:ObsDateUnknown=False:ObsQty=0:ObsNotes=:ENDDUMP=08/11/2010 1:27:22 AM:', 1, 1, 6, 1, False);"

Any help or ideas would be great.

Thanks,
0
Comment
Question by:WestCoastHip
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34082938
remove the final ";"

also, change "false" to -1 ...
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34083084
I can't see anything wrong with this sql, but I can reproduce your problem.

Instead of an ado command you could do it using :

Currentdb.execute strsql, dbfailonerror

which works OK.
0
 

Author Closing Comment

by:WestCoastHip
ID: 34083230
Thanks, I really appreciate your expertise. I was staring at that for hours.

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

10 Experts available now in Live!

Get 1:1 Help Now