SQL code with VBA in Access 2007

[Tazbir]

Hi

I'm fairly new to VBA in Access. I want to run a Query using :
DoCmd.RunSQL SQL_text
However I'm experiencing problems in generating the SQL_text variable. The problem is related to the SQL code. I read some articles about it and I tried to apply that knowledge. Unfortunately, with no success. Please could you help me figure out what did I do wrong. My SQL_text variable is defined as:

SQL_text = "SELECT [Step9: Renaming Comment columns].[Target Region], [Step9: Renaming Comment columns].[Payroll Region], [Step9: Renaming Comment columns].Business_Unit, [Step9: Renaming Comment columns].[BU RPT], [Step9: Renaming Comment columns].[Sales Org BU], [Step9: Renaming Comment columns].[Sales Specialty Code], [Step9: Renaming Comment columns].[Sales Title], [Step9: Renaming Comment columns].[Employee Name (Last, First)], [Step9: Renaming Comment columns].[Employee ID], [Step9: Renaming Comment columns].[Manager Name (Last, First)], [Step9: Renaming Comment columns].[Manager Employee Number], [Step9: Renaming Comment columns].[Mail sent to Mgr (dd-mm-yy)], " & _
"[Step9: Renaming Comment columns].[Handled By], [Step9: Renaming Comment columns].[Manager Notification Sent], [Step9: Renaming Comment columns].[Manager Notification Sentdate], [Step9: Renaming Comment columns].[Days Outstanding], [Step9: Renaming Comment columns].[Generation Date2] AS [Generation Date], [Step9: Renaming Comment columns].[Letter Issue Date], [Step9: Renaming Comment columns].[Viewed Date], [Step9: Renaming Comment columns].[Letter Passively Approved (dd-mm-yy)], [Step9: Renaming Comment columns].[Accepted Date], [Step9: Renaming Comment columns].[Reject Reason], [Step9: Renaming Comment columns].[Quest CL Status]" & _
", [Step9: Renaming Comment columns].[CEE CL Status], [Step9: Renaming Comment columns].[Measurement Period], [Step9: Renaming Comment columns].[Emp Status Code], [Step9: Renaming Comment columns].[SalesLetter Employee#Active Flag] AS [Sales Letter Employee Active Flag], [Step9: Renaming Comment columns].[Block View By Manager], IIf([Generation Date] Is Null,[Why not Created],'') AS [Why not Generated], IIf([Generation Date] Is Null,[Other Comment for not Created],'') " & _
"AS [Other Comment for not Generated], IIf([Quest CL Status]='Escalated',[What action taken against Escalation],'') AS [What actions taken against Escalation], IIf([CEE CL Status]='Generated' And [Quest CL Status]<>'Accepted' And [Quest CL Status]<>'Escalated',[Why not Accepted2],'') AS [Why not Accepted]" & _
"FROM [Step9: Renaming Comment columns] LEFT JOIN [FY11 H1 CEE Comments Table] ON [Step9: Renaming Comment columns].[Employee ID] = [FY11 H1 CEE Comments Table].[Employee ID];"

The code crushes and displays a Run time error 2342 "A RunSQL action requires an argument consisting of an SQL statement

 

[JANR]

Look at this link for an explenation:

http://www.access-programmers.co.uk/forums/showthread.php?t=207819

JR

 

[Tazbir]

Thanks for the link. If I understand corectly I cannot run this query because of the SELECT key word.

 

[JANR]

Yes, Docmd.RunSQL is ONLY for action queries (Update, append,Delete)

JR

 

[DCrake]

SQL_text = "SELECT [Target Region], [Payroll Region], [Business_Unit], [BU RPT], [Sales Org BU], [Sales Specialty Code], [Sales Title], [Employee Name (Last, First)], Employee ID], [Manager Name (Last, First)], [Manager Employee Number], [Mail sent to Mgr (dd-mm-yy)], " & _
"[Handled By], [Manager Notification Sent], [Manager Notification Sentdate], [Days Outstanding], [Generation Date2] AS [Generation Date], [Letter Issue Date], [Viewed Date], [Letter Passively Approved (dd-mm-yy)], [Accepted Date], [Reject Reason], [Quest CL Status]" & _
", [CEE CL Status], [Measurement Period], [Emp Status Code], [SalesLetter Employee#Active Flag] AS [Sales Letter Employee Active Flag], [Block View By Manager], [B]IIf([COLOR="Red"][Generation Date] Is Null[/COLOR],[Why not Created],'') AS [Why not Generated], IIf([COLOR="red"][Generation Date] Is Null[/COLOR],[Other Comment for not Created],'') [/B]" & _
"AS [Other Comment for not Generated], IIf([Quest CL Status]='Escalated',[What action taken against Escalation],'') AS [What actions taken against Escalation], IIf([CEE CL Status]='Generated' And [Quest CL Status]<>'Accepted' And [Quest CL Status]<>'Escalated',[Why not Accepted2],'') AS [Why not Accepted]" & _
"FROM [Step9: Renaming Comment columns] LEFT JOIN [FY11 H1 CEE Comments Table] ON [Step9: Renaming Comment columns].[Employee ID] = [FY11 H1 CEE Comments Table].[Employee ID];"

Take a look at the bold text this is a duplicate condition which will never be met

 

[jason97m]

Correction, the DoCMD.RUNSQL command can be used for SELECT queries. The stipulation is that the SELECT must go into a new table like this:

DoCMD.RUNSQL "SELECT * INTO table2 FROM table1"

This will not give the dreaded 2342 error message.

 

[Tazbir]

Thanks a lot guys this was really helpful