SQL statements different in Access & SQL Server

[Gordo]

Does anyone know about this?

I tried to update a table in Access and got it working using Access tables, but when I linked to a table in SQL Server the update didn't work.

I then went to SQL Server Management Studio and created a SQL Statement that would work in SQL, but it wouldn't work in Access. I want to be able to update a SQL table from Access. Can anyone help?

G-

 

[pbaldy]

Generally speaking, the Access syntax should work against a SQL Server table linked to the db (the ODBC DSN translates). The T-SQL syntax would have to be in a pass-through query to be used from Access. Not knowing what "didn't work" means, hard to help further.

 

[Gordo]

Hi Pbaldy,

Thanks for your suggestion. I didn't realize the OBDC did any translation between dbs, so that's info to me. I also had a friend who told me to try the pass through query, so I'll try that.

Many thanks for your assistance.

Gordo

 

[pbaldy]

No problem Gordo. Post back if you get stuck. A pass through query forces the processing to happen on the server, so it's sometimes more efficient. Access will actually try to do that anyway, but can't always do it.

 

[Gordo]

Still having similar problems as with my regular access query. I get the error" ODBC -call failed

[SQL Server Native Client 10] Incorrect syntax by the keyword LEFT (#156)

Maybe I'm using the wrong Native client. When I built my DSN I noticed there were several choices.

G

 

[pbaldy]

What's the SQL of the query?

 

[Gordo]

Here's the query:

UPDATE Inv_Recd LEFT JOIN dbo_Inventory ON Inv_Recd.ItemNum = dbo_Inventory.ItemNum SET dbo_Inventory.ItemNum = [Inv_Recd]![ItemNum], dbo_Inventory.In_Stock = [Inv_Recd]![Received];

G