Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need an informed opinion of database architecture

Posted on 2011-03-24
12
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 48

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
Independent Software Vendors: 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!

 
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 48

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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