Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Re-linking tables

    My first adventure into re-linking is greeted with errors I don't understand. The intent of the code is to loop through the TableDefs deleting linked tables and then adding the tables back into the tables collection linking them to the BE named "ToDbName". I can't quite figure out what I'm doing wrong?

    As trapped by my error code:Click image for larger version. 

Name:	000.jpg 
Views:	48 
Size:	32.2 KB 
ID:	35069



    Code:
    Public Function ReLink(ToDbName As String)
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strTableName As String
        
        Set db = CurrentDb
        
        For Each tdf In db.TableDefs
            On Error GoTo Err_Deletion
            
            strTableName = tdf.Name
            If Not (strTableName Like "MSys*" Or tdf.Name Like "~*") Then   'Ignore SYS & Temps
                If Len(CurrentDb.TableDefs(strTableName).Connect) > 0 Then  'Table linked?
                    DoCmd.DeleteObject acTable, strTableName                'Yes, delete the link
                    
                    On Error GoTo Err_Linking
                    Set tdf = db.CreateTableDef(strTableName)               'Create anew
                    tdf.Connect = "; DATABASE=" & ToDbName                  'Link to new BE
                    db.TableDefs.Append tdf                                 'Append to TableDefs
                End If
            End If
        Next
        Set tdf = Nothing
        Set db = Nothing
        
    Exit Function
    
    Err_Deletion:
        MsgBox "Error encountered deleting the link to table " & strTableName & vbNewLine & _
               "in database " & db.Name & vbNewLine & _
               "Error #: " & Err.Number & ": " & Err.Description & vbNewLine & _
               "Will attempt to continue processing."
        Resume
        
    Err_Linking:
        MsgBox "Error encountered linking to table " & strTableName & " in" & vbNewLine & _
               "back-end database file: " & ToDbName & vbNewLine & _
               "Error #: " & Err.Number & ": " & Err.Description & vbNewLine & _
               "Sorry, cannot run application until this issue is resolved."
        DoCmd.Quit
    
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is the BE db in that path?
    does the BE have that table?
    Can you open the table in the BE?
    compact /repair it.
    in the FE,delete the table ,then manually link it back in.

    is this multi-user?
    if so,why is the BE on C drive?
    if not, why is it split?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If this is related to your other post on getting the linked table connection property, then it seems you've deviated from what you should be doing - which is changing the connection property. Here it looks like you are trying to append a new table with no fields (which I believe you cannot do) then set its connection property. strTableName is just a variable whose value is the name of a table before you deleted it (actually, you're just deleting the connection, not the table itself). It isn't a table object. Shouldn't you just be changing the connection string for the existing tables? After all, these appended tables (assuming you got it to work) would look nothing like the ones you want to switch from/to based on the user, as strTableName as a new table would have no data anyway. Or does this have nothing to do with your other post?
    Last edited by Micron; 08-10-2018 at 07:05 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I think you've hit on what is perhaps the issue. If I'm understanding you correctly, it may be that all I have to do is change the connection property of the link. I.e., NOT to delete the table but rather just "point" the link to a different BE. And yes, this is directly related to the post of a few days ago where I was wanting to loop through the tables collection.

    So, is this a simple case of changing the DATABASE= string to refer to the new BE? (BTW, the table structure between the two DB's are identical. The point is to NOT commingle personal data with business data for a single user.)

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    It's not enough to just change the connect property. (I didn't do anything with the error handlers yer)

    Code:
        For Each tdf In db.TableDefs
            On Error GoTo Err_Deletion
            
            strTableName = tdf.Name
            If Not (strTableName Like "MSys*" Or tdf.Name Like "~*") Then   'Ignore SYS & Temps
                If Len(CurrentDb.TableDefs(strTableName).Connect) > 0 Then  'Table linked?                
                    On Error GoTo Err_Linking                
                    tdf.Connect = "; DATABASE=" & ToDbName                  'Yes, Link to new BE                
                End If
            End If
        Next

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Just missing the link refresh, "tdf.RefreshLink".

    Final code:
    Code:
    Public Function ReLink(HostDB As String, frmReOpen As Boolean)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '  ReLink all of the linked tables to the "HostDB".
    '
    '  In addition to the re-linking being done when the app is started, the user can
    '  use the register form option to "change horses" during a session.  When that
    '  occurs, ReLink will close the register and "ReOpen" so as to in effect honor
    '  the necessary code execution in the Open Event of the Register.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strTableName As String
        
        If frmReOpen Then _
            DoCmd.Close acForm, "frmRegister"      'If ReLink is "ordered" by user via form option
        
        Set db = CurrentDb
        
        For Each tdf In db.TableDefs
            
            strTableName = tdf.Name
            If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then       'Ignore SYS & Temps
                If Len(CurrentDb.TableDefs(tdf.Name).Connect) > 0 Then      'Table linked?
                    tdf.Connect = "; DATABASE=" & HostDB                    'Yes, specify new BE
                    tdf.RefreshLink                                         'and refresh
                End If
            End If
        Next
        Set tdf = Nothing
        Set db = Nothing
        
        If frmReOpen Then _
            DoCmd.OpenForm "frmRegister"
        
    End Function

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Saw your post early this morning but have been swamped all day. Was going to point out the need to only to change the link and refresh, but I see that you have that figured out. Seeing as how I was lucky enough to hit on the need, was also going to mention that your case may be similar enough to what I did in the past. The suggestion would be to have the target links in tblLinks (in your case, you'd need a field to relate those links to a user or some other factor) and simply iterate over the list, changing the connections as required. So for example, your startup function might either base this on the user's Windows login name or a prompt, and find that value in tblLinks and modify the connection according to each connection value for the factor (login name, personal use, business use or whatever). I coded so that the routine retrieved the current link and the target link and compared them. I felt there was a need to incorporate certain options if they were not the same, but I'm not seeing a need for that in your case.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Well, you post most assuredly got me on the right track. I have another app that will get updated with the more "proper" approach to re-linking in a day or two thanks to our exchange. I have yet another app that is "user name" sensitive to its function but in the current case I don't see a need.

    Thanks for your help,
    Bill

  9. #9
    rspock is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    4
    So, if I understand Graeaglebill, I can use the code he cites in a form (possibly called by a button on the opening "splash" form) to reconnect a freshly distributed frontend (on a computer with runtime only) to an existing backend in a shared directory? Is there a simpler way to reconnect a freshly installed runtime frontend "package" to the backend?

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    If by "he" you mean me, then the code you see in post #6 is installed in the front-end run-time mde that shares files in the same directory on a NAS. The code is intended to attend to ALL currently linked tables switching from where ever they currently are to the BE "HostDB" that is passed to the function. The frmReOpen parameter is simply an indication to the function as to whether the app has just been started or "re" linking is required after the app is already running.
    Bill
    a.k.a. GraeagleBill

  11. #11
    rspock is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    4

    reconnecting runtime frontends to backends

    Quote Originally Posted by GraeagleBill View Post
    If by "he" you mean me, then the code you see in post #6 is installed in the front-end run-time mde that shares files in the same directory on a NAS. The code is intended to attend to ALL currently linked tables switching from where ever they currently are to the BE "HostDB" that is passed to the function. The frmReOpen parameter is simply an indication to the function as to whether the app has just been started or "re" linking is required after the app is already running.
    Bill
    a.k.a. GraeagleBill
    Ok, it looks like you're using an older version of Access (mde?), also I'm not familiar with what an NAS is. I'm using Access 2016. Let me see if I can describe/write some code to reconnect the front end to the backend in a splash screen form that is starting for the first time in a runtime environment.

    The reason I want to do this is because I've designed a "supply/inventory app" in accdb (no... the MS canned version won't do) which works fine UNTIL I split the db. I can copy the unsplit version to a sandbox with only the runtime and it works after resolving "trust" issues - except for a really irritating runtime error when quitting the app. However, when I split the accdb and copy it to another location, I start getting run-time errors relating to unrecognized ODBC functions used to open tables using VBA database and recordset objects. In any case, I need a function that will link the frontend to the backend. So, I'm going to post some VBA code in a few minutes/hours for reconnecting and any constructive criticism or suggestions will be highly appreciated.

    Also, If anyone knows of a thread that deals with ODBC functions not recognized, please let me know where.

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Ok, it looks like you're using an older version of Access (mde?), also I'm not familiar with what an NAS
    NAS is Network Attached Storage (E.g., like a hard-drive connected to a router or network switch) My app is developed in A2013. The only reason for maintaining the code in mdb and runtime in mde files has to do with my continued use of right-click popups, something that was discontinued in A2007. I have A2003 on an old laptop where I take my mdb source files to do the maintenance on right-click functionality. There might be another way but I've never found it and I deplore ribbons as a substitute.

    I'm surprised to hear of your problems with split databases. A couple of thoughts come to mind. One, is your data normalized? Did you let Access analyze the table relationships? Un-normalized data can in certain cases confuse Access and the results are sporadic and sometimes difficult to isolate so be careful.

    You might want to Google Access ODBC failures. There are several and the discussions offered there might give you a clue as to what might be happening with your app.

    Bill

  13. #13
    rspock is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    4
    Ok, In writing VBA code in the Splash form to reconnect a broken link to the backend I discovered I can't find any pertinent references to using a file dialog to find the correct path to the backend. I know it's a part of the Windows API so Access should have a way to use it. Can someone point me in the right direction? Note: This will be used in an environment which may or may not have an older version of MS Office in it but will definitely NOT have Access on it.

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    If you're looking for linked tables in code you can loop through tbl.def collection like I do in Post #6. There I'm setting the "new" BE for the link but you could also examine the name of the "current" BE DB.
    Code:
    CurrentDb.TableDefs(tdf.Name).Connect
    If you're in design mode simply open the linked table manager to get a complete list of linked tables and the BE's to which they are linked.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @rspock
    If the computer used for the FE won't have Access on it, then it can't be used on that computer.

    @GreagleBill
    Out of interest, what right click menu items do you have in 2003 that aren't available in 2007 or later?
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Tables
    By hermannm in forum Access
    Replies: 4
    Last Post: 02-21-2018, 03:12 AM
  2. Linking Tables
    By spyldbrat in forum Access
    Replies: 5
    Last Post: 09-10-2015, 02:07 PM
  3. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  4. Linking tables
    By jlmnjem in forum Database Design
    Replies: 1
    Last Post: 09-17-2010, 01:36 PM
  5. Linking two tables
    By nitsua0491 in forum Forms
    Replies: 2
    Last Post: 10-02-2009, 07:48 AM

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