UPDATE query wrong syntax

[jordankoal]

I have a database in which when it opens the main form loads and there is an On Load event for that form. It will look at the tables and for each table that doesn't start with "MSys", "~", "tbl" and "contact". This will pull the tables I need and store the names into an array. I then loop through the array and for each name in the array I am doing an update query to update tblDrawingPhases. This query updates tblDrawingPhases.DrawingStatus with what's in Array(i)(current table name stored in the array). It also has a criteria where it will only check projects in tblDrawingPhases where tblDrawingPhases.Project Number = Array(i).Project Number

I get an error when it runs that points to db.Exectue strSql saying Syntax Error in UPDATE statement.

Here is the code:

For i = 0 To UBound(tableArray)
strSql = "UPDATE tblDrawingPhases "
strSql = strSql & "INNER JOIN " & tableArray(i) & " "
strSql = strSql & "ON [tbleDrawingPhases].[Project Number] = [" & tableArray(i) & "].[Project Number] "
strSql = strSql & "SET "
strSql = strSql & "[tblDrawingPhases].[Drawing Status] = [" & tableArray(i) & "].[Drawing Status] "
strSql = strSql & "WHERE [tblDrawingPhases].[Project Number] = [" & tableArray(i) & "].[Project Number];"
txtTest.Value = strSql
db.Execute strSql

*NOTE: txtTest.Value = StrSql is just so I can see what is actually being stored in the *

Here is what's put there:

UPDATE tblDrawingPhases INNER JOIN Test Database ON [tbleDrawingPhases].[Project Number] = [Test Database].[Project Number] SET [tblDrawingPhases].[Drawing Status] = [Test Database].[Drawing Status] WHERE [tblDrawingPhases].[Project Number] = [Test Database].[Project Number];

Looking for some guidance to what I did wrong in the statement. Thanks!

 

[pbaldy]

For starters, due to the inadvisable space you need to bracket the table name after the join.

 

[jordankoal]

For starters, due to the inadvisable space you need to bracket the table name after the join.

I went in and put the table in the INNER JOIN in brackets and I get an error that says "Syntax error in JOIN operation"

strSql = strSql & "INNER JOIN [" & tableArray(i) & "] "

 

[pbaldy]

Try pasting the sql into a new query and trying to run it. Sometimes you'll get a better error message. I'm not sure that the WHERE clause is necessary, as the join will handle it.

 

[jdraw]

Do debug.print strSQL before the db.Execute line and show readers what gets printed in the immediate window.

 

[jordankoal]

I tried copying into a query and it gave me the same error. I put debut.print in there but I don't see a window anywhere that prints anything out

 

[jdraw]

Hit ctrl G and show the immediate window.

You should read and apply http://www.cpearson.com/excel/DebuggingVBA.aspx

 

[jordankoal]

UPDATE Test Database INNER JOIN tblDrawingPhases ON (Test Database.[Drawing Number Temp] = tblDrawingPhases.[Drawing ID] AND (Test Database.[Project Number] = tblDrawingPhases.[Project Number]) SET tblDrawingPhases.[Drawing Status] = Test Database![Drawing Status];

 

[jordankoal]

I have been throwing the strSQL into a text box. It gives me the same result and I throw outputs out as needed but will definitely read the information in the link

 

[jordankoal]

I linked the tables twice. First, arrayTable(i).Drawing Number Temp needs to equal the drawing number in tblDrawingPhases. The same goes for Project Number. Then I took out the WHERE clause.

 

[jordankoal]

I made the same query using the query design. I did visually what was typed in the query. I get a warning saying "You won't be able to undo the changes this action query is about to make to the data in a linked table or tables. Do you wantto run this action query anyway?" I click yes. It runs the query saying it will update 3 rows. I click yes again and it updates the query correctly.

I looked at the SQL that was generated and copied it directly into the VBA strSQL. I tried this before and it didn't work with other queries. I tried it this time and it worked... Well, I guess that solved that problem. I thank you all for the responses, they were greatly appreciated!

 

[jdraw]

your code

UPDATE Test Database INNER JOIN tblDrawingPhases
 ON (Test Database.[Drawing Number Temp] = tblDrawingPhases.[Drawing ID] AND 
(Test Database.[Project Number] = tblDrawingPhases.[Project Number])
 SET tblDrawingPhases.[Drawing Status] = Test Database![Drawing Status];

Suggested code change:

UPDATE[COLOR="Red"][B] [[/B][/COLOR]Test Database[COLOR="Red"][B]] [/B][/COLOR]INNER JOIN tblDrawingPhases
 ON ([COLOR="Red"][B][[/B][/COLOR]Test Database[B][COLOR="Red"]][/COLOR][/B].[Drawing Number Temp] = tblDrawingPhases.[Drawing ID] 
AND ([B][COLOR="Red"][[/COLOR][/B]Test Database[B][COLOR="Red"]][/COLOR][/B].[Project Number] = tblDrawingPhases.[Project Number]) 
SET tblDrawingPhases.[Drawing Status] =[COLOR="Red"][B][[/B][/COLOR]Test Database[B][COLOR="Red"]].[/COLOR][/B][Drawing Status];