Big Tables with Filters Sorts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I write some code to remove filters and sorts from a large table? I
wouild do this on a form open event.

The problem is I have an application that can have a very large table.
Occasionally, I browse the table and set a filter or sort. When I open the
table agin it can take several minutes to open. If I had a means of
programtically removing filters and sorts on this table, life would be much
easier.
 
You can refer to the Filter property of the Properties of the TableDef.

Example:

With CurrentDb.TableDefs("Table1").Properties
.Delete "Filter"
.Delete "OrderBy"
End With

If the property does not exist, you will get error 3270.
 
I have some large (million+ records) tables and nothing ever takes that long.

In addition to removing the filter, I'd suggest checking the indexes on your
table, and make sure they are appropriate for your filters and sorts.

Also, in A2000 (and maybe A2003) the table property "Subdatasheet Name"
should be changed from the default of [AUTO] to [NONE] if you are not using a
subdatasheet (you'd know if you were using one)
 
Interesting point which I was going to follow up - Access 2000 is
automatically generating indexes which I don't want and which are counter
productive. I think it somehow relates to queries I build.
How do I stop Access doing that ? Or - does this step stop it doing this?

Now I look at the table poperties I also notice an OrderBy on RowNo. Should
I change that too? it does not look like it will help with performance.

David Mueller said:
I have some large (million+ records) tables and nothing ever takes that long.

In addition to removing the filter, I'd suggest checking the indexes on your
table, and make sure they are appropriate for your filters and sorts.

Also, in A2000 (and maybe A2003) the table property "Subdatasheet Name"
should be changed from the default of [AUTO] to [NONE] if you are not using a
subdatasheet (you'd know if you were using one)



Patrick said:
How can I write some code to remove filters and sorts from a large table? I
wouild do this on a form open event.

The problem is I have an application that can have a very large table.
Occasionally, I browse the table and set a filter or sort. When I open the
table agin it can take several minutes to open. If I had a means of
programtically removing filters and sorts on this table, life would be much
easier.
Click to expand...
Click to expand...
 
I'm not sure if it's good or bad, but I avoid putting a filter or orderby in
the properties of the table. If I need to filter or sort, I do so either
inside a query, or through the form.

I don't think Access can optimize or create indexes based on your queries.
However, somewhere (I think) there is a option that tells Access to
automatically create indexes when the field name ends in "ID" - I can't find
where that option is right now. Of course, you can delete the index if you
don't want it. It won't reappear unless you delete, and re-create the field
while the option is on.

Wheather or not your RowNo index is helpful or not depends on how you use
the table. If you order by RowNo, then that index would be very helpful. If
you order by RowNo, by 'someotherfield' then a compound index would serve you
well.


Patrick said:
Interesting point which I was going to follow up - Access 2000 is
automatically generating indexes which I don't want and which are counter
productive. I think it somehow relates to queries I build.
How do I stop Access doing that ? Or - does this step stop it doing this?

Now I look at the table poperties I also notice an OrderBy on RowNo. Should
I change that too? it does not look like it will help with performance.

David Mueller said:
I have some large (million+ records) tables and nothing ever takes that long.

In addition to removing the filter, I'd suggest checking the indexes on your
table, and make sure they are appropriate for your filters and sorts.

Also, in A2000 (and maybe A2003) the table property "Subdatasheet Name"
should be changed from the default of [AUTO] to [NONE] if you are not using a
subdatasheet (you'd know if you were using one)



Patrick said:
How can I write some code to remove filters and sorts from a large table? I
wouild do this on a form open event.

The problem is I have an application that can have a very large table.
Occasionally, I browse the table and set a filter or sort. When I open the
table agin it can take several minutes to open. If I had a means of
programtically removing filters and sorts on this table, life would be much
easier.
Click to expand...
Click to expand...
Click to expand...
 
In addition to all the other excellent ideas others have
posted. You can prevent the table's automatically created
visible(!) indexes by using Tools - Options - Table/Query
and clearing the list in the AutoIndex box.

Note that Access also automatically creates indexes for
foreign keys used in a relationship with enforced
referential integrity. You will **not** see these indexes
in the table's indexes list, but you can see them by using
DAO code to debug.print the tabledef's indexes collection.

While you're in the Tools - Options menu, be sure you have
disabled the Name Auto Correct option.
--
Marsh
MVP [MS Access]

Interesting point which I was going to follow up - Access 2000 is
automatically generating indexes which I don't want and which are counter
productive. I think it somehow relates to queries I build.
How do I stop Access doing that ? Or - does this step stop it doing this?

Now I look at the table poperties I also notice an OrderBy on RowNo. Should
I change that too? it does not look like it will help with performance.


David Mueller said:
I have some large (million+ records) tables and nothing ever takes that long.

In addition to removing the filter, I'd suggest checking the indexes on your
table, and make sure they are appropriate for your filters and sorts.

Also, in A2000 (and maybe A2003) the table property "Subdatasheet Name"
should be changed from the default of [AUTO] to [NONE] if you are not using a
subdatasheet (you'd know if you were using one)
Click to expand...
Click to expand...
 
Back
Top