Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1134
  • Last Modified:

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

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?
  • 4
  • 4
4 Solutions
Kelvin SparksCommented:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

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.
GTC-KTXAuthor Commented:
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?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

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:

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.
GTC-KTXAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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: 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.
GTC-KTXAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...

GTC-KTXAuthor Commented:
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now