Solved

Need an informed opinion of database architecture

Posted on 2011-03-24
12
402 Views
Last Modified: 2012-05-11
I have a relatively small multiuser database that I built off of the Sales pipeline template that comes with Access 2007.

I began running it in a small, multi-user environment so I used the Database Splitter in AC07 to split it into a front-end and back end.

My network consists of 4 computers all running Win7 with a GB LAN in a small office environment.  One PC serves as a shared files location, and ALL files reside on an external hard drive connected to it.  Each PC maps their MyDocuments to a subdirectory on the drive.

The front end is located in each users MyDouments folder (which is on the external drive), and the back end resides in a shared folder on the external drive.

We have one PC with a 100 base T network card, and all others use 1000 base T cards and the switch is 1000 base T as well.  The office is only 1600 sqft so runs are short.

As I add users, I wonder if this is the best way to configure this database.  It currently has about 60,000 records.

One more thing, two workstations share the same username, so when they login they also share the same MyDocuments, and therefore the same front-end.  Would this cause any type of file locking issues?

We just moved from 1 or 2 people accessing the table at a time, to 3 users.  I don't think this is a very big database, and the network is very fast, so my initial thought is that I should be OK.  However we started seeing some goofy stuff, such as slow responses and records not showing updated until the front-end is closed and re-opened and there are too many records to risk corruption.

I'd like to ask those who are familiar with this type of setup what they think.  Am I on the wrong road, or should this type of setup work ok?

This database is for tracking daily business, and I am only a novice programmer.

Your thoughts?
0
Comment
Question by:callstate
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
Comment Utility
It is never a good idea to "share" front ends as this frequently leads to data loss or corruption.  

Personally, I would put all of the front ends on individual PCs, not on the "file server".  I manage this by putting the latest version of an application on the file server, and then using a technique similar to Tony Toew's AutoFEUpdater, which is free, to keep the FEs on the users machines up-to-date.
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 167 total points
Comment Utility
The problem with sharing front-ends is that Access 'grows' as it's run.  This 'growing' can cause issues (possible mdb corruption) due to concurrent users.  In reality, it may happen with only 2 users.. maybe not. You'll have to experiment.  You could COMPACT AND REPAIR upon closing the front-end app - may help.  In the case of corruption, it's only the front-end.  The back end is not (usually) impacted by front-end corruption.

As far as the back-end - I have systems with 60 users sharing the same back-end.  Noted- they're not hammering on it constantly, but several concurrent users at any given time.  No Problems.

Scott C
0
 

Author Comment

by:callstate
Comment Utility
I am also in the process of re-reading the chapter in a book I own, "Microsoft Office Access 2007 Bible".

An option they suggest is using database replication on the FE, putting a seperate copy of the FE on each user's machine with linked tables to the BE, and using the replication option to update them when a significant change is made to the FE design master that requires updating the client's FEs.

What do you think of that option?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 total points
Comment Utility
<<What do you think of that option? >>

  It's a poor one.   Replication was only good for data.  It didn't handle replication of objects well at all.  And it's very easy to update fornt ends with something as simple as a batch file and a little code.  There are also a ton of tools out there to help with auto updating the FE's.  

Last, replication no longer exists in A2007, so it's a dead issue<g>

 I would also concur on sharing FE's; not a good idea.

<<My network consists of 4 computers all running Win7 with a GB LAN in a small office environment.  One PC serves as a shared files location, and ALL files reside on an external hard drive connected to it.  Each PC maps their MyDocuments to a subdirectory on the drive.

The front end is located in each users MyDouments folder (which is on the external drive), and the back end resides in a shared folder on the external drive.>>

 You need to change this.  The FE's should not be on the server.  Whole point of a split design is that you end up with only data going over the network because the FE's are local for the users.  Only thing that should be shared on the server is the back end.

<<I'd like to ask those who are familiar with this type of setup what they think.  Am I on the wrong road, or should this type of setup work ok?

This database is for tracking daily business, and I am only a novice programmer.>>

  You'll be fine with this setup for quite some time depending on how fast you and your database grows.

JimD.

0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Sadly, Tony Toew's product has not been Free for some time.
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
Replication is not necessary.  You should be fine sharing the same back-end with your users.

Scott C
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Oops, thanks for the update on that Peter.

0
 

Author Comment

by:callstate
Comment Utility
Since I only have 4 PCs, I only need 4 copies of the FE.

Updatiung them is as simple as overwriting their local .mdb - right?

I suppose since all PCs exist in the same office that it's really only a few minutes worth of work to copy a new FE to each machine.

It appears the consensus is to move a copy of the FE to each machine.  Since all of the tables are in the BE, all of the data is stored on the 'server' machine an there should be no problem if one of the PCs crashes and loses it's FE.

I think that's what I'll do - sound right?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Yes, that is all it takes as long as the "file server" is mapped as the same drive on all of the users PCs.
0
 

Author Closing Comment

by:callstate
Comment Utility
Thanks for all of the help!
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
Thanks for the points!  Glad to help.

You can trust my advice.  I've been writting Access apps (for a living) since version 2.0.  I've only twice encountered corrupt back-ends, and that was believably caused by a broken PC (PC was abruptly shutting down due to malfunction.  Probably in the middle of writing data.).

Good luck!

Scott C
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Since I only have 4 PCs, I only need 4 copies of the FE.

Updatiung them is as simple as overwriting their local .mdb - right?>>

  Yes.  As I mentioned, a simple batch file can acheive this.

<<I suppose since all PCs exist in the same office that it's really only a few minutes worth of work to copy a new FE to each machine.>>

  What I do is have a "master" copy of the FE on the server.  In the FE I have a table, called AppVersionControl, which has a record for each new release that I do and why.

  The FE also has a link to the same table in the "master" FE on the server.  When the app starts up, I compare the last record in each table and if they differ, I know at update needs to be done.  In that case I message the user, then have them execute a shortcut which points to a batch file.  The batch file copies a new FE to the station, they start the app again, and now the version numbers match, and the app starts up.

  Might sound a bit complicated but it's not hard at all.  Easy to setup and do.

<<It appears the consensus is to move a copy of the FE to each machine.  Since all of the tables are in the BE, all of the data is stored on the 'server' machine an there should be no problem if one of the PCs crashes and loses it's FE.>>

  Not for the FE, but there still might be for the BE.  Access/JET is client based; that is all the DB processing happens on the PC's.  The server acts as nothing more then a file share.  There is no process running on it that handles requests for clients.

  So if a PC crashes in the middle of writing, you can still corrupt the BE.  However that situation is no different then what you have now as far as the BE is concerned.

JimD.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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

11 Experts available now in Live!

Get 1:1 Help Now