Results 1 to 6 of 6
  1. #1
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31

    Can we make a loop to connect all tbls in be?

    Hi,

    I have following lines of vba code to connect BE table.

    CurrentDb.TableDefs("T_Addresses").Connect = _


    "MS Access;PWD=A*1965;DATABASE=\\inat\Admin HR\EMPLOYEES\OfferLetterDB_BE.accdb"
    CurrentDb.TableDefs("T_Addresses").RefreshLink

    I am thinking if it is possible to make a loop and connect ALL the tables in OfferLetterDB_BE.accdb. Because I have around 30 tbls in BE and for each tbl I have to write these 3 lines which is a lengthy work.

    Moreover, I may have another BE to connect with at the same location so again to write lengthy codelines.

    Is there anyway to make it in loop ?

    Anyhelp shall be appreciated..

    Regards,
    Ashfaque

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    No need to write code.
    just use the External data tool to link them in.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Instead of writing your own code, perhaps you could use JStreets auto relinker: https://www.jstreettech.com/downloads.aspx
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I use this to refresh the links to all linked tables. As you see it is taking the connection string from a field named ConnectionString in a local table named SettingsTable, but you can modify it to hard code it as a constant in the function itself (I used a public function so it could be called from the AutoExec macro).
    Code:
    Public Function vcLinkTableDefs()
    
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnectionString As String
    
    
    On Error Resume Next
    
    
    
    
      Set dbs = CurrentDb()
    
    
      ' Loop through TableDefs collection, only processing
      ' the table if it already has a Connection property.
      ' (all other tables are local ... not linked)
    
    
      For Each tdf In dbs.TableDefs
        If tdf.Connect <> "" Then
        strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
            If tdf.Connect <> strNewConnectionString Then
                tdf.Connect = strNewConnectionString
                tdf.RefreshLink
            End If
        End If
      Next
    
    
    End Function
    If your front-end is linked to multiple back-ends then you need to create a table (tblLinkedTables) with SourceTableName,DestinationTableName,ConnectionStr ing as fields. Once you have that you simply loop though it and refresh the links.

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

  5. #5
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Quote Originally Posted by Bob Fitz View Post
    Instead of writing your own code, perhaps you could use JStreets auto relinker: https://www.jstreettech.com/downloads.aspx
    Thanks Bob,

    It looks interesting.... I will try with this. Thanks for support.....regards,

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 5 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 14
    Last Post: 10-30-2018, 01:20 PM
  2. make the loop
    By tryggis in forum Programming
    Replies: 8
    Last Post: 10-07-2017, 10:06 AM
  3. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  4. How to make a loop in VB
    By darwin in forum Modules
    Replies: 5
    Last Post: 04-21-2015, 04:17 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