Using macros with multiple users of database?

I am creating a database in Access 2010 that will be used by multiple people at once. I'm going to split it to create the front end and back end. I will have macros that performs queries to make calculations on some columns of a form. Will there be an issue if two people run the macros at the same time? If so, what do I need to do?
amyamoAsked:
Who is Participating?
 
RemRemRemConnect With a Mentor Commented:
If I understand correctly, you have (your field names may vary):

fldMonday
fldTuesday
fldWednesday
fldThursday
fldFriday

...and you want to display:
fldAbsences.

You might have values of:

fldMonday = X
fldTuesday = T
fldWednesday =T
fldThursday = A
fldFriday = A

Unless you change your values structure a bit, the quickest way to display fldAbsences is for it to be:

=iif(fldMonday="A",1,0)+iif(fldTuesday="A",1,0) + iif(fldWednesday="A",1,0) + iif(fldThursday="A",1,0)+iif(fldFriday="A",1,0)

This formula adds together values of 1 for A values and 0 for everything else, and displays it in the resulting fldAbsences field. Note that this becomes a display only - if you want to be storing the totals, you'll need to record them to a field.

The model you have presently is what's known as "de-normalized" (short version: Not having the same types of data in a single field set...example: If you save customers' addresses and you make two phone number fields instead of a table of all possible phone numbers, you are limited to those two - when a person has a phone, a fax, and a cell to record, you're stuck in your denormalized form). There are times when denormalized is the way to go, and your scenario may well be one of them if it is weekday records for weekly attendance or something...thus my answer assumes you want to keep it that way. If you normalize your data, there are other methods to use.

-Rachel
0
 
RemRemRemCommented:
So long as the users have distinct front ends w/ their own copies of the macros and queries, THAT part won't be the problem. However, if they're trying to update the same records at the same time, THEN you run into locking issues. This'll be dependent on what model of locking you actually use (row/record, block, table, which get progressively more challenging to write across). If each user is limited to use the macro/query on the record they're currently on, however, and you don't let two users edit the same record at once, it should do ok. Worst case then is a "someone has changed this record since you began editing it" sort of message.

My question to you, however, is if the calculations are being done for the sake of the form, and not for reporting or data storage, why are you making them via macro instead of formula or function to display on the form as needed? For example, if you're showing the extended price based on a quantity and unit price being entered by the user, make a text box that has as a control source the formula "=[QtyField]*[UnitPriceField]" instead.

Macros should almost never be used in a database when code or formulas can be used instead.

-Rachel
0
 
amyamoAuthor Commented:
Thank you Rachel -
the reason I'm using the macros is that I have 5 fields that are entered in by the user as either X, T, or A - the "A" stands for "Absent" -

The macros consists of a series of queries assigning a value of "1" to each "A" and then totaling them - would I be able to do that portion right in the form? If so, I would rather do it that way. I couldn't figure out a way to do that using a formula right in the form.

thank you
Amy
0
 
amyamoAuthor Commented:
thank you so much - that is EXACTLY what I needed!
Amy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.