Multi-user environment

[cabusmichael]

Hello,
I will first apologize for what may appear to be a naive question.

For the database I am working on, an important requirement for the user is the ability to track the last person who edited a table. In this way, the manager is able to track which employee has <or may have> a form related to a table. So, if something is late, she can go to this employee and ask about the status.

The larger context for this project is: I have a friend who wanted a database, but the IT people in her office (she works for the Environmental Protection Agency) decided that they simply don't do databases. So, my friend decided to bypass her IT department, and have me design and implement the database.

The other larger context is, I am not sure how to transition the database to a multiuser environment. I am not sure if we need to get the IT people involved at this point, as the network experts.

I thought of (perhaps) foolish ideas to solve this problem--i.e., require everyone who updates a table to place initials in a field (hypothetically creating a drop-down menu with initials for all workers). But, this may be a huge workaround to a solution that is more obvious and efficient?

Sorry if I display an incredible lack of understanding--and, thanks so much! I started trying to help a friend, maybe I have gotten in over my head!

Michael

 

[cabusmichael]

As an update--just starting to read about splitting the database--understand that is needed!

MC

 

[HiTechCoach]

Welcome to the world of database design:

1) Before you open Access to start building a database you should (I believe this is a must)plan and properly normalized your data.

This starts by having a very clear understanding of exactly what needs done.

See:
DataModel.org: Rules of Data Normalization
MSKB 283878: Description of the database normalization basics
GroverParkGeorge's PDF on Normalization

2) You will also need to decided on a naming convention.

This is popular: Hungarian Notation

Also read:
Sharing a database

The above links are just a few to get you started.

Now you are ready to open Access and start creating your database.

See:
These links

About tracking changes. A common practice is to use an audit trail.

See:
Audit Trail into a separate table (Tracking record edits in a database)

Hope this gets you started

 

[cabusmichael]

I will read the bit on sharing a database...I have actually already designed the database, and implemented all of it. I have studied database design in a master's course (including extensive study of normalization), and took advanced courses--I know about normalization, indexing, SQL, relational algebra and calculus, PL/SQL, the whole lot. I have just never implemented a database for multiple users--that is all I need.

Thanks again!

Michel

 

[HiTechCoach]

In my experience I find it best to build in the multi user handling from the very beginning.

With multi user system, you need a security system that is part of the core design. This will allow you to do the user tracking that you need.

Be sure to look at the last link in my previosu post on Audit Trail.


To see a good example of what you should start with see:

Boiler Plate Database (click here to download)

This is a moderately advance database that incorporates a number of functions such as 1) user logon 2) table reattachment 3) user tracking 4) how to control access via the use of modal switchboards and more. FYI: Username = admin; Password = admin.

from the Utter Angel (Click Here)

 

[cabusmichael]

I don't know that building in the multi-user environment from the beginning makes a lot of sense--the design, the table structure, queries, would all have to be put in place first. To quote an article cited in a previous thread:

"Users often ask at what point in the development process do you split? Well, the answer is you just know!! At the start of the development process, you are creating tables left and right. And, further, at the start you have no users! After a considerable amount of time, that table creating process settles down to a dull roar ... as you near the time when other users will start to use the application, then that is likely when you will split"

This is the point I am at now; the design is good, the tables work with the data I have been given from the company--it seems like, from this article, it would be more work to create a frontend and backend at the start...plus, some of us are doing this for the first time--we would like to think our previous work is not for naught!

I have a friend who is a database developer, who may more sympathetic to me as an experimenter and new to the database world But, I will read the information provided, work it out, and hopefully not have to start over!

 

[gemma-the-husky]

but you dont HAVE to do anything

Access just IS multi-user straight out of the box

so to do things like you are asking you just need to add some fields to each table to record (say)

created by
created on
last edited by
last edited on

and in each forms BEFORE UPDATE event, set these details as appropriate.

now this mechanism only records who did the last change (and this gets overwritten) - so if you need a full audit history, you have to implement this in a different way.

Splitting is different - and since you will be splitting the database, you may as well do this straight away - as there are a very few functions that dont work in a split environment

 

[cabusmichael]

Hi,
Thanks Gemma--and HiTechCoach--

Sorry if I sounded frustrated--I am learning on my own at this point, and that can be a frustrating experience--

