Why don't the parentheses in SQL around the field name work in Access VBA ADO?

Question

In MS Access QBE, if I insert the following SQL, it works correctly, and I return 2 records -

SELECT [tmp_binning].[bn_faibash] FROM [tmp_binning] WHERE key2='0210043-HOU-STOR' ORDER BY [tmp_binning].[bn_faibash]; 

But if I programmatically run the same request in VBA from an ADO object, I get (incorrectly) no records. If I change SQL to remove the brackets around the field name, it will correctly return 2 records in VBA ADO.

 SELECT [tmp_binning].bn_faibash FROM [tmp_binning] WHERE key2='0210043-HOU-STOR' ORDER BY [tmp_binning].bn_faibash; 

I was not successful to understand why this is happening on its own, can someone tell me why?

Thanks.

Answer 1

First, brackets are not required either in the Access user interface or through ADO. Just omit them in all environments and the problem should go away. (If this is an Access QBE property that adds parentheses, then consider another tool or instruction for creating SQL code!)

Secondly, even with the help of brackets, I cannot reproduce the error using your SQL code, for example.

 Sub gjskdjs() On Error Resume Next Kill Environ$("temp") & "\DropMe.mdb" On Error GoTo 0 Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & _ Environ$("temp") & "\DropMe.mdb" With .ActiveConnection Dim Sql As String Sql = _ "CREATE TABLE tmp_binning" & vbCr & "(" & vbCr & " bn_faibash VARCHAR(255)," & _ " " & vbCr & " key2 VARCHAR(255)" & vbCr & ");" .Execute Sql Sql = _ "INSERT INTO tmp_binning (bn_faibash, key2)" & _ " VALUES ('002', '0210043-HOU-STOR');" .Execute Sql Sql = _ "INSERT INTO tmp_binning (bn_faibash, key2)" & _ " VALUES ('001', '0210043-HOU-STOR');" .Execute Sql Sql = _ "SELECT [tmp_binning].bn_faibash " & vbCr & " FROM" & _ " [tmp_binning] " & vbCr & " WHERE key2 = '0210043-HOU-STOR'" & _ " " & vbCr & " ORDER " & vbCr & " BY [tmp_binning].bn_faibash;" Dim rs Set rs = .Execute(Sql) MsgBox rs.GetString End With Set .ActiveConnection = Nothing End With End Sub 

Consider placing your schema as an SQL DDL with sample data.