CODEWITHSUNDEEP
ms-access
Desert Spider
Desert Spider

Reputation: 778

Limiting Linked Table Results

I have a table that I have linked from another database that has both Active and Inactive employees. I am only wanting to see the Active employees inthe linked table. I know how to do this through a query, but I dont want to have a query if there is a way to only show records that have a "Active" in the Status field.

Upvotes: 4

Views: 4131

Answers (1)

HansUp
HansUp

Reputation: 97101

With your linked table open in Datasheet View, click the Status column then apply a filter ... in Access 2007, click the filter (funnel) icon on the "Sort & Filter" section of the "Home" ribbon. Choose "Equals" from the "Text filters" dropdown of the resulting dialog and enter Active in the text area. Click OK and save the table layout by clicking the save icon or with the Ctrl+s keyboard shortcut.

Then open the table in Design View. Click yes to "open it anyway" when it warns you that you can't modify the design of a linked table. (You won't be modifying the table design, but you will be changing an item in its TableDef.Properties collection.) From there, open the table's property sheet, locate the "Filter On Load" property and set it to Yes. Close the table and click Yes when Access asks whether to save the change.

If my instructions were adequate, you should see only those rows with Status="Active" when you reopen the linked table in Datasheet View.

Here is a screen capture from my system where I saved a filter expression, ([AssetMovements1].[AssignedTo] Like"a*"), for my AssetMovements1 linked table.

Table Property Sheet

Those properties could be set from VBA code if you prefer. Just beware that Filter is a user-created property which means it doesn't exist in the Properties collection until you assign it a value. From code, you would need to use the CreateProperty method and then assign the property value.

Upvotes: 6

Related Questions