Difficulty in creating a database for multiple v. single users

Factual Questions
1

I am about to tell a client that they should be using a database for a particular task (creating dissemination lists). They’re very likely to agree, and ask me to create it.

I’m proficient enough to create a database for one user (or many users with separate files), including basic functions using macros and VBA. What I’ve no experience in, is creating a database in which multiple users can add and delete records from multiple locations. Given the client’s constraints, this will be done in MS Access 2000.

Is writing a database to be used by a group of networked computers (i.e., not over the Internet, but over the organizations’ intranet) significantly more difficult? That is, does doing so require a lot more than figuring out a handful of new terms, wizards, and associated code? Of course I expect there to be a learning curve, but is this a whole different world or just an extension of database basics? If it makes a difference, the particular office would have about fifteen users, and the total database would have under ten thousand records.

How far over my head am I about to get?

Thanks,

Rhythm

[sup]I put this in GQ thinking that though there is no factual answer per se, it is still somewhat concrete – as in, yes, there are entire books devoted to networking databases, or no, Access has XY&Z functions easily accessible. Of course, I apologize if this is the wrong forum. [/sup]

2

It’s not really all that hard - Access supports multiple users by default. A very simple solution would be just to put the database on a network share, and have people open the file directly. That’s not really the best solution, though. It would be far better to have a back-end database file on the server (which contains the tables and queries), and have another MDB file which is the front-end which you can copy to each machine (which contains reports, forms, macros and the like). The front-end needs to link to the back-end tables, which is again pretty straightforward. The only problem I’ve found is that making design changes can be complicated, because you need to make sure everyone is working with the latest copy of the front-end. This page is an introduction to sharing Access databases on a network, and I can find more links that I used if you need them.

A more complicated solution involves database replication, which definitely introduces complications but also opens more possibilities (eg local copies on laptops which can then be taken home). I don’t think you need this at the moment, but again, if you do, I can definitely dig up some links.

3

**Dervorin ** has addressed the architecture and logistics; when I read your question I was thinking more about transaction management. How many different tables are there? If the schema is very simple, like one big table, then you are probably not going to have to worry about that.

Transaction management involves scenarios like having multiple interactions with a user to build records that will be modified in multiple tables, and it might take minutes to do it. Then you have to roll back if something fails. A simple example might be an ecommerce purchase where the user creates an account, gives you credit card info, and then you create an order. At checkout time you find out that item is out of stock, then you have to reverse (roll back) the transaction.
Another issue is concurrent access, where another user might be doing something that could affect the same records during the same time.

I am not terribly familiar with Access but if your application is fairly straightforward Access probably does a lot of table locking under the covers so you don’t have those kinds of issues.

4

Have you considered building an ASP Web site with HTML forms and letting people edit the data that way? The database would stay in one place and no one would access it directly. Multiple users can update it in real time. If it’s on public Web space with password protection (either ASP-based security or NTFS/Windows permissions) then it can be accessed by anyone with login credentials from anywhere in the world - thus avoiding the need for network access to get to the DB.

Of course that is just one way of doing it and not necessarily the best or the worst way.

5

You could write software without much attention to multi-user needs, and it might work, but it won’t be very good and it will frustrate your users no end when they don’t know why they can’t do stuff they expect.

It’s easy if you know how. My expertise is not in Access, but unless the basic concepts have changed, it shouldn’t matter.

Basically, you need to assume that:[ul][li]To avoid mass confusion, files will need to be locked at some time or other, which will prevent other users from accessing or updating anything. You need to keep locking at an absolute minimum; use record locking instead of file locking where possible, and make sure if any operation doesn’t complete or a user walks away from their desk, the file does not remain in the locked state for more than the minimum time.[]You need to program so two users cannot update the same record at the same time, since user #2’s updates will wipe out #1’s without warning.[]Each user needs to be sure that if he is looking at a record and someone else updates it, his view refllects that change as soon as possible.Operations that must be done with a file locked for some time should be scheduled for times that minimize disruption.[/ul]Many database management programs have built-in routines that will avoid corrupting files, but I don’t advise relying totally on them. The ones I have seen are not robust or user-friendly.[/li]
Some examples of routines I developed, which I will illustrate in pseudo-language code…wherever you might say “open file”, I would substitute something like this (this is bare-bones, no fancy stuff):


