Solved

Using macros with multiple users of database?

Posted on 2011-03-21
4
478 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

695 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