Solved

How to Deploy MS Access Application so multiple offsite users can update simultaneously

Posted on 2009-05-04
10
1,104 Views
Last Modified: 2013-11-29
I have a sales database which i've been developing that will track sales opportunities of our salesforce.  The salesforce is obviously located in the field, and will rarely be in the main office.  They will therefore be updating their data from home (likely on a nightly basis).  I'm sure there will be many times when multiple salesmen will be trying to update at the same time.  I'm trying to figure out what the best way to implement this is.  I'm relatively new to Access and have not implement anything yet that needs updated by multiple people.

As far as remote access for the salesmen, they have two ways that they log on to our server.  For email, they log on to Outlook Web Access.  If they need access to our server or an office computer, we do that through Remote Web Workplace (SB Server 2003).  They have never really had the occasion to do much more than the Outlook Web Access until now.

Any help on how to implement this would be appreciated.  I guess the main thoughts/questions  i have are as follows:
1.  how do i properly account for the fact that multiple users may be trying to update or open the database at the same time?
2.  Do i need to worry about users corrupting the form or database?  is there anything i should do about that?
3.  Also, i've read a little bit that Access 2007 (which i am using) has some sort of e-mail update capabilities.  Not sure how robust this functionalitiy is and if it would work with a fairly complex input form that has multiple tabs and subforms.  If this is a viable possibility for me, i'd like to know if its the best way to pursue.

Anything else that i haven't thought about yet or didn't mention?
0
Comment
Question by:GTC-KTX
  • 4
  • 4
10 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 125 total points
ID: 24299460
Remote Web Workplace is fine using SBS, but you'll need PC within the internal network to connect to (one per concurrent user).

Each PC will need a copy of Access installed (or at least a run time). The database MUST have a fornt end/ back end spilt (i.e. back end its the tables only, front end - the rest)You need a batch file or simple Access appn that grabs a master copy of the front end and copies it to the local drive. They work on this. Access' record locking should manage multiple users OK.


Kelvin
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 375 total points
ID: 24299508
So you'd have on the road salesmen, who would NOT be connected to the office, entering data and such in the field, and at some point you need to synchronize those databases with the main one at office? This cries out for replication, but unfortunately Access 2007 doesn't include replication as did earlier versions of Access.

You can move the database to SQL Server Express, which is free and does support replication. Each road warrior would have a SSE installed on their machine, and those users would synchronize the SSE databases at some point. Of course, replication databases are only as "fresh" as their last update, but then that's true of your road warrior machines also.

SSE Replication topic:
http://msdn.microsoft.com/en-us/library/ms165700(SQL.90).aspx

If users do NOT need to work with the db throughout the day, and ONLY need to update items when they get back to their home/hotel, then you can setup a Terminal Server to handle this. Users would log into the TS box, and they'd run a copy of the Access db just as if they were on the local office network. However, if your users must use their laptops in the field, then this just won't work - you'd need to look into replication, IMO.

Just note that replication is typically a technology you build in from the ground UP, not after the fact. Far too often we get these sorts of questions after the application has been finished, and unfortunately that makes the implementation of replication much more difficult.

The "email update" functionality of 2007 is not ready for prime time, IMO. I've seen faaaaaaaaaar too many posts here and on other forums where users had quite a bit of trouble with this feature. IMO, until such time as this have been in the wild for a bit, I'd stay far away from it.
0
 

Author Comment

by:GTC-KTX
ID: 24305186
kelvin,
A few questions on your post:

>The database MUST have a fornt end/ back end spilt (i.e. back end its the tables only, front end - the rest)
Can you elaborate on what this means?  How i do it?
>You need a batch file or simple Access appn that grabs a master copy of the front end and copies it to the local drive.
Again, kind of new to Access, so can you give me some more detail on this as well?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 375 total points
ID: 24305289
A split database is one where you have two files: The Backend (which contains Tables only) and the FrontEnd (which contains everything else). You would deploy a copy of the FE to EACH user, and all of those FEs would connect to the SAME BE. It's similar to a client-server setup, although Access/Jet is not a server-type database.

Access provides a splitter wizard to do this for you:
http://office.microsoft.com/en-us/access/HA102400961033.aspx

