Solved

Need an informed opinion of database architecture

Posted on 2011-03-24
12
405 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
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 167 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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 57
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
Access query with left expression 5 29
Run Time Error 3075 15 43
Getting Run-Time Error 13 - Type Mismatch 3 26
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

19 Experts available now in Live!

Get 1:1 Help Now