I think Gemma's suggestion would work for this office--not that many employees. And, "last updated" is really what is required (from what I understand). But, the audit trail may be helpful for general security--perhaps I will do both...

In a larger sense, I am trying to get experience working on projects in my spare time. In this case, I should be more accepting of hangups along the way...

Thanks again for your help!

Michael

 

[Rabbie]

I may not have understood you correctly but are you going to allow users to change the design of tables. IMHO this would be a recipe for disaster

 

[cabusmichael]

Rabbie,
Oh no, not to change the design of tables! Just make the user input their initials when they change data in the table, so it is known who last input data. I don't think these users would want (or even know) how to change the table designs!

But, I won't be on staff as the database administrator or anything--this is just a part-time project for a friend, just for fun...as I noted, her IT department wants nothing to do with databases, so I doubt even they would change the table design (or, know how to either). Once I get this finished, and hand it off, though, I won't be around the office to administer it or anything--so, they could change things and I would have no idea, unless my friend emailed or called me. So, theoretically, someone could change the design, but that is not my intention!

 

[cabusmichael]

But, you do bring up some concerns I have. I don't have the IT people to help, I am making this at home, using the server where it will be implemented is not an immediate option...

However, my friend needs a database, has no fund to hire someone, and has no help from IT--so, here I am The one upshot is, the office has a small number of staff (being a branch of the EPA), and just a subset of that will be using the database (maybe three people?). This project is immensely fun for me, I just hope the final product will be useful for the context in which it will be used. Thinking about it being used to track grants for projects that will (hopefully) help the environment is pretty exciting...so, I am determined to make the project work within the limitations I have.

The real world is messy--recipes for disaster are hard to avoid--even the great Julia Child had some recipes that turned into disasters (her husband just had a strong stomach).

 

[Lightwave]

Cabus while the user may not need to redesign the tables it is possible that you as designer may need to alter or make additional fields in some of your tables.
As you have probably already discivered table definition is nearly always the first stage in designing applications and potentially every table that you want to record the user against may need an additional field. Entirely feasible to go back and add a field to every table but you will realise once you get into splitting of databases practically speaking you want a situation where you change nothing in the backend and only items in the front end. This is more important where the database is actually in use. So for professionals it is vital.

Tagging as I describe below requires the addition of the kind of fields indicated by Dave(gemma) in what I refer to from here as the backend and where the database was in constant use would require a re-issue of the back end.

If a backend is in constant use then any time spent by you to change the backend would in a large system lead either lead to lost data or alternatively down time when others would not be able to work on the database. Imagine your bank was changing the tables they stored information about your bank account. You happened to put cash in at 1.00pm but you are feeding into information into a database which was backed up and altered at 12.50. At 1.10pm the database administrator overwrites the former backend with a new one. Because you input your information at 1.00pm that payment in will not be recorded. This is why professionals would always look to get table defintion right from the get go.

Since it would appear that this is largely not split yet - it is a somewhat academic as there is no particular negative to changing the back end other than simply being not the best practice.

I did a similar tagging in a recent database. I have separate front ends for each user and the front end contains a very small default table which is unique to the user. When they first set up they record their details in this table. I then used the dlookup command to look up the users name every time they enter the database and set up some public variables. Then as Dave indicates use a before update to record the person altering each field as and when they do.

So depending on how you want to do it
You will
Need the database split
Need knowledge of public functions
Understand the Before update event
Alter tables to add fields as per Dave's post

There are a significant number of steps in each of these which will be specific to your design and your database hence the generalised responses to your question.

Make a back up immediately and then...
Roll up your sleeves and just go fot it.
Provided you have a backup you have nothing to loose and everything to gain

Note this is the way I would do it and may not be the optimum method works for me though

 

[gemma-the-husky]

dont make the user input anything

just get his windows logon, and record that

 

[ghudson]

In the forms Before Update event...

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.txtModifiedBy.Value = Environ("UserName")
    Me.txtDateModified.Value = Now()

... to track who last edited a record and when.

 

[cabusmichael]

Hi,
Quick note--I was not in the best mood Sat. and Sun., so sorry if that became reflected in my posts. I really appreciate the help--this forum is awesome!

All the information is extremely helpful--I will study the examples provided.

So, I will be a better forum member in the future, I promise (including searching through the forum more often, and responding to posts myself)! Many apologies if I haven't been...can't tell you how sorry I am if I haven't been.

Have a great day,

Michael