As to the batch file, I'm not overly familiar with Web Remotes, but if it's similar to TS, then you'd have a simple batch file on the user's Remote desktop that would copy a new FE and launch that FE:

@ECHO OFF
copy "source" "destination"
run "destination"

Still, this doesn't account for situations where users would need to update their databases "on the road" and then synchronize them when they get back to the office/home. If you have this sort of a setup, then no remoting technology will really work for you, unless you provide your on-the-road force with wireless connections and use TS or Web remote through that connection.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:GTC-KTX
ID: 24305296
LSM,
How difficult is it to move this to SQL Server Express.........or SQL Server (I know we have it)?

The Terminal Server may be an option.
I dont totally understand it as you described.  I dont think its a problem if they wait to update all of their calls until they are at home or the hotel.  If thats the case, how exactly would it work?
Would it simply be a local copy of the entire Access file, including all tables and forms etc, that is simply synched up somehow?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 375 total points
ID: 24305604
If they can do this - update all their calls from home/hotel - then you can certainly use Terminal Server, or REmote Web Workplace (which is apparently a Web portal that would allow your users to use a Remote Desktop, just like TS). This would allow you to keep the app in Access (although SSE is certainly a good idea, regardless) and give your OTR users the ability to remote into the office after their workday is over.

Using remote technology, users would log into the server (either via the Web portal, or through the Remote Desktop Client app), and would then be presented a "desktop", just like you see when you fire up your local workstation. The difference is that "desktop" is being run on a server somewhere, and that server is (basically) passing back screenshots to you ... you send keyboard/mouse input to the server, the server processes them, and returns to you new screenshots. This typically works well, assuming your OTR users do NOT need to do this during their sales calls ... if they do, they'd have to depend on either (a) access via their customer's network or (b) access via some sort of dialup scenario (like a wireless cell card or something).

If you have a Microsoft Server (i.e Server 2003) setup, then you have Terminal Services available. You'd have to purchase CAL's for those Terminal Server users, of course, but it's a reliable, robust technology and is in use by many of my clients, including NASA/Goddard and NASA/Kennedy.

In specific regards to moving to SSE or SQL Server - if you're running 2005+, MS provides the SQL Server Migration Assistant (SSMA: http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx) which does a very good job of moving your data to SQL Server. In many cases, there is little else to do other than run the SSMA utility. Of course, you can move much of your code to the server in the form of Stored Procedures, but that's not entirely necessary.
0
 

Author Comment

by:GTC-KTX
ID: 24305674
Thanks LSM,
The description you give for Terminal Server sounds exactly like Remote web workplace.  For Remote web workplace, i type in a web address to my server from any machine that has internet access.  I then choose which of the office PC's i want to log in to.  Once i log on, i see that particular office machines desktop from my remote location.  Are they the same?  If they are different, do you see a benefit to using one over the other?
0
 
LVL 84
ID: 24306182
It is similar, but my understanding of Remote Web Workplace is that you can connect to a single machine on the network, and use THAT machine as if you were sitting in front of it. Terminal Server supplies you with a true "remote desktop" ... that is, TS is NOT tied to a specific network machine, and doesn't depend on other network resources to provide that desktop. With RWW, you must connect to a specific machine in order to see that machine's desktop ... TS doesn't have this limitation, and can serve up many concurrent users at the same time. RWW can connect ONE user to ONE network machine ... so if I use RWW to connect, and the local workstation I want to use is not on, I can't connect. With TS, I'd get my remote desktop as always, regardless of whether any other network resource was running.

If your OTR users all have a specific "local" office machine, then they can use RWW to connect each user to THAT machine. If they do NOT, and you are expecting them to share the same "local" office machine, then I suspect you'd have some complaints if UserA is logged in but UserB needs to log in as well (since RWW would deny the second user a login), but I suppose this would depend on the user volume and such ...

0
 

Author Comment

by:GTC-KTX
ID: 24306325
LSM,
Sounds like TS is what i want then.

With TS, would i still split the database like was discussed in previous posts?

For someone relatively new to Access, is TS hard to implement?
We have an external IT consultant that is available for things that i can't handle, but i'd rather avoid that expense if i can figure it out.

How can i learn exactly how to implemnt TS?
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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.

708 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

13 Experts available now in Live!

Get 1:1 Help Now