none
VBA to automatically create linked tables from selected back-end file. RRS feed

  • Question

  • Did a search in the forum but did not find this.  I am making a front-end file with a goal to have a button to semi-automate linking to the back-end.  Pretty much I want the User to be prompted only to locate the back-end file and then use VBA to automate linking the tables from the back-end.

    Anyone achieve this or at least can point to the right direction?
    Saturday, February 11, 2017 3:07 AM

Answers

  • Yes Sir!  That is exactly what I want to do!  

    If you want to create links to all the tables in the selected back-end file that's easily done with a little bit of code:

        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        
        With Application.FileDialog(1)
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then
                        DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, acTable, _
                            tdf.Name, tdf.Name
                    End If
                 Next tdf
            Else
                MsgBox "No back end file selected", vbInformation, "Warning"
            End If
        End With

    If, on the other hand, you want to select specific tables then you could amend the code as follows:

        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        Dim strMessage As String
        
        With Application.FileDialog(1)
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs           
                    If Left(tdf.Name, 4) <> "MSys" Then
                        strMessage = "Create link to " & tdf.Name & "?"
                        If MsgBox(strMessage, vbYesNo + vbQuestion, "Link Table") = vbYes Then
                            DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, acTable, _
                                tdf.Name, tdf.Name
                        End If
                    End If
                 Next tdf
            Else
                MsgBox "No back end file selected", vbInformation, "Warning"
            End If
        End With


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Saturday, February 11, 2017 11:35 PM Typo corrected.
    • Marked as answer by Amedean Sunday, February 12, 2017 5:26 PM
    Saturday, February 11, 2017 11:29 PM

