Results 1 to 14 of 14
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Help creating auto-relinking VBA to same folder

    Requirement: Database is split due to size of some tables... As such, we need links to stay consistent depending on who opens the front-end. HUGE CAVEAT - the network location creates a situation where this data is far to slow to retrieve, update, and append. Therefore, the front and back end pieces must first be copied locally to the user's computer (performance). But, the links between the pieces will of course, break. I need to get the relinking to occur automatically, and then do so by looking in the same folder.

    I had a similar snippet of code in an Access 97 database, which I cannot convert without a pre 2013 Access installed, which I don't, so not too worried about it.

    HOWEVER, I did find the following article explaining how to use code to accomplish this:
    https://www.microsoft.com/en-us/micr...access-tables/



    KEEP READING: (Note: I took the liberty of finding/replacing all bad characters that paste natively from the above article. It has strange ', ", and - chars being used, which didnt' fit syntax.)
    Code:
    '—————————————————————————-' Procedure: RefreshTableLinks
    ' Purpose: Refresh table links to back-ends in the same folder as front end.
    ' Note: Linked Tables can be in more than one back-end.
    ' Return: Returns a zero-length string if all tables are relinked.
    ' Return: Or returns a string listing tables not relinked and errors.
    '—————————————————————————-
    
    
    Public Function RefreshTableLinks() As String
    On Error GoTo ErrHandle
    
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strCon As String
    Dim strBackEnd As String
    Dim strMsg As String
    Dim intErrorCount As Integer
    
    
    Set db = CurrentDb
    
    
    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
    'Verify the table is a linked table.
    If Left$(tdf.Connect, 10) = ";DATABASE=" Then
    'Get the existing Connection String.
    strCon = Nz(tdf.Connect, "")
    'Get the name of the back-end database using String Functions.
    strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "") - 1)))
    'Verify we have a value for the back-end
    If Len(strBackEnd & "") > 0 Then
    'Set a reference to the TableDef Object.
    Set tdf = db.TableDefs(tdf.Name)
    'Build the new Connection Property Value.
    tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd
    'Refresh the table link.
    tdf.RefreshLink
    Else
    'There was a problem getting the name of the back-end.
    'Add the information to the message to notify the user.
    intErrorCount = intErrorCount + 1
    strMsg = strMsg & "Error getting back-end database name." & vbNewLine
    strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine
    strMsg = strMsg & "Connect = " & strCon & vbNewLine
    End If
    End If
    Next tdf
    
    
    ExitHere:
    On Error Resume Next
    If intErrorCount > 0 Then
    strMsg = "There were errors refreshing the table links: " _
    & vbNewLine & strMsg & "In Procedure RefreshTableLinks"
    RefreshTableLinks = strMsg
    End If
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
    
    
    ErrHandle:
    intErrorCount = intErrorCount + 1
    strMsg = strMsg & "Error " & Err.Number & " " & Err.Description
    strMsg = strMsg & vbNewLine & "Table Name: " & tdf.Name & vbNewLine
    strMsg = strMsg & "Connect = " & strCon & vbNewLine
    Resume ExitHere
    
    
    End Function
    The following is used for error reporting.

    Code:
    Dim strMsg As String
    
    'Run the Procedure, getting any error messages.
    strMsg = RefreshTableLinks()
    
    
    'strMsg will be a zero-length string if there is no error message.
    If Len(strMsg & "") = 0 Then
    Debug.Print "All Tables were successfully relinked."
    Else
    'Notify the user of the errors.
    MsgBox strMsg, vbCritical
    End If

    I am not seeing results for this so far. When removing the back-end database from the current folder, it does not error, nor does it seem to relink even when the correct file is placed back into the folder.

    Please let me know if there is a better design - THANKS!

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    the front and back end pieces must first be copied locally to the user's computer
    I find the above quote to be troubling.
    How are you sharing the database? The users should already have a local copy of the front end on their computers.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    So users are not sharing data? You cannot replace backend.

    I have similar code. Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    the network location creates a situation where this data is far to slow to retrieve, update, and append.
    Have you taken any steps to limit the actual amount of data that is being pulled from the backend?
    How big are the tables? Is the network local and wired?

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm also concerned about this approach.
    If users modify any data, are you seriously proposing to copy each user's changes back to the network
    If so, what if more than one user modifies the same data. Who 'wins'?

    This can only work successfully if the backend is made READ ONLY or there is only one user
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by moke123 View Post
    I find the above quote to be troubling.
    How are you sharing the database? The users should already have a local copy of the front end on their computers.
    Sorry for the late response. You are not understanding the purpose of the database. it is not so that users can enter data, but to prepare a report that we do not have the ability to create outside of access.

    The task could be handed to two different people depending on the day (someone out of office), and so we just need to have them copy the files local for performance reasons, then have them link together.

    Thanks.

  7. #7
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by moke123 View Post
    Have you taken any steps to limit the actual amount of data that is being pulled from the backend?
    How big are the tables? Is the network local and wired?
    The network is across our company's enterprise, several cities. However, the drive speed and configuration of the shared server location, is the reason for the poor performance. I have been informed that it will all be replaced with SharePoint in the near future, but I don't want to wait for the speed of business, ya know?

  8. #8
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ridders52 View Post
    I'm also concerned about this approach.
    If users modify any data, are you seriously proposing to copy each user's changes back to the network
    If so, what if more than one user modifies the same data. Who 'wins'?

    This can only work successfully if the backend is made READ ONLY or there is only one user
    1 user at a time, this is not an issue. This is not a user database, but the person preparing the report is technically called a "user". Hence the confusion.

    I really do need to auto-relink, so any help would be appreciated.

  9. #9
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    EDIT: Here is a sample database I am using to develop this, then will transfer it to another database.
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Have you step debugged your code? Comment out the On Error GoTo line while debugging.

    Here is my re-linking procedure:

    Code:
    Public Sub SetTableLinks()
    'Use the variables provided to do the manipulations desired
    Dim td As TableDef
    Dim db As DAO.Database
    Dim strOld As String
    Dim strNew As String
    'replace the following strings as needed
    strOld = "R:\Lab\Database\Data\LabData.accdb"
    strNew = "C:\Users\Owner\June\DOT\Lab\Data\LabData.accdb"
    Set db = CurrentDb
    For Each td In db.TableDefs
        If InStr(td.Connect, strOld) > 0 Then
            Debug.Print td.Name
            Debug.Print "Old Link: " & td.Connect
            td.Connect = Replace(td.Connect, strOld, strNew)
            td.RefreshLink
            Debug.Print "New Link: " & td.Connect
        End If
    Next td
    db.TableDefs.Refresh
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    I have a common front end (stored locally) that can link to one of two back ends (identical except for the data) depending on the project. This is the code I use to ensure the database links to the correct back end.

    [Code]
    Function LinkDBbackend(project As Integer) As Boolean

    Dim dbBackend As DAO.Database
    Dim tdfExtTables As DAO.TableDef
    Dim strBE As String
    Dim pw As String
    Dim rs As DAO.Recordset
    Dim dbs As DAO.Database Dim tdf As DAO.TableDef
    Dim strTable As String

    pw = "Password"

    On Error GoTo CouldNotLink

    ' Set the DB location
    Select Case project
    Case 1 ' Project 1 strBE = "\\blabla\project1\Databasebackend.accdb"
    Case 2 ' Project 2 strBE = "\\blabla\project2\Databasebackend.accdb"
    Case Else
    MsgBox "The database is not set up to work with this project. Please contact Admin", vbOKOnly, "Not allowed"
    Exit Function
    End Select

    'Routine to relink the tables automatically.
    Set dbs = CurrentDb()
    For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
    If tdf.Connect "MS Access;PWD=" & pw & ";DATABASE=" & strBE Then 'only relink tables if the are not linked right
    If Left(tdf.Connect, 4) "ODBC" Then 'Don't want to relink any ODBC tables
    strTable = tdf.Name
    dbs.TableDefs(strTable).Connect = "MS Access;PWD=" & pw & ";DATABASE=" & strBE
    dbs.TableDefs(strTable).RefreshLink
    End If
    End If
    End If
    Next tdf

    LinkDBbackend = True
    Exit Function

    CouldNotLink:
    MsgBox "Unable to connect to linked tables, make sure you have permission to access this project.", vbOKOnly, "Error"
    LinkDBbackend = False
    On Error GoTo 0

    End Function
    [\Code]
    Last edited by Robyn_P; 08-08-2018 at 04:40 AM. Reason: Formatting

  12. #12
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Gaa, sorry formatting still pants, how do I do code tags again?

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    how do I do code tags again?
    Highlight the text and click the Octothorpe (#) above the text box.

    the code tags should look like "["Code]" and "[/Code]" (with out the quotes)

    use the [] instead of <>

  14. #14
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    if you really are coping the data to your desktop then you could ammend this code to relink the front end to the backend

    https://accessjitsu.com/2016/01/10/m...n-table-links/

    i have used this once before to relink the front end to the backend (if that is ever moved) or drive letters/ unc path changes


    steve

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

Similar Threads

  1. Creating A New Folder
    By Eranka in forum Access
    Replies: 1
    Last Post: 05-30-2018, 01:25 AM
  2. Replies: 3
    Last Post: 08-07-2015, 07:21 AM
  3. Replies: 1
    Last Post: 08-05-2015, 12:04 PM
  4. Replies: 4
    Last Post: 06-27-2013, 12:29 PM
  5. Creating Viewport into a folder
    By dragondata in forum Access
    Replies: 2
    Last Post: 03-26-2010, 07:15 PM

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