Programatically change linked tables

[stricknyn]

Hello, all,

Wondering if there is a way to do this...
I have a front end user application in Access and two back ends. The two backends are identical. The difference between the two back ends are that one is for development and the other is live (users hit this via the front end). Normally the front end is linked to the live back end. There are times when I need to do developmental work and I want to hit the developmental back end for testing.

Normally, I can use the linked table manager to relink my front end to the developmental back end. There are alot of linked tables so this becomes a time consuming process to relink the tables every time.

I was wondering if there is a way to do this via code? Then all I'd have to do is hit a button and it would link me to the developmental back end or the live back end.

Thanks in advance!

Strick

[randem]

stricknyn,

In Access there is a re-link function from the menu. Go to Tools->Database Utilities->Linked Table Manager. This will allow you to change all your linked tables to the other database with Just a click.

There is also a way to do this function by code. I dont remember it right now but I will see if I can find the code.

[stricknyn]

Hello,

Yes, that's the way I normally do it. I was looking for a way of doing it via code.

Strick

[pnish]

Here's some code using ADO & ADOX to link external tables. I also have the DAO equivalent (somewhere) if you're interested.

Public Sub AttachTable(ByVal srcDB As String, ByVal srcTable As String, _
    ByVal destDB As String, Optional ByVal destTable As String = "")
    '
    '   Create a link to a table in a database.
    '   If destTable > "" then rename the table in the destination database
    '
    Dim cat As ADOX.Catalog
    Dim con As ADODB.Connection
    Dim tbl As ADOX.Table
    
    Set cat = New ADOX.Catalog
    Set con = New ADODB.Connection
    Set tbl = New ADOX.Table
    
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & destDB
    
    Set cat.ActiveConnection = con
    '
    '   Determine whether we're renaming the destination table
    '
    If Len(destTable) > 0 Then
        tbl.Name = destTable
    Else
        tbl.Name = srcTable
    End If
    Set tbl.ParentCatalog = cat
    '
    '   Set the link properties
    '
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = srcDB
    tbl.Properties("Jet OLEDB:Remote Table Name") = srcTable
    '
    '   Append the table to the collection and we're done
    '
    cat.Tables.Append tbl
 
    Set cat = Nothing
    Set tbl = Nothing
    
    con.Close
    Set con = Nothing
 
End Sub

[randem]

stricknyn,


I found the code. It's verrrry old but should still work. It opens a table with all the table names and the database that they are in, then attaches to that database and links the tables for all names in the table.

Function SetupAttachedTables() As Integer
 
    Dim TblName As String
    Dim dbCurrent As Database
    Dim dbTable As Recordset
    Dim Errors As Integer
 
        Set dbCurrent = CodeDb()
        Set dbTable = dbCurrent.OpenRecordset("Attached Tables", dbOpenDynaset)
 
        dbTable.MoveFirst
 
  '      On Error GoTo CountAttachErrors
        Errors = 0
 
        Do While Not dbTable.EOF
 
            TblName = dbTable![Table Name]
            dbName = dbTable![Database]
 
            DeleteTable TblName              'Delete old table reference
 
            DoCmd.TransferDatabase A_ATTACH, "Microsoft Access", dbName, acTable, TblName, TblName
            dbTable.Edit
            dbTable![Attached] = Now
            dbTable.Update
 
            dbTable.MoveNext
 
        Loop
 
        dbTable.Close
        SetupAttachedTables = Errors
        Exit Function
 
CountAttachErrors:
        
        Errors = Errors + 1
        Resume Next
 
End Function

[Ecniv]

Hi,

Not sure about the DAO code there but what you want to do is something like this (possibly)

1) Delete all linked tables
2) Open the other (external) db (test or live)
3) Loop through and link tables in the current db, using the table names from the external db

** Note: you will probably not need to link the "msys*" tables

If you are using DAO then please try a looping code first. If you are still having problems, then post up your code for assistance/guidance.

[stricknyn]

Thanks, guys,

I'll try this code out when I get home and let you know how it turns out.

Strick

[swoozie]

How did the code work?

I am looking for an ADO option for relinking tables. I have a DAO version but I am looking for ADO version.

Thanks,

Swoozie

[pnish]

Have a look at post #4 in this thread....

[swoozie]

Yeah, That is the one I have been looking at. I just wanted to know if everything worked, or if any issue were come accross. I was trying to use something similar and had issues. however the code is #4 seemed simpler than what I had so before I removed my working DAO code I just wanted feed back on what the outcome of this post was.

Swoozie

[pnish]

That code is basically what I use and it works fine. You will probably want to add some error handling though.

[Ggalla1779]

Personally I would never relink a development database with live one.

I normally create a completely seperate folder or drive area for testing databases and the live system.

When making changes to the data database just copy over the new objects to live and replace em or get users out...fill em with live data.

The trouble with the relink code is there is a chance you will forget where your Frontend is pointing...and you could plough on doing development to Live system.

I just wouldnt take any risk......