Check to see if file is available, if not, -> error routine
  else check to see if record can be locked, if not ->error routine (or wait)
    else lock record, read data to two temporary memory arrays, unlock record, display data to user, accept edits to one array.  
  If user says to save edits, check to see if record can be locked again, if not -> error routine
  else lock record, read record to 3rd array.  Compare 3rd array with original array.  If different, this means the record was updated by someone else.  Unlock record and handle that.
    else write new data to record and unlock record.  Report success to op.

Get the picture? Note that at no time did I say “lock record and wait for op to do someting…” because, sure as shootin’, he’ll take his lunch break right then and the whole company is held up.

Once you develop and troubleshoot a set of routines, you can plug them in where code would normally say “read file” or “write record”. Then it gets easier.

Also watch out for deadlocking, where you have one file locked and are waiting for another to be locked, but it can’t be. Deadly.

6

Wow, fantastic! Thanks for the replies. It looks like there’ll be a learning curve, but not one I should be daunted by or need to allocate a large amount of time for.

Thanks!!!

7

[QUOTE=Rhythmdvl]
Wow, fantastic! Thanks for the replies. It looks like there’ll be a learning curve, but not one I should be daunted by or need to allocate a large amount of time for.
[/quote]
Well..if you’re really serious about taking the time to learn and do a good job, I suggest you try some basic operations first. And be sure you have at least two computers side-by-side running the same program so you can test for conflicts. Three is better – one to program/analyze, two to simulate multi users.

When I first got into this, I ran simulations overnight to see how often two users might randomly conflict and how bad it could get if they did. From the programs I see around these days, I doubt if many would pass the test.

From then on, using the routines I had debugged, I had very few problems until CPUs got much faster. Then I found I had to rerun some tests and fine-tune some parameters that didn’t exist before!

8

I have been developing software professionally, based around the Office suite, for many years now. Access is my favorite tool. It is perfectly capable of handling what you say you want, and most of the advice in this thread is spot-on. Not to seem too harsh here, but Musicat’s advice is way too in-depth for your purposes, and most of it doesn’t apply to Access since the database engine will handle all of that for you. (Sorry, Musicat!)

The locking is about the only thing that DOES apply, but don’t worry about the mechanics of it - Access will handle that pretty gracefully. The remaining issue is when Joe Average goes to lunch after modifying a record, but hasn’t saved it yet. To get around this there are a bunch of methods, but a possible quick one would be to use the built-in form timer and have it undo the changes after a pre-set time with no input, and close the record. For myself, I tend to have the app pop up a message and wait a bit longer when this happens before undoing all the changes, just in case they’re tied up on the phone or whatever and are still there.
Here are some more suggestions:

  • If you’re talking multiple users on a LAN, then the prior advice about seperate front-end and back-end databases is precisely what you want. Keep the tables in the back end, everything else goes in the front, and link to the data.

  • Start off by building in the ability to handle updates. All that’s required is a table that stores the current ‘version number’; when you make an update to the front end, update the version number. Each front end should, upon starting, check that version number against the one stored internally. If they don’t match, pop up a message about updating, give 'em a link, and shut down. This will prevent most of the potential pitfalls in having multiple copies of the front end around.

  • Replication is a great concept, but in reality it is trickier than it looks. Unless you REALLY need the capability, I would suggest against it.

  • Force yourself to stick to the no-spaces rule. By this I mean do not use ANY spaces in any object names in the database - tables, queries, reports, forms, whatever. Forget the space bar exists. This will save you headaches later on when trying to construct queries or use objects from within VBA code. By the same token, don’t use dashes or other ‘normal’ punctuation. An underscore is fine, if you really need a seperator in an object name.

  • Validate your input! Use forms instead of parameter queries (a p-query is where you put a field in that requests input when you run the query; they’re bad, mm’kay?) when accepting criteria. Make use of the Validation Rule property on input controls - text boxes, etc. Make sure Limit to List is turned on for combo and list boxes. That sort of thing. Every user added increases the chance of bad input exponentially.

  • Develop your own naming scheme (that sticks to the no-spaces rule!), and use it religiously. A common one is to put ‘frm’ in front of every form name, ‘tbl’ in front of every table, and so on. This lets you identify precisely what TYPE of object you’re using at a glance, which is easier to lose track of than it sounds.

