Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm looking for some advice specifically related to MS Access 20003/2007 (sidebar: we are in a multi-year long transition from 2003 to 2007.. ugh). In short... what I'm wanting to find out is 3 things: 1) Quickest 2) Safest 3) Quickest and Safest... write methods for remote databases users.

    Here's the scenario: I work for a large corporation that has a large remote user community spread across the US (about 75 users) along with an in-office user set (about 40). We have developed a reporting tool/db for all the users for obtaining job information (i.e. schedules, billings, variances, milestones management, customer provided information, etc.). Currently, it is basically a read only application. All the users have their own front-end (Local FE) and essentially their own back-ends (Local BE). These LBEs get refreshed when the user connects to the network (either in-house or via VPN) essentially using compressed text files which then uncompress locally and then import in to the LBEs. There is a massive amount of data (3GB in 4 different LBEs) that gets refreshed each day (using the 7Zip compression tool) to each user within about 10 minutes update time. So far so good.

    However, now my boss is asking that I develop this tool to become more interactive... which essentially means I now have to allow for upload/write capability (for about 20 remote users and 20 in-house users). And I have to keep that updated data in a central location (Server BE) so that it can be accessible by every user in as real time as possible. So... all that being said... what are my various/best options? Should I look into SqlServer? What do I do if SqlServer is not an option I can persue? My company is currently implementing SharePoint. Would speed and accuracy be better if I pursued that option for a BE? Or should I stick with using .mdbs as BEs on our Windows Network? If so... what are my best write options? Meaning, should I use session objects and/or append/update queries or disconnected ado recordsets or dao objects, etc.?

    What I do know is.. that a live network connection to mdb BEs is only feasible for the in-house users (and that's pushing it as well). This scenario will not work for the remote users... it's just too slow.

    Thanks in advance for your input and suggestions.

    Steve

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    For a network with that many in-house users, I would run, not walk, to SQL Server. We do nearly all of our development using SQL Server as the back-end. 40 users on an Access back-end is just too many to try to deal with if the database goes corrupt, etc. And it solves some issues for you in terms of having users with different versions of Access.

    As to the remote situation and making it interactive - there are several options, none of which are ideal. One is to use replicaton - SQL Server is pretty robust in that situation, and more reliable than Access in my experience. However that requires a SQL Server installation on each workstation. Note the the Express version is a free download, but only supports push subscriptions, and not updating from the remote. The second is using a link to the back-end, and that is always horridly slow - not a good plan. The third is to use Windows Terminal Services or one of the commercial products such as Citrix. I do development and support using that facility on systems from coast to coast, and it is generally quite responsive as long as you have a broadband web connection. I even do work using mobile broadband. However, therre are some issues to consider. If your remote users are road warriors and need access to the data from unpredictable places that can be an issue. It might be possible to have a local copy that isn't updateable so they could access the data but not submit updates. An advantage is that everyone can be on the same version of Access, since the apps are stored on the central server. And you don't have the issue of someone trashing their Access installation inadvertently.

    That's just the start of what will probably be a lengthy conversation. Hopefully others will chime in with their experiences, and you can shed further light on some of your constraints. If you want to dig into a specific approach, post back with questions.
    Wendell

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So here's an additional thought... what about Replication?

    I tried it once (a long time ago in a galaxy far, far away) and I didn't like it at all and haven't ever looked at it again. Is it better? Would it be a viable solution in my scenario or would it be just over complication for what it's worth?

    Thanks,
    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In Access 2003, replication is still a pain in the behind.

    In Access 2007, replication is no longer supported for the new .accdb database format.

    So I don't recommend replication in Access.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Regarding replication... good to know. (I didn't like it anway.)

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I agree with Hans - replication is generallly a huge PITA in Access. I do use it in SQL Server in cases where we have concerns about exposing a database to the web, and it is robust, even across the Internet where connectivity is lost. But it can be a real pain too - especially when you want to make design changes to tables. In those cases you have to effectively rebuild the publication and the subscriptions if you change a replicated table. We have been running one scenario where we have some 700 users hitting a secured web page that displays selected information from a replica, and the users are allowed to submit update transactions that are subsequently vetted by the office staff. It has been running for over three years, and gone through 3 versions of SQL Server. But if your users are allowed to submit updates directly to the main database, then you would need to run a full version of SQL Server with merge replication, as the Express version doesn't support that kind of replication. Another issue is that somebody has to manage the inevitable update conflicts where two people edit the same record.

    I would seriously investigate the Terminal Services approach. It will probably take a more robust server, but would solve several problems for you.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    I like Wendall's suggestions and the reasoning behind them. Among other things, this simplifies, by eliminating, for the most part, concurrency issues that arise when users from multiple locations update the same record.

    Wendall's point about keeping open an option of a local store is also excellent, especially if you have traveling users, and even if you don't. In that regard, you might consider a local SQL data base, even if it runs in SQL Server Express. Even in that scenario, local updates could be made, and flagged for export on the next connection. However, exports of locally saved updates resurrect the concurrency beast.

    Wendall is right; this will be a long conversation, because there are, unfortunately, no perfect solutions, and many compromises to work out.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So here's a question for you. It seems my company has Oracle db. Would this be a solution with enough benefits for me to pursue IT about getting a few tables with RW permissions?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Oracle has many of the same benefits as SQL Server, and you can connect to Oracle tables using ODBC. Unfortunately my experience is all with SQL Server - it is considerably cheaper.... perhaps someone else can share their experiences with Oracle.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •