Invalid SQL Statement 3129 - Expected DELETE, INSERT, etc..

sgrace

Registered User.
Local time
Today, 18:01
Joined
Dec 13, 2011
Messages
22
Hello all.

I'm getting this error on DoCmd.RunSQL stSQL statement:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (Error 3129)

My stSQL is program generated but Debug.Print shows this as the resulting string for runSQL processing:

INSERT INTO tblcf_GroupTreatmentForms (GR_CopyIndicator, GR_PFR_AutoID_PatientFormsRegistry, GR_AxisIa, GR_GroupName, GR_GroupType, GR_Reliability, GR_Participation, GR_GroomingHygiene, GR_Behavior, GR_GroupTheme, GR_PatientProgress, GR_InteractionsWithOther) SELECT 'C', GR_PFR_AutoID_PatientFormsRegistry, GR_AxisIa, GR_GroupName, GR_GroupType, GR_Reliability, GR_Participation, GR_GroomingHygiene, GR_Behavior, GR_GroupTheme, GR_PatientProgress, GR_InteractionsWithOther FROM tblcf_GroupTreatmentForms WHERE GR_PFR_AutoID_PatientFormsRegistry = 646


I can't tell what's wrong with the syntax. I'm trying to duplicate a record in a table and this same mechanism is working fine for other tables.


Any help/insight greatly appreciated.
 
For your convenience, here is the SQL in a formatted version:

Code:
[FONT=Courier New][SIZE=2][COLOR=blue]INSERT[/COLOR] [COLOR=blue]INTO[/COLOR] [COLOR=maroon]tblcf_grouptreatmentforms[/COLOR] 
            [COLOR=maroon]([/COLOR][COLOR=maroon]gr_copyindicator[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_pfr_autoid_patientformsregistry[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_axisia[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_groupname[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_grouptype[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_reliability[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_participation[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_groominghygiene[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_behavior[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_grouptheme[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_patientprogress[/COLOR][COLOR=silver],[/COLOR] 
             [COLOR=maroon]gr_interactionswithother[/COLOR][COLOR=maroon])[/COLOR] 
[COLOR=blue]SELECT[/COLOR] [COLOR=red]'C'[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_pfr_autoid_patientformsregistry[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_axisia[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_groupname[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_grouptype[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_reliability[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_participation[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_groominghygiene[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_behavior[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_grouptheme[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_patientprogress[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]gr_interactionswithother[/COLOR] 
[COLOR=blue]FROM[/COLOR]   [COLOR=maroon]tblcf_grouptreatmentforms[/COLOR] 
[COLOR=blue]WHERE[/COLOR]  [COLOR=maroon]gr_pfr_autoid_patientformsregistry[/COLOR] [COLOR=silver]=[/COLOR] [COLOR=black]646[/COLOR]  [/SIZE][/FONT]
 
Hello all.

I'm getting this error on DoCmd.RunSQL stSQL statement:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (Error 3129)

My stSQL is program generated but Debug.Print shows this as the resulting string for runSQL processing:

INSERT INTO tblcf_GroupTreatmentForms (GR_CopyIndicator, GR_PFR_AutoID_PatientFormsRegistry, GR_AxisIa, GR_GroupName, GR_GroupType, GR_Reliability, GR_Participation, GR_GroomingHygiene, GR_Behavior, GR_GroupTheme, GR_PatientProgress, GR_InteractionsWithOther) SELECT 'C', GR_PFR_AutoID_PatientFormsRegistry, GR_AxisIa, GR_GroupName, GR_GroupType, GR_Reliability, GR_Participation, GR_GroomingHygiene, GR_Behavior, GR_GroupTheme, GR_PatientProgress, GR_InteractionsWithOther FROM tblcf_GroupTreatmentForms WHERE GR_PFR_AutoID_PatientFormsRegistry = 646


I can't tell what's wrong with the syntax. I'm trying to duplicate a record in a table and this same mechanism is working fine for other tables.


Any help/insight greatly appreciated.

At first glance, it appears that the Insert statement has the same number of parameters with similar names for the corresponding ones. One thing that we cannot see is whether the Types are also the same. Have you tried to run this as a stand alone Query (without VBA)? If there are any syntax errors or Type Conflicts, SQL might be able to tell you what to fix.
 
Hello.

At first I didn't think to try running the query stand-alone in the native Access 2010 SQL tool. But when I did, it ran fine! I opened the query designer and copied the debug.print SQL ouput, just as originally posted below, into the SQL view of query designer. Then I saved it and ran it. It did exactly what it was supposed to, by appending a record to the specified table, with no error messages.

So now the question is why am I getting the 'Invalid SQL statement - 3129' when trying to run the command via VBA?

I can post all of the code here if of value, but in a nutshell:

Dim stSQL as String

Then I build stSQL programmatically and do a debug.print of stSQL to see the SQL that is posted below.

Then: DoCmd.RunSQL stSQL

That's when I get the 3129 error message pointing to the RunSQL line of code.

I'm at wits end with this and have to get past it so any help very much appreciated.
 
Thanks for all of the help.

I figured it out. Very sheepishly I must admit to a typo in the DoCmd.RunSQL line of code. I should have been specifying stSQL3 instead of stSQL.

Thanks again for the time invested. The suggestions did help me troubleshoot and rule out actual SQL issues.
 

Users who are viewing this thread

Back
Top Bottom