I’ve got plenty more, but this should get you started. If you run into specific problems, don’t hesitate to ask.

9

:eek: Oops! I certainly didn’t mean to minimize things!!!

That is, I recognize that there will be a learning curve, but it sounds like one I’ll be able to climb without losing site of other work.

10

How far (timewise) from the server is the farthest client? IIRC Access had a big problem with extended latencies. LAN ok; WAN not ok. Sofaspud can probably give chapter and verse. Also, for how many users is this? If it’s for many people, could you use Access for the front end and one of the free SQL servers for the back end?

11

[QUOTE=Quartz]
How far (timewise) from the server is the farthest client? IIRC Access had a big problem with extended latencies. LAN ok; WAN not ok. Sofaspud can probably give chapter and verse. Also, for how many users is this? If it’s for many people, could you use Access for the front end and one of the free SQL servers for the back end?
[/QUOTE]

I have Access databases that run over a WAN quite successfully. I have a front-end at each remote site, and the shared back-end at my site. The key is to minimize the data transfer; for example, data tables that are static (that is, that can’t be updated by the users) are kept as individual copies in each front-end, and field sizes are kept as small as possible. (Don’t use Memo fields if you do this!) I have batch files that copy the master front-end from my test server to each location’s production server each night (overnight, when nobody is using the application). This lets changes I make to those static tables (and programming changes to the forms, etc.) propagate out to those users, and also automatically shrinks the front-end (Access databases’ system tables grow as they’re used, sometimes excessively) by overwriting it with a front-end that’s not regularly used and is regularly compacted.

Some rough numbers to keep in mind:
Number of users that can simultaneously use a single combined front-end/back-end .mdb file: about 5.
Number of users that can simultaneously use a single front-end, with a separate back-end file: about 15, maybe 20.
If you have more users than that, they should each have their own front-end, or you should divide the users into smaller groups that each share a front-end. The theoretical maximum number of simultaneous users of a multiple front-end/shared back-end Access system is 255. I’ve never pushed it that hard, but I’ve had well over 100 users sharing (simultaneously) a single (very carefully designed!) system of this kind.

In all cases, you should have backup copies of the front-end. It will occasionally get corrupted, sometimes in a way that’s very difficult to recover, and you’ll need to restore from backup. If you split the database, you should virtually never encounter back-end corruption.

(This has been my life for about ten years now, for two different companies. Just for the record. :wink: )

Number of users that can

12

Very odd. The hamsters replaced my .sig with part of my message. The little devils!

13

[QUOTE=Quartz]
How far (timewise) from the server is the farthest client? IIRC Access had a big problem with extended latencies. LAN ok; WAN not ok. Sofaspud can probably give chapter and verse. Also, for how many users is this? If it’s for many people, could you use Access for the front end and one of the free SQL servers for the back end?
[/quote]

Access used to be a lot worse about handling latency than it does now, back in the Bad Old Days when MS had the engine handling file transactions internally rather than relying on the OS. That was many moons ago, however; these days, if your filesystem doesn’t time out, Access won’t. However, it may run like a lame pig on a hot day in quicksand unless you design it properly. More on that below.

As for the free SQL backend suggestion, absolutely! Access is perfectly capable of connecting to just about any data source, and I can even provide links and tips for using DSN-less connections (which make deploying the front end a WHOLE lot easier, let me tell you).

[QUOTE=SCSimmons]
…

In all cases, you should have backup copies of the front-end. It will occasionally get corrupted, sometimes in a way that’s very difficult to recover, and you’ll need to restore from backup. If you split the database, you should virtually never encounter back-end corruption.

(This has been my life for about ten years now, for two different companies. Just for the record. :wink: )
[/QUOTE]

Dude… are you me? :slight_smile:

One trick I’ve employed to keep front-end corruption to a minimum is to employ a every nth run automatic compact and repair (where ‘n’ is an arbitrary figure I pluck out of thin air). Most users won’t even know where to find the Compact and Repair option, and will be scared to use it if they knew where it was. You can trigger it (silently, even!) with VBA code. This will take care of the little housekeeping bits that MS doesn’t quite get right, and keep your headaches down at the same time.

