Ms Access Query with sql server linked tables

[jcarneir]

Hi,

I have at work, a mdb with some tables a queries. We are migrating the tables to an sql server enviroment and the mdb is now empty, with some queries and the new linked tables but...

The queries don't work now.

case 1:

"Delete * From TableA" is not working

Case 2:
suppose TableB have 2 rows and 2 fields;

FieldKey FieldStr
01 One
02 Two

"Update TableA Inner Join TableB on TableA.SomeField = TableB.FieldKey Set TableA.AnotherField=TableB.FieldStr" is not working too.

Any clue???

 

[r937]

case 1: remove the asterisk

case 2: depends if you're running the query as a pass-through (which you should) which uses sql server syntax

Access

update table1
inner
join table2
on table1.keyfld = table2.keyfld
set table1.fldx = table2.fldy
where table2.fldz = 'foo'

SQL Server

update table1
set fldx = t2.fldy
from table1 t1
inner
join table2 t2
on t1.keyfld = t2.keyfld
where t2.fldz = 'foo'



r937.com | rudy.ca