Re: Missing semicolon error???

This (misleading) error message occurs because a WHERE clause is meaningless
and invalid in an INSERT statement.

Mind you, having removed the WHERE clause you will next hit the problem
highlighted by Dale Fye, which is that you are trying to open a recordset
using an action query. Dale's solution is almost there, you should do this:

objDB.execute strSQL1, dbFailOnError


"Al" <Al@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BC7706AB-6729-4FF4-93F2-B3AA5E614045@xxxxxxxxxxxxxxxx
I am trying to run an Insert query and keep getting a "Rub-time error
'3137':
Missing semicolon (;) at end of SQL stement. I haven not seen this error
before; however, I have a form event that triggers the following code to
run
(I populate the variables and have other code too, but this is where the
problem lies):

Dim strSQL1 As String
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values ( "
&
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','"
+
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" +
CStr(FridayOfWeek) +
"','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID] = " & PID & ""

Set objDB = CurrentDb
Set objRS = objDB.OpenRecordset(strSQL1) 'IT ERRORS HERE

Call objRS.Close
Set objRS = Nothing
Set objDB = Nothing

This is what I see in debug (everything is getting populated fine):
"INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values (
1,'High','','Test Curr','Test Plan','Complete',1,'Friday, September 12,
2008','C',1,1) Where [ID] = 1"

Thanks in advance,
Al


.

Relevant Pages

  • Re: Missing semicolon error???
    ... (I populate the variables and have other code too, ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Missing semicolon error???
    ... (I populate the variables and have other code too, ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: Missing semicolon error???
    ... Agreed, but if you are going to add the dbFailOnError option, youalso need ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: Missing semicolon error???
    ... Unless this is actually a pass-thru query to SQL ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: Query to get Table schema
    ... Set objDB = Server.CreateObject ... objDB.Open sDBName ... 'Set objRS = objDB.Execute ... empty, "Y O U R T A B L E N A M E ")) ...
    (comp.databases.ms-access)
Loading