“What you don’t know can’t hurt you can drive you bonkers.” – R. Hedgins

Today’s posting will deal with an option setting that can minimize interoperability issues between Microsoft’s nuanced interpretations of standards.

Microsoft Access and Microsoft SQL

Background Info

SQL is actually an acronym for Structured Query Language. ANSI standards are guidelines for different companies to follow to allow for a consistent methodology for implementing certain features. All of the major database companies normally follow the ANSI standards in addition to adding their own additional commands. While we are all too familiar with how well standards are adhered too and the consequences of such.

In the beginning…

When Microsoft first purchased the database software that would later become Microsoft Access, one of the uniqueness about the aforementioned software was the internal query language it used was an incomplete version of the ANSI-89 standard for SQL. When Access 1.0, 2.0 and Access 95 were released Microsoft diligently followed along with the ANSI 89 standard while adding some additional functionality not supported in the ANSI-89 standard.

ANSI 89 vs ANSI 92

When the ANSI-92 standard was finalized Microsoft was faced with a conundrum of sorts. So many other pieces of Microsoft Access and their customers, were dependent on Microsoft’s interpretation of the ANSI-89 standard, they couldn’t update without losing a majority of backwards capability. Problems with backwards capability would have made a large group of their customers very upset. The ANSI-92 standard isn’t backwards compatible with the ANSI-89 standard. There are too many schema and technical reasons why you can’t mix and match the command structures. And as a result the SQL interpreter can only resolve ANSI-89 or ANSI-92 statements. And even today for whatever reason Microsoft Access’s default SQL language is ANSI-89. And this is what can drive us bonkers.

T-SQL (Microsoft’s version of SQL) was originally modeled after ANSI-92. T-SQL is currently aligned with the ANSI 2008 standard, which is backwards compatible with ANSI-92. Most of us writing SQL (T-SQL) code normally are writing ANSI-92 compliant code, and using features from ANSI 2003, ANSI 2006 and possibly ANSI 2008 standards.

Improving Access

Occasionally you may have a need to use Microsoft Access and dislike the little idiosyncrasies of writing queries in the older ANSI-89 format. You would rather use something more familiar to you. Or you may have a query you’ve already written and want to re-use it in Access. Well you are in luck. It is possible to tell Microsoft Access SQL interpreter that you will be writing queries in ANSI-92 format, and want to take advantage of some additional commands not available in ANSI-89.

In Access if you select menu command File – Options, and select Object Designer on the left side, you will see the options below. In the middle there is the option to switch to ANSI-92. You can select for the database and future databases you create can be defaulted to the ANSI-92 format.

Microsoft Access Options

Microsoft Access Options

 

Pros and Cons

The advantage is you will have the additional commands available in ANSI-92, you will be able to write queries in the format you are more accustomed to, and less things driving you bonkers.

The disadvantages are once you switch to ANSI-92, you can’t revert back to ANSI-89. Any queries currently in the database may need to be altered. Not everything that is supported in T-SQL is supported by Access. There some little things that may need to be adjusted here and there.

Happy Mining.
–Michael

Extra Info

Before switching the SQL language I would suggest taking a backup of the Access database. After applying the option the database will close, and follow it’s compress and cleanup routines.