Solved

Using macros with multiple users of database?

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

810 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