IN clause, Inner Joins and Update - is it possible

[RichD]

I am trying to use the IN clause to update a table in another mdb. VBA produces the SQL string, but I get Error 3144 Syntax Error in Update statement.
The table names and field names are the same in both mdbs.
Here is the string:


UPDATE [projectcodes]INNER JOIN [projectcodes]IN C:\Updatetables2.mdb ON [projectcodes].

 = [projectcodes].[Code] IN C:\Updatetables2.mdb SET [projectcodes].[Code] IN C:\Updatetables2.mdb = [projectcodes].[Code], [projectcodes].[Description]IN C:\Updatetables2.mdb = [projectcodes].[Description];

There are more fields in SET clause, but I have abbreviated them.
I have checked Help files and various forums, but cannot find an answer. Maybe its not possible to update a table this way. 

TIA

Rich

Bespoke and off-the-shelf Access solutions
[URL unfurl="true"]www.blsoftware.co.uk[/URL]

 

[Golom]

I think that you need to specify range variables for your tables like this
[tt]
UPDATE [projectcodes] As P1
INNER JOIN
[projectcodes] IN "C:\Updatetables2.mdb" As P2
ON P1.

 = P2.[Code] 
SET P2.[Description] = P1.[Description]
[/tt][/color]
I've eliminated [b]SET P2.Code = P1.Code[/b] because you are matching the two tables on the "Code" field so they are already equal.

 

[RichD]

Thanks Golom I have put variables in as follows, but I still get the same error:

UPDATE [projectcodes] As T1 INNER JOIN [projectcodes]IN 'C:\Updatetables2.mdb' As T2 ON T1.

 = T2.[Code] SET T2.[Code] = T1.[Code], T2.[Description] = T1.[Description], T2.[Client] = T1.[Client], T2.[Full Description] = T1.[Full Description], T2.[Date Started] = T1.[Date Started;

I have left the SET T2.code = T1.Code in as sometimes there may be up to three inner joins. I thought I would try to get it working with just one first.
Also I have tried it with single quotes around the path and without but the same error comes.

Thanks again. I would be really grateful if you could have another look.

Rich

Bespoke and off-the-shelf Access solutions
[URL unfurl="true"]www.blsoftware.co.uk[/URL]

 

[Golom]

After a bit of investigation, it appears that Access is a bit brain damaged here. It won't allow you to do a join between a local table (i.e. one in this database) and an external one. I instead, created a query called 'PCF' like this:
[tt]
SELECT *
FROM ProjectCodes AS P2 IN 'C:\Projects.mdb';
[/tt]
and then the following does work
[tt]
UPDATE ProjectCodes INNER JOIN PCF
ON ProjectCodes.Code = PCF.Code
SET PCF.Description = [ProjectCodes].[Description];
[/tt]

 

[Golom]

PS. If you have Access XP then the following also works
[tt]
UPDATE ProjectCodes INNER JOIN
[SELECT * FROM ProjectCodes AS P2 IN 'C:\Projects.mdb']. AS PCF
ON ProjectCodes.Code = PCF.Code
SET PCF.Description = [ProjectCodes].[Description];
[/tt]

 

[RichD]

Golom,

In the end I took the easy way out and linked the remote table in VBA, ran the modified SQL string, then deleted the link.
Many thanks for your help, I have learnt a bit more about SQL strings today.

Rich


Bespoke and off-the-shelf Access solutions

http://www.blsoftware.co.uk