Results 1 to 4 of 4
  1. #1
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71

    Question Switch from a hosted SQL backend to a local SQL backend during network outage

    I've searched through the forums and online and haven't really found a workable solution to what I'm trying to do.



    I have a split database where the backend is hosted off-site on a SQL server. There is a local server onsite running an SQL-lite type program that we used to test the database during development. What I'd like to do is set up a process where we can switch to the local server when we lose internet connectivity so that we can continue to collect data without the need to revert to paper forms.

    Is there a way to programmatically set up a button/macro/form to make the switch? In my head I envision being able to click on a button and have the links move from the hosted server BE to the local server BE without any intervention from the user. The switch would only be a change in where the server is located, the table names and number would be identical. I've seen code for when it's an Access BE and FE, but nothing for SQL server side BE's.

    The BE is hosted off-site because of our IT policy, so keeping it local isn't an option. However, we can use a temporary, local server for network downtime and then append the tables to the hosted server.

    Is this something that's do-able or am I asking for too much flexibility?

    Thanks,

    DD

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    This is one way.

    Change the ODBC source of the link table.

    http://support.microsoft.com/default...b;en-us;159691

    Note: I have not tested this code. It is from Microsoft. Good luck.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have experience with network outage on your current FE/BE setup? If the network fails, do you get any corruption issues on restart?

    I think (haven't done it in a production environment) that it would be much like changing from BE_A to BE_B. Where BE_A is remote and BE_B is local.
    Especially since you are recording data entry, not updating a number of remote tables.
    Good luck.

  4. #4
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71
    Quote Originally Posted by orange View Post
    Do you have experience with network outage on your current FE/BE setup? If the network fails, do you get any corruption issues on restart?

    I think (haven't done it in a production environment) that it would be much like changing from BE_A to BE_B. Where BE_A is remote and BE_B is local.
    Especially since you are recording data entry, not updating a number of remote tables.
    Good luck.

    We've only had a single instance (so far) or a network outage. However, we're just moving to using the Access database so we're not sure how persistent this may be. When we had the outage that's when the idea came up for some way to work around a network outage.

    I'm sure that the process is pretty simple, I just need to get the coding down.

    Thanks all for the suggestions I'll update the post on how it goes - sooner if I can't make any forward movement.

    DD

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use network and local backend
    By andythepandy93 in forum Access
    Replies: 2
    Last Post: 11-15-2012, 11:24 AM
  2. Replies: 1
    Last Post: 04-08-2012, 11:42 PM
  3. Creating a local network Database
    By blukit01 in forum SQL Server
    Replies: 1
    Last Post: 03-15-2012, 01:20 PM
  4. Pass Through Queries - Local and Network Tables
    By vinfurnier in forum Queries
    Replies: 1
    Last Post: 03-31-2011, 11:18 AM
  5. Unable to open Access on Local Area Network
    By MikeWaring in forum Security
    Replies: 1
    Last Post: 12-24-2010, 11:46 AM

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
  •  
Other Forums: Microsoft Office Forums