VERY large lookup table

[mscertified]

Is there any advantage (or disadvantage) to putting a very large lookup table
in a separate mdb file? This table currently has over 8000 rows and I am
being asked to add 17,000 more rows. This table will be accessed by pretty
much every user every time they use the database. It's main use is to
populate combo boxes i.e. only one row will be selected at a time. Thanks.

 

[Klatuu]

No advantage or disadvantage that I know of unless it is needed in the table
relationships. 9700 rows isn't really big. I have tables with over 350,000
rows. It really is only a matter of the mdb file size limit of 2 Gig.
The only issue I see is that a combo lookup table with 9700 rows may be a
performance issue if you are on a slow network.

 

[PatHartman]

If possible, keep a duplicate of the lookup table in the front end (assuming
each user has a local copy of the fe). The main copy of the table resides
in the be and is used for RI. The local copy is only used to populate
combos. However, you are getting to the reasonable limits for a combo
even when the source is local. One techinque people use is to separate the
list by first character so you can choose a-z or 0-9 to reduce the initial
size of the combo list.

 

[Klatuu]

If records will be periodically added to the lookup table, I would not
recommend keeping a local fe copy. That would be setting up a maintenance
nightmare.

 

[Dale Fye]

Nightmare?

Dave, it's really only two queries. One to delete the records that are
still in the local copy and no longer in the backend, and one to insert new
records. Maybe a third if there are individual fields that might change
(although you could tackle the update and insert in one query UPDATE/INSERT
query in Access (don't think this works with SQL Server).

I do this all the time, although that may be because some of my users are
connected via a wireless network, and I really need to keep the network
traffic to a minimum. Its even easier if you have date/time stamps in the
tables.

Dale

 

[Klatuu]

Dale,

The wireless issue make that valid. The main problem I have is keeping all
the fe copies in sync. For example, if User A adds a new entry, there will
be a delay before user B sees the change and could possibly add an indentical
entry. How do you manage that?

 

[Dale Fye]

Generally, I update the backend immediately if the users actually add a
record to the lookup table in question; but most of the time, the backend
lookups that I am using are managed by the applications admin user, so they
update the backend table, and the front ends get updated when my splash
screen first comes up. I display a message on the splash screen and disable
all the buttons until the front-end updates are complete.

I also generally store a FE_Updated field in my local_Parameters table, so I
can add that to the WHERE clause in my update queries.

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Okay, so it is not truely dynamic, then.
For it to be dynamic, each time a user added a new entry, the backend would
have to be updated. But for the other users to see the change, the combo
would have to be periodically requeried.

 

[Dale Fye]

You are right, but if you have a "lookup table" that has 25,000 rows, would
you let just anyone add records to it?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Hard to say. It would depend on the business rules. I would try to avoid
having such an table, but if it were an absolute necessity, I would consider
implementing this approach:

http://allenbrowne.com/ser-32.html

from Allene Browne