To reinforce SCSimmons’ suggestion, do NOT use Memo fields. If you absolutely must (for a field that will regularly require more than 255 characters, for example), then split that field out to its own table and reference it only when needed. I can provide more details if needed. The key really is in data transfer; train yourself to think about what each transaction – each hit on the back end – really entails before you design a form and you can eliminate most bottlenecks before you even enter the actual development phase.

Some key points re: data transfer:

  • Filters are bad, mm’kay? When you use a filter on a form or report, what Access is really doing (in most cases) is requesting ALL the data that fills the basic criteria, then narrowing that down on the front end. Use dynamic queries instead (not the same as parameter queries, by the way).

  • Searching records via the built-in controls is also bad, for pretty much the same reasons. Access isn’t smart enough to construct a query based on your search criteria, so what it does internally is essentially filter it. You won’t notice this if the data is stored locally; across a LAN or WAN, you’ll have time to go make a cup of coffee while it thinks.

  • Design your forms to retrieve and manipulate one record at a time, wherever possible. This way, you’re not locking records you don’t need to (worst case, the whole table!), and you’re not transferring data you don’t need to either.

Access really is a good solution for low-impact (and even some high-impact) scenarios. Designing and maintaining multi-user databases with it is a great way to learn the intricacies of database management and design. Good luck! (And don’t hesitate to ask if you have questions!)

14

[QUOTE=Sofaspud]
I have been developing software professionally, based around the Office suite, for many years now. Access is my favorite tool. It is perfectly capable of handling what you say you want, and most of the advice in this thread is spot-on. Not to seem too harsh here, but Musicat’s advice is way too in-depth for your purposes, and most of it doesn’t apply to Access since the database engine will handle all of that for you. (Sorry, Musicat!)

The locking is about the only thing that DOES apply, but don’t worry about the mechanics of it - Access will handle that pretty gracefully.
[/quote]
You’re probably right, I didn’t take offense! :slight_smile: I developed some of these concepts over time, since unlike many developers, I was close to the application’s actual use by real people, and I took notice of what frustrated them and what made their job easier. If you can accept the defaults in Access, great – just be sure you know what they are and what the consequences will be!

Another hint, not related to multi-user programming specifically, but a pet peave of mine – be kind to your users. Parse EVERY input for allowable characters and valid data to avoid problems later (and tell them exactly why the input isn’t accepted and how it can be fixed), and don’t force them to enter data in a rigid manner if you can bash it to fit with a simple line or two of code. Example: I use different programs that ask for a tax parcel number. Some REQUIRE a dash in the middle, some PROHIBIT the dash, and some accept it but don’t return the record you want. Some will give you an error if you put in a leading space or zero, others INSIST on a leading zero on occasion. Don’t do this to your users – how difficult is it to normalize the entry, stripping off leading/trailing spaces and compensating for internal dashes? Only a lazy programmer ignores these factors!

15

[QUOTE=Musicat]
You’re probably right, I didn’t take offense! :slight_smile: I developed some of these concepts over time, since unlike many developers, I was close to the application’s actual use by real people, and I took notice of what frustrated them and what made their job easier. If you can accept the defaults in Access, great – just be sure you know what they are and what the consequences will be!
[/quote]

Fortunately, Access has become very standardized over the years, and the defaults are pretty good for an out-of-the-box solution. Granted, your user isn’t going to understand them, but the choices it GIVES the user can rarely cause integrity issues (for example, it typically won’t overwrite lock conflicts, but instead offer to reload the changed data and start fresh). In short, Access tends to follow the ideal path whenever it runs into problems.

The good news is, you can take over at any point and override the defaults however you like. This means that you can replace (for example) “ODBC connection error” with “I can’t connect to the server right now. Please contact your support team.” Or whatever, obviously.

Absolutely correct! This is applicable in ALL programming, but is especially unforgivable in Access, where the tools you’re given come complete with automatic validation routines that you can customize for your needs. In fact, Access offers the ability to define a validation rule set (and warning message) for each field at the TABLE level, which carries down to every control bound to that field on each form, automatically. VERY handy stuff; define the rules once and let it go happily on its way.