axsprog
04-01-2013, 09:14 AM
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
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