Accessing a linked Dbase IV file is slow....

[Brad Pears]

I have an access 2000 app that uses a linked table to an older application
built in dbIV.

I have a combo box whose record source property is set to an SQL select
clause from this 'linked' table. The combo box allows the user to scroll
through the records or they can simply begin entering a portion of the field
and it will filter down to what they want.

The users are complaining that this process of case# selection is very very
slow - and indeed it is. There are many records in this linked dbase IV
table to boot.

Can anyone think of anything I could possibly do to speed this up? This
particular dbase file I am linking to is being updated on a regular basis
throughout the day by an old legacy dbaseIV application and as such I really
can't create a duplicate of it to use in my app (which would really speed it
up for sure.)

Does anyone have any ideas or are they going to have to just live with it
until the old legacy dbase app is converted (it is scheduled to be - but not
for another year anyway...)

Thanks, Brad

 

[Albert D. Kallal]

You mention many records, and that is a relative term.

In my applications, tables with say only 150,000 records are considered very
small, and my applications runs without any noticeable delays.

However, of course a small table of 150,000 records is nothing to search on
if you have a index on the data.

However, your problem is much that you have a linked table. I would
re-check, and consider re-linking to that table, and ENSURE THAT YOU SELECT
the index file also when you link. If you can get ms-access to use the index
when you link, then you still should be able to get decent performance here.

Further, a comb box is not really good for more then perhaps 100, or 150
records. After then, even when you get decent performance, it tends to be
sheer torture to force users to scroll through a few hundred records anyway.
And, on linked tables, ms-access has to load much more data into the combo
as it can't control the data source like it can with native ms-access
tables.

If when you link the table you CAN NOT can get the index to work, then you
likely are in trouble here solution wise.. If you CAN ensure that the index
is working, and the combo box is still slow, then dump the user of a combo
box, and build some type of pop up form that searches the data.

Here is some example screen shots of such type searches:
http://www.members.shaw.ca/AlbertKallal/Search/index.html

And, in the following set of screens, you can also seen a good number of
search ideas again...none of which use comb boxes (as mentioned, they not
for large lists..but only to pick from a "few" entries.).

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm


So, check if you are using the index, check if using the index is fast (just
write a few lines of code to retrieve ONE record from the linked table....if
it is fast, then indexing is obviously being used..and you can then go with
the above search screen ideas. then, if you have tiny tables of 50,000
roads, or a larger table of 500,000 records, searching should be near
instantaneous anyway, even on that linked table.....

 

[Brad Pears]

Thanks for your suggestions.... I will look into them for sure.

Brad