Database management using forms...

Database management using forms...

Post by Lyndo » Tue, 05 Sep 2006 22:08:21

Hi all,

This may seem like a silly question given Access is a database
management tool, but I am working on a project to develop a change
management tool that will allow users to request changes to certain
data values within tables in a production environment database. The
change management tool will have a local copy of the database that will
allow the administrators to make changes to the data in the local
database and then generate an output file that contains the necessary
SQL update queries to update the production database after the changes
have been implemented (similar in concept I suppose to CVS where the
local copy is used to update the baselined version). Why this
particular architecture was adopted is not for me to comment on, I am
just building the system.

The administrators of the system have little to no database knowledge
so the requirement is to build a front end system using Access forms
that allows the local Access database to be manipulated as described
above. Typical operations would be to update a row of data, insert a
new row or create a new table. What I am basically required to do is
build a dumbed down version of Access using Access forms and VBA that
facilitates and validates these basic commands. I am wondering if
anyone knows of any sample projects or APIs that would assist in this
regard... I would like something that coupld possibly generate a
graphical representation of an Access database and display that in an
Object or Image frame and then allow tables to be opened by double
clicking or something. Any help would be greatly appreciated...

Many thanks,

Database management using forms...

Post by Albert D. » Wed, 06 Sep 2006 06:14:53

I don't really think this is much possible.

I mean, if they add a new field to a table, then the 100's of lines of code,
and even forms, and even reports would have to modified to show that extra

And, what kind of training does it take to learn how to add a new field to a
table? (answer...very little!!!).

I mean, I used some 4th GL systems in the past, but at the end of the day,
any changes of value required some coding.

You *can* make some things easier. for example, I built a security screen
and manger. While my code still uses the built in security system, for sure
placing some forms and menus on top of ms-access, the process became MUCH
MORE easy.

Here is the screen shots and article as how I did this:

However, the above does not involve changing the design of tables, or
database structure. further, the built in screens that allow you to modify,
or add fields to a table is ALREADY VERY EASY. So, while my article is great
on making the UI easy, that example does not modify the database structure.

The instant you jump into building something that changes its design as you
move along is the instant that your users must become developers. IF you can
figure out a way to avoid this problem, then you will be the next Bill
Gates. This concept is just not really possible, and not seen anything that
would do otherwise in my long career in this industry.

Further, why should users create tables? How can you have any management
system if new tables are being created all the time? This means that NO
structure can exist. How can historical data and reporting occur if the
current tables and designs are in a state of change?

So, I not exactly sure what you are looking for, but a system that relies on
new tables, and changes in table designs is really DOOMED to failure.

It seems to me the problem is that no one wants to sit down and define the
problem that needs to be solved. so, you are being asked to make something
that does not requite any problem solving!!!

I would figure out what they need to accomplish, and then build system that
accomplishes this task. That is the way the industry has worked from day
one, and I don' see how you can change the need for good designs by allowing
users to make tables.

Creating tables in ms-access is not the hard part. Having good table
designs, and understanding a relational database modeling is most certainly
an issue here....

I might be missing your question, but I just don't see how history data and
reporting can occur when table and designs are in a constant state of
change. How will past data and history be managed in such a system?

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada