Query not working in code

J

jez123456

Hi experts

I have a query named qryUpdate@QueryObjects which has the following SQL

UPDATE [@QueryObjects] INNER JOIN [@QueryObjects] AS [@QueryObjects_1] ON
[@QueryObjects].OBJECT_ID = [@QueryObjects_1].SHORTCUT SET
[@QueryObjects_1].FILENAME = [@QueryObjects].[FILENAME]
WHERE ((([@QueryObjects].FILENAME)<>[@QueryObjects_1].[FILENAME]) AND
(([@QueryObjects_1].CATEGORY)=13));

I can run this query successfully with the vba code

DoCmd.OpenQuery "qryUpdate@QueryObjects"

However, I would like to get rid of the query qryUpdate@QueryObjects and
just use vba code to produce the same effect. I've tried using

DoCmd.RunSQL ("UPDATE [@QueryObjects] INNER JOIN [@QueryObjects] AS
[@QueryObjects_1] ON [@QueryObjects].OBJECT_ID = [@QueryObjects_1].SHORTCUT
SET [@QueryObjects_1].FILENAME = [@QueryObjects].[FILENAME] WHERE
((([@QueryObjects].FILENAME)<>[@QueryObjects_1].[FILENAME]) AND
(([@QueryObjects_1].CATEGORY)=13));")

but this is not working.

Any ideas why, as the sql code is the same?
 
J

jez123456

Hi Van

I've tried the string without the parentheses and also using

Dim SQL As String

SQL = "UPDATE [@QueryObjects] INNER JOIN [@QueryObjects] AS
[@QueryObjects_1] ON [@QueryObjects].OBJECT_ID =
[@QueryObjects_1].SHORTCUT
SET [@QueryObjects_1].FILENAME = [@QueryObjects].[FILENAME] WHERE
((([@QueryObjects].FILENAME)<>[@QueryObjects_1].[FILENAME]) AND
(([@QueryObjects_1].CATEGORY)=13));"

DoCmd.RunSQL SQL

but this is not working either

Van T. Dinh said:
Try without the parentheses around the SQL String.

--
HTH
Van T. Dinh
MVP (Access)


jez123456 said:
Hi experts

I have a query named qryUpdate@QueryObjects which has the following SQL

UPDATE [@QueryObjects] INNER JOIN [@QueryObjects] AS [@QueryObjects_1] ON
[@QueryObjects].OBJECT_ID = [@QueryObjects_1].SHORTCUT SET
[@QueryObjects_1].FILENAME = [@QueryObjects].[FILENAME]
WHERE ((([@QueryObjects].FILENAME)<>[@QueryObjects_1].[FILENAME]) AND
(([@QueryObjects_1].CATEGORY)=13));

I can run this query successfully with the vba code

DoCmd.OpenQuery "qryUpdate@QueryObjects"

However, I would like to get rid of the query qryUpdate@QueryObjects and
just use vba code to produce the same effect. I've tried using

DoCmd.RunSQL ("UPDATE [@QueryObjects] INNER JOIN [@QueryObjects] AS
[@QueryObjects_1] ON [@QueryObjects].OBJECT_ID = [@QueryObjects_1].SHORTCUT
SET [@QueryObjects_1].FILENAME = [@QueryObjects].[FILENAME] WHERE
((([@QueryObjects].FILENAME)<>[@QueryObjects_1].[FILENAME]) AND
(([@QueryObjects_1].CATEGORY)=13));")

but this is not working.

Any ideas why, as the sql code is the same?
Click to expand...
Click to expand...
 
V

Van T. Dinh

If you use concatenations to construct the SQL String in code, then check
your concatenation.

Use the Debug.Print SQL to check what is actually being passed to JET for
processing.
 
Top