Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need an informed opinion of database architecture

Posted on 2011-03-24
12
Medium Priority
?
421 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 49

Accepted Solution

by:
Dale Fye earned 668 total points
ID: 35207969
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 668 total points
ID: 35208133
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
ID: 35208209
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 664 total points
ID: 35208281
<<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
ID: 35208327
Sadly, Tony Toew's product has not been Free for some time.
0
 
LVL 20

Expert Comment

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

Scott C
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35208379
Oops, thanks for the update on that Peter.

0
 

Author Comment

by:callstate
ID: 35208386
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 49

Expert Comment

by:Dale Fye
ID: 35208483
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
ID: 35208488
Thanks for all of the help!
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35208614
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 58
ID: 35208767
<<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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

916 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