0

i am trying to write a simple append query using SQL for my access database. Upon trying to execute the code, the message i am getting is:

Complilation error. Exepected function or variable

The query is a query which joins 4 tables and pastes the fields into another table. When using a standard MS Access query it works fine. I then generated and copied the SQL code (below) but unfortunately cannot get the query to work.

A final note about something strange. Unlike all the other SQL queries i have successfully written, this one, upon writing the Application.DoCmd.RunSQL (st_sql) into VBA, the space between the "L" and the "(st_sql) for some reason gets truncated.. Strange, this doesnt happen for any other string in the Whole routine where i successfully have other append queries.

Below is the code:

st_sql = "INSERT INTO[tblContactReporting03]([ID Project],[tblProjManagementPhaseHierarchy],[tblProjManagementSubPhaseHierarchy],[ID_Event],[SubTask_Hierarchy],[Project],[Sub project],[Project_Phase],[Project_Sub_Phase],[ContactFullName],[Role_Type],[type],[Event],[Effective_date],[Commitment],[Sub_task_name],[Status],[Notes])" & _
            "SELECT[tblProjectMasterList].[ID Project],[tblProjManagementPhase].[Hierarchy],[tblProjManagementSubPhase].[Hierarchy],[tblContactReporting02].[ID_Event],[tblContactReporting02].[SubTask_Hierarchy],[tblProjectMasterList].[Project],[tblProjectMasterList].[Sub project],[tblProjManagementPhase].[Project_Phase],[tblProjManagementSubPhase].[Project_Sub_Phase],[tblContactReporting02].[ContactFullName],[tblContactReporting02].[Role_Type],[tblContactReporting02].[type]," & _
            "[tblContactReporting02].[Event], [tblContactReporting02].[Effective_date],[tblContactReporting02].[Commitment],[tblContactReporting02].[Sub_task_name],[tblContactReporting02].[Status],[tblContactReporting02].[Notes]" & _
            "FROM[tblProjectMasterListINNER JOIN ([tblProjManagementPhase] INNER JOIN ([tblContactReporting02] INNER JOIN [tblProjManagementSubPhase] ON [tblContactReporting02].[ID_Project_Sub_Phase] = [tblProjManagementSubPhase].[ID_Project_Sub_Phase]) ON ([tblContactReporting02].[ID_Project_Phase] = [tblProjManagementPhase].[ID_Project_Phase]) AND ([tblProjManagementPhase].[ID_Project_Phase] = [tblProjManagementSubPhase].[ID_Project_Phase])) ON [tblProjectMasterList].[ID Project] = [tblProjManagementPhase].[ID_Project]" & _
            "ORDER BY [tblProjectMasterList].[ID Project], [tblProjManagementPhase].[Hierarchy], [tblProjManagementSubPhase].[Hierarchy], [tblContactReporting02].[ID_Event], [tblContactReporting02].[SubTask_Hierarchy];" & _
Application.DoCmd.RunSQL(st_sql)
Improve this question

1 Answer 1

Reset to default
1

I'd recommend a Debug.Print st_sql before running so that you'll be able to debug the constructed SQL.

The error you're getting is because RunSQL is a sub, not a function, so you need to call it 1) without parentheses:

Application.DoCmd.RunSQL st_sql

or 2) preceed it with Call and use parentheses:

Call Application.DoCmd.RunSQL(st_sql)

You can use syntax 2 for functions that when you don't need to use their return value.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you pteranodon. This is interesting because I use this code all the time for routines and never had the problem. Perhaps because there are 4 joins included and it is more complex..? Thanks.. After trying however, i am now getting a syntax error on the join operation.. Does this simply mean that perhaps your solution is correct however I still don't have the code written correctly prior to the DoCmd.RunSQL ??
Hi. Just to let you know I found the syntax error.. Now my problem is that i am getting an error message regarding the first piece of the "INSERT INTO" line stating that field [tblContactReporting03].[ID Project] is unknown.. Very strange because it certainly exists and is spelt correctly.
On closer inspection, you'll need to work on that SQL. You need a spaces between INSERT INTO and [tblContactReporting03] and another space before the (. I see lots of places where [table] or [field] is touching the preceeding or following keyword. Also, look at your line breaks -- you need a space before ORDER BY, for example.
I think the missing spaces in the SQL string are probably the real cause of this problem, not the parentheses in RunSQL. When you are building SQL strings, like this, you can create a new query in Access, and then switch to SQL view instead of Access's default Query by Example view. You'll see just a blank box for text. Paste in your SQL and then switch to DataSheet view to test it. This will report SQL errors outside the context of VBA. In general, all SQL keywords need to be separated by spaces from table or field names, so you'll never have KEYWORD[tablename].[field].
It may also help to view your previously-created queries in SQL mode to get a feel for the SQL syntax of queries you already understand.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.