Results 1 to 5 of 5
  1. #1
    WimDC is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    12

    switch backend

    Hi,



    this may be a very stupid question, but here it comes.

    I'm using a large application (split database) on a server (I don't have admin-rights, it's from my work). In frontend, all tables are correct linked to multiple backend-(data)-files.
    So far so good.

    But sometimes, one of the backend-files gets "locked" on the server (read-only) and mostly, the next day (I think after a nightly reset of the server), everything works fine again. Automatically.
    This means that some days, the data can't get updated until the night.

    So I'm thinking of using a second backend ("mirror") in case the original backend is locked. I can copy the original backend to a new file with a new name, but it's impossible to change (in front-end) every link to the tables (linked table manager).
    It would be good if I could use a simple table in my frontend with the name of the back-end-file that has to be used. Thus avoiding "manual" update of all linked tables with the manager.
    So I could change the name of the back-end-file to the mirror-file in case of and all of my colleagues would then work with the data in the mirror-file.

    Or use a "switch" (with vba) to switch between 2 backend-files: the original one and the mirror-copy

    Is this possible or is there another solution?

    Thanks for helping.

    Wim

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I do this to switch between sites.
    the form has a combo box ,me.cboConn, that has the connect string.
    all the tables relink to the new path.

    Code:
    Public Sub AutoRelink()
    Dim vTbl, vPath, vTName, vDbTbl, vFile, vMdb, vLocalDir, vConn, vSite
    Dim db As Database
    Dim tdf As TableDef
    
    
    On Error Resume Next
    Set db = CurrentDb
    For Each tdf In db.TableDefs
       
       If (tdf.Connect) <> "" And InStr(tdf.Name, "~") = 0 Then
               tdf.Connect = Me.cboConn  'user chooses backend
               Debug.Print tdf.Name
               tdf.RefreshLink
    skipLink:
            'End If
       End If
    Next
    MsgBox "Done", , "Relink"
    Beep
    endit:
    Set tdf = Nothing
    Set db = Nothing
    Debug.Print "---done"
    End Sub

  3. #3
    WimDC is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    12
    Hi,

    thank for the quick reply and give it a try.
    BUT... I only need to change the link for certain tables, not all tables as I use multiple back-end-files for my data.

    "Somewhere" in the For Each tdf in your code, there should be a "IF THEN" to check if the current back-end matches the file I want to change.
    Something like if tdb.Name = "oldfilename_AAA.accdb" then tbf.connect = Me.cboConn

    If a table is linked to "oldfilename_BBB", then nothing has to be changed for that particular table. And then continue with the next table.

    Do you have a suggestion?

    Wim

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put the list of those tables into a table, then cycle thru that list only.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Wim,
    Just a word of caution, you are going on a very slippery slope with this approach. You will have to be very dilligent with replacing the "mirror" copy with the "original" imediately after the original stops working and replacing the original with the mirror after the server reset. And what would happen if the mirror gets locked too, create another one? I would suggest you try to see why the locking happens, could be a faulty network hardware or the db design or....

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Switch Routine
    By Thompyt in forum Programming
    Replies: 7
    Last Post: 03-27-2015, 09:31 PM
  2. Switch Query for Yes/No
    By james28 in forum Queries
    Replies: 3
    Last Post: 08-01-2014, 02:49 PM
  3. IIF or Switch or ??
    By pj33558 in forum Queries
    Replies: 8
    Last Post: 04-21-2014, 11:44 AM
  4. Replies: 3
    Last Post: 01-02-2014, 08:11 AM
  5. Switch and Tables
    By UtilityRyan in forum Database Design
    Replies: 0
    Last Post: 06-12-2007, 03:49 PM

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