Solved

Change table links in VBA

Posted on 2004-04-13
5
978 Views
Last Modified: 2008-03-17
Hello Experts,

I would like to link tables to a backend database "on the fly" when a user selects an option or pushes a button etc.

How would I go about deleting the current table links and re-linking them to a new network path? The reason I am doing this is that I would like to have 1 tool that can work in any region, whereas I currently have 3 tools, 1 for each region.

Thanks in advance for your help!
0
Comment
Question by:sph3rion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Expert Comment

by:phileoca
ID: 10814388
what we do at our company is use Map Network drives.  We picked S, so all of our links go to the S drive, so no matter where we go, to connect, to the backend (which we have 2 duplicates at 2 satellite locations, 1 one via the web server) connecting to the S drive connects it to the backend

However, you can Dynamically create links, however you'll have to specify the path and name before hand... which really doesn't make it "on the fly".

<>< KT
0
 
LVL 1

Author Comment

by:sph3rion
ID: 10814813
I can specify the path and name in VBA right?

The problem is that someone set up 3 different databases with identical tables (names and format) with autonumbers and the like as primary and foreign keys, it's not a design I would have come up with but I must maintain it. So since I can't merge any of the data into one database, I need the tool to function as if it were.

I know it can be done as I have seen code that looks like it can be made to work in this way but I need some help with it.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10814955
Personally I use this function to relink to the _be.mdb in the same directory as the frontend:

Function fncRelink()
'functie om de gelinkte tabellen te verversen naar de "_be" database
'Hierbij wordt aangenomen dat de "_be" database in dezelfde map staat
'als de frontend

Dim td As TableDef

For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
     td.Connect = ";DATABASE=" & Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_be.mdb"
     td.RefreshLink
  End If
Next


End Function

But I guess it gives you an indication :-)

Nic;o)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 10814965
I have some code which I use as we use one frontend but different versions of the same database

I created this, pass in name of DB, password if any, TRUE to create links, FALSE otherwise


Public Function Install_RefreshLinks(ByVal sDB As String, ByVal sPassword As String, ByVal bCreateLinks As Boolean) As Boolean
   
    Dim dbData As Database
    Dim tdData As TableDef
    Dim sTmp As String
    Dim i As Integer
    Dim bLoop As Boolean
    Dim iAbortLoop As Integer
    Dim bDone As Boolean
    Dim iCnt As Integer
 
 
    On Error GoTo ins_err
   
    Install_RefreshLinks = False
   
'Using refreshlink keeps failing with installable isam error,
'To avoid this on other client machines, Im cheating by deleting first

    Err.clear
    iAbortLoop = 0
   
    If bCreateLinks = False Then
        Set dbData = CurrentDb
        bLoop = True
        iCnt = 0
        While bLoop = True
            bDone = False
            For Each tdData In dbData.TableDefs
                If tdData.Attributes = dbAttachedTable Then
                      dbData.TableDefs.Delete tdData.Name
                      iCnt = iCnt + 1
                    bDone = True
                End If
            Next tdData
            iAbortLoop = iAbortLoop + 1
            If bDone = False Or iAbortLoop = 500 Then
                bLoop = False
                MsgBox iCnt & " linked tables were removed.", vbInformation, APP_TITLE
            End If
        Wend
        'Now recreate links
        'If Err.Number = 0 Then bCreateLinks = True
    End If
 
    If bCreateLinks = True Then
        iCnt = 0
        If sPassword <> "" Then
            Set dbData = DBEngine.Workspaces(0).openDatabase(sDB, False, False, ";PWD=" & sPassword)
        Else
            Set dbData = DBEngine.Workspaces(0).openDatabase(sDB)
        End If
   
        For Each tdData In dbData.TableDefs
            If tdData.Attributes = 0 Then
                If tdData.Name <> "RPT_Temp" And tdData.Name <> "MainMenu" Then
                    'Cater for temp tables used by Dev but not for release
                    If Left$(tdData.Name, 3) <> "xx_" Then
                        DoCmd.TransferDatabase acLink, "Microsoft Access", sDB, acTable, tdData.Name, tdData.Name, False
                        iCnt = iCnt + 1
                    End If
                End If
            End If
        Next
   
        MsgBox iCnt & " linked tables were created.", vbInformation, APP_TITLE
    End If

    fCaller.stbEMEA.Panels(1).Text = ""
    Install_RefreshLinks = True
    GoTo ins_ok
   
ins_err:
    MsgBox "Error " & Err.Number & " trapped. " & vbCrLf & Err.Description, vbCritical, APP_TITLE

ins_ok:
    Set dbData = Nothing
End Function

0
 
LVL 1

Author Comment

by:sph3rion
ID: 10815103
Thanks very much!

I ran into some work but I will try out this code in a few hours
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question