Solved

Using macros with multiple users of database?

Posted on 2011-03-21
4
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:RemRemRem
ID: 35181040
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
ID: 35181146
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
ID: 35182020
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
ID: 35182118
thank you so much - that is EXACTLY what I needed!
Amy
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

738 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