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

When I connect Access 2007 to SQL Server (whether 开发者_如何学Cby 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?

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?

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.

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.