Solved

Using macros with multiple users of database?

Posted on 2011-03-21
4
472 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:amyamo
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:RemRemRem
Comment Utility
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
 

Author Comment

by:amyamo
Comment Utility
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
 
LVL 7

Accepted Solution

by:
RemRemRem earned 125 total points
Comment Utility
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
 

Author Comment

by:amyamo
Comment Utility
thank you so much - that is EXACTLY what I needed!
Amy
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now