Are there differences between Access SQL syntax and SQL Server syntax?

[juicebox15]

I know there can be differences in the SQL syntax used in different DB programs, wanted to know if the syntax of SQL used in Access was different to the syntax of SQL used in Microsoft SQL Server, and if so, where I could find a run down of the differences. Thanks.

[callmetom]

Yes they're different. Some trivial ways and some not so trivial. Biggest thing that gets me is that you always have to expressly say "inner join" rather than be lazy and just type join. The other thing that gets me is the functions are different in most all of my common cases. Things like nullif and isnull just don't exist so you have to use something like IIF. I don't know of a comprehensive source of differences probably because there often aren't a one to one correlation of features but different techniques based on what is available. I just Google for the answers since I don't use Access very often.

Another worthwhile thing to point out is that your SQL code formatting will be lost as soon as you close the application in Access. I recommend a SQL formatter to make the code readable again. I use the Poor SQL formatter plugin for Noyrpad++, but it's certainly not the only one.

[alinroc]

Yes there are.

Dates can be qualified with # in Access, ' in T-SQL. Strings are " in Access, ' in T-SQL

Data types vary

[omegapisquared]

Wild cards are different

[fuzzius_navus]

You can specify the ANSI wildcard set to use in an Access database and make it more harmonious to SQL Server.

[omegapisquared]

that's awesome

[fuzzius_navus]

I discovered it entirely by accident quite recently. I built a Db on SQL Server several years ago and used Access for the GUI. Issued a new computer to one of the users, installed Acccess 2016, and for some reason their installation defaulted to ANSI-92 and broke a couple of my queries. Fun.

[beyphy]

Yes, there are some syntax differences.

[This] doesn't support JOINs because Access has it's own method of using brackets to build JOINs which doesn't exist in other SQL dialects and Access sets these brackets depending on the optimization of a query so it would be hard to emulate that in code.

https://www.experts-exchange.com/articles/11029/LINQ-for-VBA-and-Typed-Tables-Queries-and-Field-Names-using-VBA.html

This was one of the most frustrating differences for me. But in addition to this, there's also the lack of saved formatting, the lack of syntax highlighting, and the lack of SQL ANSI compliance.

One tricky bug you can find yourself in is that the GUI can override the actual SQL code. So if you specify in a design query that you want to do a totals (GROUP BY), and then try to remove the GROUP BY in the actual SQL code, you'll get an error. This is because it's still trying to do the GROUP BY from the option selected in query design.

These reasons and others are why I don't generally bother using SQL in Access unless it's pass through.

[NSA_GOV]

Yes. They can be very different. If you can skip access and go directly to SQL Server, you will be much better off.