All replies

  • Hi,

    Have you tried JStreet's Relinker?

    Saturday, February 11, 2017 3:47 AM
  • Nice!  I opened the file but this is not working out-of-box.  Opened the module and it looks promising!  Do you know of a working example?  I'm getting compiling errors.

    NVM, I just opened the BETA version and I dont get compiling errors.  I will sit here and dissect this and update here where I am at.  Thanks.

    • Edited by Amedean Saturday, February 11, 2017 1:52 PM
    Saturday, February 11, 2017 1:46 PM
  • Hi,

    Good luck! Let us know if you get stuck.

    Saturday, February 11, 2017 2:26 PM
  • Okay, I copied the module and executed it using an Click below:

    Private Sub Database_Button_Click()

        basJStreetAccessRelinker.jstCheckTableLinks_Full

    End Sub

    The code sort of worked.  It did not get linked tables showing in my DB after I selected the back-end.  I then restarted the database and now when I click the same button there is no complete execution of the code.  It no longer gives me the prompt to select the database.
    • Edited by Amedean Saturday, February 11, 2017 4:23 PM
    Saturday, February 11, 2017 4:11 PM
  • I take it that you cannot dictate where the back end file is located. 

    For the sake of other, less experienced developers - the link is held in the front file.  So if you can link it once - - then you can copy that front file to others & these other users don't have to do anything at all in regard to linking.

    This assumes of course there is uniform infrastructure naming i.e. drives thru out the organization.

    As a remote developer typically my point of contact has enough skills to be instructed how to do the initial link - though sometimes I must remote into their site, make the first link - and then they copy it & deploy from there.....

    Saturday, February 11, 2017 4:24 PM
  • Hi,

    As already mentioned, you would copy the module into your front end application. If you check out the Beta, I think it uses an Autoexec macro to initiate the relinking. This way, the user does not have to click on a button, and the tables are relinked automatically, if needed, as soon as the front end app is opened.

    The way the relinker works is it checks if the linked tables are still available or not. If they are, then nothing happens, there is no relinking process needed. If the linked tables are not available, then the user gets a prompt to located the back end file.

    So, in your experiment, the first time you clicked on the button, you were prompted to locate the BE. But the next time you clicked on it, nothing happened because the tables are already linked to the BE. To continue testing the code, you can rename the BE or move it to a different folder each time before clicking on the button (or before restarting the FE, if you go the Autoexec route).

    Hope it helps...

    Saturday, February 11, 2017 4:32 PM
  • The module is copied in the front-end.  I use the button to initiate the code because my database system is intentionally supposed to function as a rapid deployable database.  I wanted to program this for the average layman to copy and paste assuming full read-write privilages.

    I did rename the back-end database and moved its location. Still have the same problem.  
    Saturday, February 11, 2017 4:49 PM
  • Okay, without being there to see what you're doing, it's hard to imagine what is happening. But just to confirm, you already have linked tables in the front end and just want to make sure the front end stays linked to them, correct? Or, are you trying to have the user select the BE and the code is supposed to make linked tables for the first time?
    Saturday, February 11, 2017 5:18 PM
  • "Or, are you trying to have the user select the BE and the code is supposed to make linked tables for the first time?"

    Yes Sir!  That is exactly what I want to do!  
    Saturday, February 11, 2017 5:28 PM
  • Okay, it makes more sense now. The "relinker" is not the tool for you then because it only works with existing linked tables. How do you plan on selecting the tables to link? For example, if the user happens to select any BE, how do you know which tables from the selected BE to link to your FE?

    In any case, to create a new linked table, you can try using the TransferDatabase method.

    I'll see if I can find a different link for you with an example of how to use it.

    Saturday, February 11, 2017 6:01 PM
  • Okay, here's a couple of links for you. I hope you find any of them useful. Let me know if you need more help.

    TransferDatabase

    Swap BE Demo

    Good luck!

    Saturday, February 11, 2017 6:08 PM
  • "Or, are you trying to have the user select the BE and the code is supposed to make linked tables for the first time?"

    Yes Sir!  That is exactly what I want to do!  

    Hi Amedean,

    In my applications I have a little (meta)data table, containing a.o. the path to the BE database. As soon as the database is places in a different environment, the difference is detected, and a routine to relink starts. After relinking the value in the (meta)data table is adepted, so that there is no further relinking in the next start up.

    The relink routines loops through all tables. If the value of the Connect property of the table contains the old path string, then the old path string is replaced by the new path string, and assigned to the Connect property, followed by a RefreshLink.

    It all runs automatically when the BE is in one of the submaps of the FE-map.

    Imb.

    Saturday, February 11, 2017 9:04 PM
  • Hi Imb,

    Maybe I misunderstood what the OP said. I originally thought the question was about "relinking" tables. But now, I am under the impression it is about "linking" tables - not "re"-linking.

    In a sense, I think he/she is looking to build a Table Linking Wizard similar to the Import Wizard except the only option is to Link and not actually import tables.

    Just my 2 cents...

    Saturday, February 11, 2017 9:58 PM
  • Maybe I misunderstood what the OP said. I originally thought the question was about "relinking" tables. But now, I am under the impression it is about "linking" tables - not "re"-linking.

    In a sense, I think he/she is looking to build a Table Linking Wizard similar to the Import Wizard except the only option is to Link and not actually import tables.

    Hi .theDBguy,

    I have been in that doubt too. Finally I concluded that when a FE is transferred from development to production, the tables were already linked, and only need to be relinked in the  production area.

    But you are right that there can be situations that unlinked tables need to be linked. Instead of changing the connect-string you need to construct the connect-string, assign it to the Connect property, and use .RefreshLink. The looping through the tables-collection stays the same.

    Imb.

    Imb.

    Saturday, February 11, 2017 10:41 PM
  • Yes Sir!  That is exactly what I want to do!  

    If you want to create links to all the tables in the selected back-end file that's easily done with a little bit of code:

        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        
        With Application.FileDialog(1)
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then
                        DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, acTable, _
                            tdf.Name, tdf.Name
                    End If
                 Next tdf
            Else
                MsgBox "No back end file selected", vbInformation, "Warning"
            End If
        End With

    If, on the other hand, you want to select specific tables then you could amend the code as follows:

        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        Dim strMessage As String
        
        With Application.FileDialog(1)
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs           
                    If Left(tdf.Name, 4) <> "MSys" Then
                        strMessage = "Create link to " & tdf.Name & "?"
                        If MsgBox(strMessage, vbYesNo + vbQuestion, "Link Table") = vbYes Then
                            DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, acTable, _
                                tdf.Name, tdf.Name
                        End If
                    End If
                 Next tdf
            Else
                MsgBox "No back end file selected", vbInformation, "Warning"
            End If
        End With


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Saturday, February 11, 2017 11:35 PM Typo corrected.
    • Marked as answer by Amedean Sunday, February 12, 2017 5:26 PM
    Saturday, February 11, 2017 11:29 PM
  • Wow!  Thank you all for your help!
    Sunday, February 12, 2017 5:30 PM
  • Yes Sir!  That is exactly what I want to do!  



    Ken, I know this has been some time ago but its almost what im looking to do. I have a ms access FE utility and from that I want to be able to select a button on my utilitys form and it create a link from a different BE and FE. Now on this utilities form I have the path to each in text boxes (txtFEMaster) and (txtBackEnd). The single table I need to create a link to is called (tbl-version_fe_master).

    How can I do this? Thanks!

    Tuesday, June 30, 2020 4:21 AM
  • Assuming that the linked and source tabledef objects already exist in both the front and back ends, to refresh the link in the front end call the following function, passing the path to the front end, the path to the back end, and the name of the table into it as its arguments:

    Public Function RefreshLinks(strFrontEnd As String, strBackEnd As String, strTable As String)

        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef

        Set dbs = OpenDatabase(strFrontEnd)
        Set tdf = dbs.TableDefs(strTable)

        tdf.Connect = ";DATABASE=" & strBackEnd
        tdf.RefreshLink

    End Function

    If you wish to create a new link in the front end call the TransferDatabase method of the DoCmd object.  See the following link for more details:

    https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferdatabase?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaac10.chm4188)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue%26f%3D255%26MSPPError%3D-2147217396

    For future reference, rather than piggy-backing on an old thread, please start a new thread and include a hyperlink to any old thread(s) to which you wish to refer.

                

    Ken Sheridan, Stafford, England

    Tuesday, June 30, 2020 11:53 AM