Solved Using ANSI 92 SQL Syntax

[DickyP]

Is it possible to set the 'SQL Server Compatible Syntax (ANSI 92)' option in VBA?

 

[DickyP]

Found it! setOption "ANSI Query Mode", 1

 

[isladogs]

Are you sure you want to do this?

 

[DickyP]

No I don't want to do this, but have to for one specific purpose only! To load a table from a CREATE TABLE sql which has a Decimal data type I have to!

My usage is:

setOption "ANSI Query Mode", 1

DoCmd.RunSQL "CREATE TABLE ... etc"

setOption "ANSI Query Mode", 0

It works.

 

[isladogs]

Good to see that you are reverting back immediately. Years ago I changed it and forgot to swop back. Lots of issues that I couldn’t pin down as I couldn’t remember what I’d changed

 

[GPGeorge]

One of the recent bugs in Access, which was recently fixed, was paralleled, it turns out, when ANSI 92 was turned on. Tracking that down was, like Colin said, not easy. I'm surprised that there is actually a use case for it.

 

[isladogs]

I think you are referring to reserved error -1001 which I had reported and was fixed in version 2311

Reserved Error - There is No Message for this Error

This article discusses the unhelpful reserved error messages that you may occasionally see. The meaning is explained for several of these errors together with action you can take to help eliminate these in the future.

isladogs.co.uk

There may be other such examples

As for use cases, perhaps it was useful back in the far distant days of Access Data Projects (ADP) which allowed direct connection to SQL Server but were deprecated in A2007

 

[GPGeorge]

I think you are referring to reserved error -1001 which I had reported and was fixed in version 2311

Reserved Error - There is No Message for this Error

This article discusses the unhelpful reserved error messages that you may occasionally see. The meaning is explained for several of these errors together with action you can take to help eliminate these in the future.

isladogs.co.uk

There may be other such examples

As for use cases, perhaps it was useful back in the far distant days of Access Data Projects (ADP) which allowed direct connection to SQL Server but were deprecated in A2007

Thanks, Colin, that may have been the one I was thinking of.