MS Access Database Check Box List Filters Missing On SQL Server back end

Question

When I connect Access 2007 to SQL Server (whether by ADO recordset or by linked table) I no longer get check box lists (of available filter values) in the datasheet column filters.

Is this feature available only with MDB/ACCDB and/or DAO?

Biscuit

The only checkbox list in datasheet view that I know of is the multivalue field available in ACCDB format. It's not supported in SQL Server so far as I know, and shouldn't be used by anyone who doesn't need it for Sharepoint compatibility. – Juliojulis

This has nothing to do with multi-valued fields. It is the checkbox list you see showing all the available values in a column when you are FILTERING a datasheet. In a datasheet view, next to each column header there is an arrow. If you click it you get a choice of filters as well as all the availble values assuming there aren't too many different values (e.g., a location field but not a dollar amount field). Very important and valuable feature. – Biscuit

Answer 1

I think the check box in datasheet view of native Access tables is governed by the "Display Control" property in the table design. I don't recall what's available when the table is in SQL Server. If you provide a form in "datasheet view", you should be able to bind a check box control to the SQL Server column.

Edit: I think I misunderstood your question yesterday. If you click the Office Button, select Current Database, then put a check in the "ODBC fields" box under "Filter lookup options" ... does that do what you want?

Fendig

You nailed it! Thanks. Nobody else knew the answer to this on other forums. – Biscuit

For Access 2010, go to the File tab > Options > Current Database then scroll down to "Filter lookup options..." – Promontory

YES! I checked the ODBC Fields checkbox and I got this issue solved! MANY THANKS! – Tacnode

Answer 2

I know we're breaking protocol by not opening a new question, but I'm going to answer this nevertheless so this thread will be complete. This is a more complete answer than the previous ones.

I think I have this topic nailed down now.

The lookup filters won't work with a recordsource that is not an Access object, and they don't work in linked tables directly.

You have to create a query of the linked table, for example: Select * from tblOrders, and use that query as the recordsource in order to get the lookup filters.

HOWEVER, I found a more flexible approach as well. I create passthrough queries to SQL/Server and use that as my recordsource. Then, in code, I set the SQL of the passthrough queries like this:

Currentdb.QueryDefs("qpstOrders").SQL="Select * from Orders where OrderID =" & Me.OrderID

In the current event of my subform, I change the query on the fly to pass the appropriate record -- or it can just be a more generic query. The lookup filters work fine this way and the interaction with SQL/Server is lightning fast.

Biscuit

Nobody will ever see this. It's great information, but since it was not posed as a real question, nobody will ever find it. – Asphyxiant

Answer 3

Open the database that you want to optimize.

Click File > Options to open the Access Options dialog box.

In the left pane of the Access Options dialog box, click Current Database.

In the right pane, under Filter lookup options, mark "ODBC Fields" check box.

Bubaline

This was already answered by Hansup 11 years ago... Your answer is a duplicate of his. – Biscuit