Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: Synchronize Access database across the internet

    I have a client who is setting up a remote office location, and utilizes a program that uses an access database for customer transactions and information.

    He wants the information that is entered in either office location to be updated with the other location promptly, bidirectional. I have the network all setup for transfers across the internet, that's no problem. My trouble comes in with the Database. The program that uses it pulls an inordinately large amount of information from the database (not designed for internet use) so the remote location is very slow, since it pulls all the information through the internet frequently. What I need is a way to simply copy the database to the remote location and then subsequently synchronize the changes. The replication feature of access is not an option because it causes a number of side effects in the program that are not acceptable to the client.

    I was hoping there might be a way to capture all of the queries that are run through the database over a span of time (say 5 minutes), then transfer only those queries through the internet and run them against the other database and vice-versa.

    If anyone has any ideas, thank you in advance. I'm not locked into the idea I presented above, if someone has a better one, let me know please. That's just what I thought might work best with my limited knowledge of databases

  2. #2
    Join Date
    Jun 2010
    Posts
    3
    By the way, the access database is Access 2003

    And my current configuration is just a mapped network drive so that the client program at the remote location accesses the database on the home server as though it were local
    Last edited by Raistca; 06-22-10 at 15:58.

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    there is no inherent solution within Access - since it is designed to operate on a LAN only. Here are some approaches:
    * spend a ton of money on a high speed WAN so that the thru put approaches that of a LAN

    * move to a web based solution: inside this option are several paths one could take:
    1. the Access07/10 web capabilities with Sharepoint - or a sharepoint service AccessHosting.com
    2. any of dozens of web design technologies i.e. asp.net/php/LAMP stack etc...also this should include an online web db service such as qrimp.com
    3. there is a new service from eqldata.com that puts Access up on the web in terminal services mode - might be worth checking out

    * replication; AccessTables.com offers a service - but not oriented toward every 5 min is more about 1x per day. Obviously if you have .mdb files you can implement your own replication - but at 5 min intervals it really isn't practical.

    bottom line - when an Access application (or truly any desk top application) needs to go wide geographic area with multiple simultaneous users - - there are major issues and still a pretty big step up in cost & complexity.

  4. #4
    Join Date
    May 2010
    Posts
    601
    There are still other options that I use.

    I will assume that the source to the Access application is available to be modified.

    Quote Originally Posted by Raistca View Post
    I was hoping there might be a way to capture all of the queries that are run through the database over a span of time (say 5 minutes), then transfer only those queries through the internet and run them against the other database and vice-versa.
    This is possible to do. With only a five minute delay will add a performance hit. It will also require a lot of VBA coding.


    Quote Originally Posted by Raistca View Post
    I have a client who is setting up a remote office location, and utilizes a program that uses an access database for customer transactions and information.

    He wants the information that is entered in either office location to be updated with the other location promptly, bidirectional.
    I would use a shared back end.

    Options:

    1) SQL Server: convert the back end to an SQL server. The remote office can connect directly to the back end over the internet. To convert the Access app to an SQL server back end will probably take so programming changes. There are the free: MS SQL Express or mySQL that can be used

    2) Thin Client: I use Terminal Services a lot. This does require a Terminal Server. It is also very possible NO changes will be required to the Access database. It will also be in real time! This is a great solution is you change modify the Access app.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2010
    Posts
    3
    Thanks for the suggestions guys. I would personally love to move the remote office to terminal services, but our home server is running SBS 2003 and is without, and the boss doesn't want to put up the money for it. If you know of another option here, let me know. But honestly I'm not sure how the server would perform under such a load. It is fairly old (circa 2004)

    I think that a sql server back end might help some, but I think in the end it would still be too slow, based solely on the sheer amount of data this program pulls (It's insane, I want to hunt down the guy who coded this and do bad things to him). Upon opening it loads the entire list of customers names before the user even searches for someone.

    I may not do this if it takes too much, but could you point me in the direction of how to go about the VBA coding for my own personal edification that would be great. I don't have any experience with VBA, so I wouldn't even know where to begin. I enjoy coding, so I might just do it for the hell of it

    Thanks again!

    Also, I'm not sure what the application was coded in, but it is an executable and not dependent on Access for execution, just data. Therefore I don't have access to the source code, nor can I make any changes to the structure of the Access database, since the program would then not find what it was looking for (it looks for the database by file name, by the way)
    Last edited by Raistca; 06-23-10 at 18:58.

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Raistca View Post
    Thanks for the suggestions guys. I would personally love to move the remote office to terminal services, but our home server is running SBS 2003 and is without, and the boss doesn't want to put up the money for it.
    Just like you would not let a user set at the sever and work, the same is true for remote sessions. I find it always best to have a separate machine that users remote into. I think of it as a shared workstation.

    Quote Originally Posted by Raistca View Post
    I think that a sql server back end might help some, but I think in the end it would still be too slow, based solely on the sheer amount of data this program pulls
    Huh? <confused> That is one of the main benefits of a SQL server. You just made a stronger case for going to an SQL server.

    Quote Originally Posted by Raistca View Post
    I may not do this if it takes too much, but could you point me in the direction of how to go about the VBA coding for my own personal edification that would be great. I don't have any experience with VBA, so I wouldn't even know where to begin. I enjoy coding, so I might just do it for the hell of it

    Thanks again!

    Also, I'm not sure what the application was coded in, but it is an executable and not dependent on Access for execution, just data. Therefore I don't have access to the source code, nor can I make any changes to the structure of the Access database, since the program would then not find what it was looking for (it looks for the database by file name, by the way)
    If the client does not own the source code and.or has the ability to modify the app, then SQL server, replication, etc is out.

    Learning VBA code, or hiring a Access/VBA Expert like myself, will not help in the situation.

    If the client want to continue to use the current software IMHO the best and probably only solution is to get a Terminal Server or some other Thin Client solution (Like 2X or Citrix).

    Otherwise a new solution will have to be purchased that can handle multiple locations. Either Purchase a new software application or have one custom one written.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    with all due respect to HiTechCoach - - who is a great resource and all around good guy....I don't think a move to sqlserver helps at all because the issue is the network latency between the FE and BE - - regardless of type of BE.

    The Terminal Services advice fundamentally is good (as always from HTC) - but it generally also means a good high speed (private?) WAN - which I mentioned as my first point - and in small business environments is unaffordable. (also note my second point, item 3, which is a generic TS service offering from eqldata.com)

    I believe my initial post still outlines the realistic universe of options.

  8. #8
    Join Date
    May 2010
    Posts
    601
    @NTC, your original list of options was great. I was just expanding on the possibilities.

    Then Raistca said...

    Also, I'm not sure what the application was coded in, but it is an executable and not dependent on Access for execution, just data. Therefore I don't have access to the source code, nor can I make any changes to the structure of the Access database, since the program would then not find what it was looking for (it looks for the database by file name, by the way)
    This really does limit the options for the existing software.


    Note:
    I have actually had great success running Remote Desktop Client over generally available high speed internet service, like cable and DSL, from the local providers. For security, a VPN appliance is normally used.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Oct 2009
    Posts
    340
    well, the fundamental reality is the network latency. and so one must select the best tool within that construct...

    remote control/vpn involves more hand shaking ack/nak and is slower - but they will maintain the link and work well because this type solution is designed to be used on a wide range of network quality/speeds. But for multiple users the vpn/remote control method is a burden. Works good for user's connection to their desktop giving access to every file on the pc/network - not specifically the solution to making an Access application into a multiuser WAN solution....

    In general a web application is the most efficient technology. so if you are designing a multi user, wide area app from the ground up - - one really should be web based.

    in the case of Access - separate the FE from the BE (and this includes whether or not the BE is sqlserver or on sharepoint) and the FE begins to time out (I've never found out any spec in this regard) and behavior is unreliable. That it works at all is because the public internet overall speed has improved considerably in the last decade. But it still isn't reliable. This is why the sharepoint solution features the 'work locally' function - which then encompasses replication when you re-share those tables back onto sharepoint. So the dilemma is the existing Access application needing to move to the net - where they don't want to re-write as a web app. There is no ideal solution - - and this question appears alot at forums....

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by NTC View Post
    well, the fundamental reality is the network latency. and so one must select the best tool within that construct...

    remote control/vpn involves more hand shaking ack/nak and is slower - but they will maintain the link and work well because this type solution is designed to be used on a wide range of network quality/speeds. But for multiple users the vpn/remote control method is a burden. Works good for user's connection to their desktop giving access to every file on the pc/network - not specifically the solution to making an Access application into a multiuser WAN solution....

    In general a web application is the most efficient technology. so if you are designing a multi user, wide area app from the ground up - - one really should be web based.

    in the case of Access - separate the FE from the BE (and this includes whether or not the BE is sqlserver or on sharepoint) and the FE begins to time out (I've never found out any spec in this regard) and behavior is unreliable. That it works at all is because the public internet overall speed has improved considerably in the last decade. But it still isn't reliable. This is why the sharepoint solution features the 'work locally' function - which then encompasses replication when you re-share those tables back onto sharepoint. So the dilemma is the existing Access application needing to move to the net - where they don't want to re-write as a web app. There is no ideal solution - - and this question appears alot at forums....
    NTC,
    I agree with you. I really am arguing with you.

    But in this case for the OP, it is not a Access front end, but an app that is an EXE. It only has a Access back end. Also they did not write it or have the source code.

    ... for multiple users the vpn/remote control method is a burden ...
    I was not referring to remote control, but an RDP session. That has not been my experience.

    I work over 95% of the time in a RDP session. About the only time I have an speed issues with a RDP sessions is when I use a public WiFi connection from my laptop.

    Maybe my clients have just been lucky, but they have have great success with the public internet. It has been mainly with Cox Comm.

    I current support a client that has a TS farm with 6 servers that supports over 900 clients/devices at over 200 remote locations running a POS system with an SQL back end and several Access FE's. It is extremely fast. Even almost all the local users use a RDP Session.


    For multiple locations, I have tried many solutions and to date, the best reliably one for my and my clients has been Thin Client (Citrix, Terminal Services, etc).

    I have had great success with Thin Client. It allows most LAN based Rich Client apps to be used from multiple locations without any modifications to the software.

    I agree that Thin Client is not a one size fits all solution. There are some Rich Client apps that do not do well in a RDP session.

    Sharepoint sounds like it will be great for some solutions when it matures.

    Some friend have been using Access 2010 with MS SQL Azure with great success. With there specific apps, they have had better success with Azure than Sharepoint.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Oct 2009
    Posts
    340
    it may be with Cox (or AT&T or Quest whoever) but is the TS Farm example really using generic public internet? or have they a defined corporate high speed vpn with fat pipes in/out of the cloud with Cox or whoever?....I really doubt they would entrust that serious of a mission critical app into public Cox (or whoever) without a VPN contract. I did a tour of duty in networking inside mega corp...and the VPN is an overlay (or underlay) of the carrier's 'public' network - with guarantees in thruput/response time etc..and costs $$$. Plus apps designed to be distributed from the get-go have very savvy local caching and such - to keep alive when the network isn't perfectly fast.... and none of this is the generic Access reality....

    usually the questions about trying to Access onto or over the net, at these forums are small businesses with avg links into the public internet - - - and that is where my advice is framed.

    it really isn't that we disagree at all; what one can implement via a high speed vpn and with apps built to be WAN based is significantly different than the generic public internet and Access. I always assume the latter is the situation.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not recommending this, as I think your biss really should stump up for the proper solution (which would be a server backend in a centralised location serving asmany remotes as required.

    what you could do is run a copy of Access with a fimer on a form which periodically sucks the changed data out of each of the db's. however identifying that changed data could be tricky. you coudl spot when someone adds a record (by adding a new boolean column called say reported, and set that flag to true when you've reported it to the other remote.

    spotting deletions would be tricky, unless say you had a local duplicate pon each site and do a comparision between records that exist in the live db and those that don't in the duplicate. using something such as the query wizard missing itesm

    spotting changes could be even trickier you'd have to do a column by column comparison and that coudl be costly in terms of performance of the existing scheme

    the other alternative it to do that work overnight assuming you can find a window when neither app is in use

    in any event its a nasty nasty workaround and I wouldn't recommend it.r
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2009
    Posts
    340
    your last line was key "its a nasty work around and wouldn't recommend" !!

    In the end - for the average Access application wanting to be over the net:
    * spend money on a high speed WAN so that the thru put approaches that of a LAN

    * move to a web based solution: inside this option are several paths one could take:
    1. the Access07/10 web capabilities with Sharepoint - or a sharepoint service AccessHosting.com
    2. any of dozens of web design technologies i.e. asp.net/php/LAMP stack etc...also this should include an online web db service such as qrimp.com or quickbase.com
    3. there is a new service from eqldata.com that puts Access up on the web in terminal services mode - might be worth checking out

    * replication; AccessTables.com offers a service - but not oriented toward every 5 min is more about 1x per day. Obviously if you have .mdb files you can implement your own replication - but at 5 min intervals it really isn't practical.

    bottom line - when an Access application (or truly any desk top application) needs to go wide geographic area with multiple simultaneous users - - there are major issues and still a pretty big step up in cost & complexity.

  14. #14
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    An idea that just might work depends the fact that Access can export data to an Excel file or a .csv file. This file is then simply copied as a transaction file to the remote machine, after which a process on the remote machine picks up the data and updates the remote database. In other words, a quasi-batch process. This would allow the systems to function independently. It would not be a real time update of course, but at least neither database is affected by network latency. You could have multiple files shot of at say five minute intervals. You could even have multiple copies of an Access, linked to the back end, each of which processes a named transaction file.

  15. #15
    Join Date
    May 2010
    Posts
    601
    @Jim, the stuff part about sending batch updates is all the work it takes modify the system to be able to export the correct data. And when importing, apply the updates to the correct records. Basiclly you are creating replication via transaction batches.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

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