Recordsets via VBA vs. direct table access for multiple users

[Tim]

I am in the process of creating a new database that will house 9 normalized
tables with 3 main parts. First a membership table containing around 30,000
records, a provider table containing roughly 50,000 records, and an encounter
table that contains roughly 450,000 records. I don't know if it makes a
difference, but we are using version 2003.

Currently the database has 450,000 records in a flat file that I will import
into the normalized tables. We have been using this flat file for 9 months
with multiple "record is locked" errors while 5 people are updating different
fields. Another problem I have had to fix is making everyone enter data the
same way and consistently (not leaving blanks). This has been a huge
headache, so I think it will be best to create a totally new database that
has normalized tables and more data integrity checks.

So, my question is this...First, I don't know if this is the correct
terminology, but I can either create forms that access the tables directly or
as many books favor, I can create forms that are "disconnected" from the
table and use VBA to load recordsets into memory, manipulate that, then save
it back to the tables. Which do you think is a better design? Since I am
starting over from scratch (and importing data in later) I can create it
either way. I assume if I access the tables directly, then I will still have
the "record is locked" problem more often than if I use disconnected tables
via VBA.

I would appreciate any thoughts and reasons why.
Thanks!

 

[John W. Vinson]

I am in the process of creating a new database that will house 9 normalized
tables with 3 main parts. First a membership table containing around 30,000
records, a provider table containing roughly 50,000 records, and an encounter
table that contains roughly 450,000 records. I don't know if it makes a
difference, but we are using version 2003.

Sounds like a big step in the right direction!

So, my question is this...First, I don't know if this is the correct
terminology, but I can either create forms that access the tables directly or
as many books favor, I can create forms that are "disconnected" from the
table and use VBA to load recordsets into memory, manipulate that, then save
it back to the tables. Which do you think is a better design? Since I am
starting over from scratch (and importing data in later) I can create it
either way. I assume if I access the tables directly, then I will still have
the "record is locked" problem more often than if I use disconnected tables
via VBA.

Unbound forms can be useful; they're a lot of work and fiddly programming
though, and they may not be all that much advantage. If you're properly
normalizing the tables (so that you don't have to have users updating the same
page or same record all the time) it's much less likely that you'll have
contention. I'm guessing that the Encounter table will be the busiest in terms
of updates, and that you'll be adding members and providers less frequently?
I'd use a Form based on a query which retrieves only one (or a small number)
of records, probably with suitable subforms. For example, you could use a Form
to retrieve a single member, or all members whose names begin with a few
letters typed into an unbound textbox; a subform could show that member's
encounters. Not sure what other tables or requirements you have.

Check out the resources at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

There are some good links to large database "best practices".

John W. Vinson [MVP]

 

[Tim]

John,
Thank you for the links. I will check them out.

The only table that will be updated, daily, is the encounter table. The
other tables really won't change at all, until I load new updates from the
mainframe, which, in the past, has been once every 4 to 6 months. So, 2 to 3
times per year.

I think your suggestion to use a query to limit the amount of records pulled
into the actual form is a good idea. Maybe it will help with contention as
well.

Thanks for the info!
Tim