• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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

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
WestCoastHip
Asked:
WestCoastHip
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the final ";"

also, change "false" to -1 ...
0
 
peter57rCommented:
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
 
WestCoastHipAuthor Commented:
Thanks, I really appreciate your expertise. I was staring at that for hours.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now