Table design & VB

[AHopper]

As a beginner I designed a database that uses bound forms and tables for
different parts of a production process. I used a table for good product and
another table for spoils. I will not go into detail about the process
involved, however, with time the database has become more complicated (200
forms, 108 tables more of both needed) . The database is on a network, split
into a MDE front end MDB backend, with Access Runtime on approx 30
workstations. Making corrections, creating reports is complex. The integrity
of the data is verifiied by the events on forms. All saving is done by
command buttons when the user indicates the record is ready to be saved. I
have not used many modules. Presently I am being adviced to design using
unbound forms and to combine data into fewer tables. Information I now have
in one bigger record will be split into several records. I have been told
that the database will be less complicated if I do this. I would need to use
VB to save, edit, delete etc.. I would use temporary tables (which I do not
know how to do) to create reports. The goal is to eventually change the
database to SQL backend Access front end. It is easier for me to continue
designing as I have been, since I will need to learn how to use more VB, and
SQL queries if I follow the new design suggestion. Much of my existing design
will need to be reworked and eventually replaced. I am willing to do that if
it is the right or better way to go.

I would appreciate any advice on
1/ whether to pursue the new approach
2/ other suggested design approaches
3/ how/where to learn VB, SQL at this new level.

It has been the support of the discussion groups that has helped me get as
far as I have.
Thank you in advance for taking time to help.

Allan

 

[Tim Ferguson]

On Tue, 9 Nov 2004 05:49:02 -0800, AHopper

Not sure that I understand all your problems, but here goes with my
tuppence-worth...

(200 forms, 108 tables more of both needed) .

Not a trivial application then...

The database is on a network, split
into a MDE front end MDB backend, with Access Runtime on approx 30
workstations.

This is pushing Access to its limits probably, but should be okay-ish.
Hope you have lots of backups!

Making corrections, creating reports is complex.

That is why it pays to get the design correct first time round!

The integrity
of the data is verifiied by the events on forms.

Ummm: if this means instead of using db-level validationrules, referential
integrity, etc. then I would be really worried.

All saving is done by
command buttons when the user indicates the record is ready to be saved.

Why? The usual Access Forms events are pretty bulletproof for most
requirements.

Presently I am being adviced to design using
unbound forms

Cannot think why. The words "wheel" and re-inventing" come to mind.

and to combine data into fewer tables.

Cannot comment on this. If you really need to model 100 different
entities, then you need 100 tables. If the underlying design is badly
wrong, then you need a Major Overhaul.

I have been told
that the database will be less complicated if I do this.

It's not about complexity, it's about correctness! Although there are
usually several "right" designs for a given real life situation, there are
many times more wrong ones.

I would need to use
VB to save, edit, delete etc..

Bolox. Very rare that Access forms don't work well when left to their own
devices.

I would use temporary tables

I'm a purist, and regard temporary tables as the spawn of the devil. I
also recognise that sometimes the devil has to have some spawn!

The goal is to eventually change the
database to SQL backend Access front end.

Probably a good idea with that many users and a complex data model. Better
security too. You have a number of paths you could look at: ADP projects
(specifically designed for SQL Server; practise on MSDE first); proper web
forms (ASP is not so different from VBA that you are used to); using your
present MDB/ MDE with ODBC connectors; etc.

It is easier for me to continue
designing as I have been, since I will need to learn how to use more VB,
and SQL queries if I follow the new design suggestion.

I think it is fair to say that very little can be accomplished in Access
without using any VBA; and SQL is a must to get the data you really want,
especially if you want your network manager to keep you on his christmas
card list.

Much of my existing design
will need to be reworked and eventually replaced. I am willing to do
that if it is the right or better way to go.

Don't forget that all applications have a life cycle and need to be
reincarnated every so often. If this is a database on which your business
depends (and the livelihoods of workers, staff, clients and patients,
etc.) then you need to stay well within your sphere of competency.

Hope that makes some sense

Tim F

 

[AHopper]

Tim
Your comments are very helpful.

Thank you

Allan