transitioning from vba/access sql to T-SQL and errors

[axsprog]

i have an Access database, heavy VBA, front-end to SQL Server 2008 backend.

I have generally allways used the following method to execute sql statements

strsql = "mysqlstatement"
then
currentdb.execute strsql, dbseechanges

This obviously is not used for select statements but has served me well for years for updates, insert into's, and deletes.

To speed up some processes I have written a function that creates a querydef for my "action" statements that does a passthrough to sql to be executed server side - and that function works flawlessly....Unless I am using joing statements

If i use a join statement syntax errors at tyhe left join or inner join are returned and if I use the following methods to replace the joins which works great in select statements:
examples:
SELECT A.A, B.B, C.C
FROM aaa AS A, bbb AS B, ccc AS C
WHERE
A.B = B.ID
AND B.C = C.ID
or
SELECT A.A, B.B, C.CFROM aaa AS AJOIN bbb AS B ON A.B = B.IDJOIN ccc AS C ON B.C = C.ID then a multipart error on not bound (column names) occur.

Below I give two real world examples that work perfectly as currentdb.executes but crash when I try to run them server side.

Any help would be beyond appreciated.

strsql = "UPDATE TASK inner JOIN [TASK Excel Data] ON TASK.[Activity ID] = [TASK Excel Data].[Activity ID] " _
& "SET [TASK Excel Data].JCID = [TASK].[JCID], [TASK Excel Data].JobCardNumber = [TASK].[JobCardNumber]"

strsql = "INSERT INTO tblResourceDisc ( ResouceID ) " _
& "SELECT DISTINCT TASKRSRC.[Resource ID] " _
& "FROM TASKRSRC LEFT JOIN tblResourceDisc " _
& "ON TASKRSRC.[Resource ID] = tblResourceDisc.[ResouceID] " _
& "WHERE tblResourceDisc.ResouceID Is Null"


Thank you

[Pogla]

Your update statement is the wrong format:

It should be something like

UPDATE TASK
SET [JobCardNumber]  = [TASK Excel Data].[JobCardNumber]
FROM [TASK Excel Data]
INNER JOIN TASK
ON TASK.[Activity ID] = [TASK Excel Data].[Activity ID]

SQL Server can handle spaces in table names and columns, I just wouldn't reccomend it.

Try just substituting

INSERT tblResourceDisc ('ResourceID')

Rather than

INSERT INTO tblResourceDisc ( ResouceID )