Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Unanswered: MS Access server......? Share and share alike

    Hey all, I know this isn't technically a database question but I'm hoping someone can help me anyway! I work for a small mining company that has 4 locations around the country (geographically far apart...furthest one is 4000-5000km away). We have an Access database that we use between all the locations to track orders/stocks etc. A VPN is set up between each location, over which the database is synchronised daily.
    We'd like to move to a real-time system, where everyone is using the "same" database (instead of replicas) and therefore can see each other's changes straight away. The master copy of the dbase is on a server here, but the VPN is waaaaay too slow for the remote locations to access the database this way (even if they have their own front ends). So what options are there? Seems we've either gotta pay for a faster connection, or move the database onto someone else's server who has a faster connection. I'm sorta new to all this, so does anyone have any experience in this sort of thing that can help me out? Which way sounds better, paying someone to host the database (assuming that Access will run off a web server....never tried that either), or paying possibly large amounts for a faster connection (we're currently on SDSL 512/512 at all locations)? Prices would be great too if someone has that knowledge/experience (Australian prices that is....), or even just links to companies that could help.
    Thanks in advance!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry you didn't get a reply on this yet. i thought about it yesterday but my opinions are, well, opinionated, so i was hoping someone else would step in.

    slow LAN/VPN & multiuser & Access/Jet as data engine: far far away from my preferred situation.
    - first it is as slow as you like
    - second it is fragile (all processing happens at the client so the slightest hesitation from LAN/VPN/fileserver leaves your db corrupt).

    ideally, you re-write the application to talk to an SQL serving machine.
    you seem to be price sensitive, so why not Linux & MySQL & any old PC you can find hanging around the office. if you 'rewrite appropriately', you should be safe for dozens of users on almost any machine made in the past few years, and secure from LAN/VPN drops. total cost $0.00 plus some download-time from the net to get the soft.

    the hassle is 'rewrite appropriately' ...minimise the data swapped both ways on the LAN/VPN and maximise processing done on the 'server'. it's a different mindset.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2004
    Posts
    75
    cheers for the reply....yeh I thought that a server database program would be better suited to this kind of application, problem is I have no knowledge of any database program besides Access. How hard is it to learn MySQL/SQL Server after having learnt Access? I am mainly self-taught and I've only dabbled in ADO connections and the like....I assume I could use the same Access front end I already have (from things said on this forum), but I wuoldn't know how to connect that to a mySQL/SQL server back end
    Also, in this scenario you're suggesting, are you saying the VPN would be sufficient? It seems to me that it would be worse, since all processing is done at the server, there would be alot more traffic than the VPN can handle..
    Thanks for your help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the server acts as a back end data store for Access. Yu can contiue to deploy your application in Access, you "just" tell Access to talk to a server rather than Access's native datastore: JET

    A word of caution though, you need to drop any SQL processing doen in Access, as you will then have the qworst of both worlds. You need to switch to disconnectged recordsets, do more error trapping yourself. its a heck of a pain, but worht iut in the long term


    if you do go down this route consider getting a decent book on Access development in this style (Access XXXX Developers Handbooks are a good start)

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2004
    Posts
    75
    thanks for your help healdem. Just how hard is it to convert an all Access database to an Access front end with an SQL server back end? I've had a look at SQL server before and I wouldn't know where to start with that. I'm not 100% sure what you mean by "disconnected" recordsets, or how to "drop any SQL processing done in Access"....does the second one mean I'll have to rewrite all my queries in SQL server?
    Are there any SQL server beginner tutes/FAQs etc online? I just need something to at least get me started with SQL server....
    And can someone please explain exactly why doing it this way will be faster over the VPN?
    Cheers

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its a failry majopr task to truly leverage the power of a server back end with any front end, not just Access. If you have developed your applciation using JET as the datastore then its a totally different mindset. Access does a great job at making it easy to create quick and simple, visually straightfoward applications. however most of those whizzy bits are not approppriate if you are using a server backend. not that you can't use them, its just that the default (ie simple wizards) don't work well.

    the reason a server backend will (probably) benefit you is that the data processing is being done on the server, the server then sends only the required information up the vpn to the client. Instead of the client sucking the data (and ther nearby records fromt he indexes and tables) up the vpn. so you are sending a lot less information over the wire, so its faster. Also there is less data being humped around, there is less risk it will get corrupted.

    you need to divide your application into 2 (the front end ie the interface) anbd the backend (the data store.

    in the backend you need to make use of stored procedures (these can handle the row inserts & changes (and trigger an audit log if required). you can stroe your queries for the list boxes & reports etc as views. the server knows the view defintion already so it has already "ccosted" the least rsource hungry way of accessing your data.

    Think of it this way rather than haveing each client workstation reading and modyfying the indexs & tables you have on central server doing all that manipulation, each client requests that the server parcels up the inforamtion it (the client) needs. the real art is making sure that you only request what you actually need from the server. so query design is important, getting the best from SQL is inportant.

    A way formawar may be to make a new access applications and use the Access Data Project as tyour development tool. A word of caution you need to be very very carefull and not get sucked into writing queries directrly in Access, or in forms or reports, any SQL that you do issue should be on local tables OR as pass through queries to the server
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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