Fix new Settings Incompatibility Bug
I believe I have hit a new bug in ACCESS.
This conflict shows up when a front end using standard Access SQL connects to a Back-end using SQL Server Syntax (ANSI92).
It results in very strange behavior with Combo-Boxes failing to Auto-Complete and Wildcard Queries not returning results.
Access 2010 is immune! Phew.
It is repeatable and can be reproduced by following the process:
There appears to be a new bug in ACCESS 365 / Click to Run.
It DOES NOT EXHIBIT on Access 2010, but I have not tested with 2013.
Issue: Combo Boxes (drop-downs) fail to Auto-Complete List Items when user is keying Data.
Additionally, some Queries fail to return results.
The Cause:
SQL Server Syntax setting.
To Reproduce:
Create a New Database (Call It TestBE or Similar)
Go to File > Options > Object Designers
Under Query Design find “SQL Server Compatible Syntax (Ansi 92)”
Untick/ Uncheck if selected. Click OK to apply…
Create a new Table tblLinked with an ID (auto-number) field and a Description Field (Short Text)
Populate a few rows of this table with some words
Close the TestBE Database.
Create a new Blank database (Call it TestFE or similar) with Access 365CTR (I’m using Version 1905)
Go to File > Options > Object Designers
Under Query Design find “SQL Server Compatible Syntax (Ansi 92)”
Untick/ Uncheck if selected. Click OK to apply…
Create a small Table tblTEST with an ID (auto-number) field and a Description Field (Short Text)
Populate this table with some random words in the Description.
Create a new Unbound Form (Form1). Add a ComboBox with RowSource set to tblTEST. Set 2 Columns, with Column widths set to 0cm;5cm
Normal Behavior:
Open the form Form1. Type the first letter of a Description in the Combo box. Access will autocomplete the word if matched. Correctly.
The ISSUE:
Now still in the TestFE database; link to the table tblLinked in the TestBE Databases.
Open Form1 and test the Combo again. Functionality is still OK. Clear the Combobox.
Open the linked table tblLinked. It works fine. Close it.
Open Form1 and test the Combo again. Functionality is still OK. Clear the Combobox.
Create a new query on tblLinked. “SELECT * FROM tblLinked” Save this as qryFail.
Open Query qryFail in datasheet view. Looks fine.
Open Form1 and test the Combobox with a single letter . Autocomplete still works.
Clear the Combo box.
Open qryFail again and Click Refresh All from the Home Ribbon.
Now on Form1 test the ComboBox again. Auto-complete Fails!
It will stay failed until the database is closed and reopened.
Any Query’s in the Database using a wildCard Like * will now only return results if using SQL Syntax of Alike %.
In Access 2010 this is not seen!

1 comment
-
Brian commented
oops I missed a step!
The TestBE must be Set to “SQL Server Compatible Syntax (Ansi 92)” to see the error!
Go to File > Options > Object Designers
Under Query Design find “SQL Server Compatible Syntax (Ansi 92)”
Tck/ Check if NOT selected. Click OK to apply…