Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

VBA Relinking of Tables from multiple back-ends

I wish Microsoft would make it easier to relink tables from multiple backends. If you don't select only tables from a single source you need to keep re-selecting the path to the backend you wish to link to instead of doing it once...

I have an app that has 3 different sources of data and it's a drag to relink when I need to push out test version (attached to test back-ends) to the live environment several times a week.

I have code to relink and have used it tons of times -- but can't seem to be able to specify which back-end table to link to.

Anyone have any ideas?

Thanks Experts!
0
Eileen Murphy
Asked:
Eileen Murphy
  • 2
  • 2
  • 2
  • +3
1 Solution
 
pdebaetsCommented:
Our free TableLinker utility might be what you need: http://peterssoftware.com/tlk.htm
0
 
Rey Obrero (Capricorn1)Commented:
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks I have that code - just not sure how to specify which back-end to link to. As I said, this works fine if you wish to link to the original location but I want to specify a different one.

I know I've done this before and am digging through old apps I've written to see if I can find it.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Nick67Commented:
This runs in my autoexec code
Select Case True
    Case ReturnComputerName = "touchsmart1"
        Call Refresh_Query_Link(2)
        Call Refresh_Table_Link(2)
    Case Application.CurrentProject.Path Like "M:\Azure*"
        Call Refresh_Query_Link(3)
        Call Refresh_Table_Link(3)
    Case Application.CurrentProject.Path Like "M:\Eng"
        Call Refresh_Query_Link(4)
        Call Refresh_Table_Link(4)
    Case ReturnComputerName = "admin-pc"
        Call Refresh_Query_Link(5)
        Call Refresh_Table_Link(5)
    Case Else

End Select

Open in new window


And these are the query and table relink procedures that go with it

Function Refresh_Table_Link(ConnType As Integer)
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "DATABASE=TI_Data")
        If intSubStringLoc > 0 Then
        Select Case ConnType
            Case 1
                TD.Connect = "DRIVER=SQL SERVER;SERVER=reesrdp\SQLEXPRESS05;DATABASE=TI_Data;Network=DBMSSOCN;Trusted_Connection=Yes"
            Case 2
                TD.Connect = "ODBC;DRIVER=SQL Server;SERVER=TOUCHSMART1\SQLEXPRESS08;DATABASE=TI_Data;Network=DBMSSOCN;Trusted_Connection=Yes"
            Case 3
                linkstring = "ODBC;Description=TI_Azure;DRIVER=SQL Server Native Client 10.0;SERVER=" ....stuff left out!
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
        End Select
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function


Function Refresh_Query_Link(ConnType As Integer)
On Error GoTo myerr
Dim QD As QueryDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each QD In CurrentDb.QueryDefs
    If Len(QD.Connect) > 0 Then
        'MsgBox QD.Name
        intSubStringLoc = InStr(QD.Connect, "DATABASE=TI_Data")
        If intSubStringLoc > 0 Then
        Select Case ConnType
            Case 1
                QD.Connect = "DRIVER=SQL SERVER;SERVER=reesrdp\SQLEXPRESS05;DATABASE=TI_Data;Network=DBMSSOCN;Trusted_Connection=Yes"
            Case 2
                QD.Connect = "ODBC;DRIVER=SQL Server;SERVER=TOUCHSMART1\SQLEXPRESS08;DATABASE=TI_Data;Network=DBMSSOCN;Trusted_Connection=Yes"
            Case 3
                linkstring = "ODBC;Description=TI_Azure;DRIVER=SQL Server Native Client 10.0;SERVER="....stuff left out! like pwd!
                If QD.Connect <> linkstring Then
                    QD.Connect = linkstring
                End If
            Case 4
                QD.Connect = "DRIVER=SQL SERVER;SERVER=reesrdp\SQLEXPRESS05;DATABASE=TI_Eng;Network=DBMSSOCN;Trusted_Connection=Yes"

            Case 5
                linkstring = "ODBC;DRIVER=SQL Server;SERVER=reesrdp\SQLEXPRESS05;".....stuff left out
                If QD.Connect <> linkstring Then
                    QD.Connect = linkstring
                End If
        End Select
        End If
    End If
Next

Exit Function
myerr:
MsgBox QD.Name
Resume Next


End Function

Open in new window


Now, for you, you could build a table: TableID, TableName, DevConString, TestConString, and ProdConString

Have your autoexec open a recordset, run through it until rs.EOF and set your conn strings according to some preconditons.

Mine run against file location or machine name.  Yours can run against whatever makes sense for you.  Since many tables will have the same ConnString you could normalize things to boot.

One Table: TableID, ConStringTypeID
Another table:  ConStringTypeID, ConnString
0
 
Armen Stein - Microsoft Access MVP since 2006Commented:
If you want a much easier approach to relinking tables, you're welcome to use our free J Street Access Relinker on our J Street Downloads page:

http://www.JStreetTech.com/downloads

It's a lot nicer than the Linked Table Manager.

It's some code that you simply copy into your front-end application.  It handles multiple Access back-end databases, ignores non-Access tables, and can automatically and silently relink to back-end databases in the same folder as the application (handy for work databases or single-user scenarios).  There's a ReadMe table with instructions.

Cheers,
Armen Stein
0
 
clarkscottCommented:
Here's a real easy way.
Create an Access front-end that links to the real data.
Create an Access front-end that links to the test data.

When you want to test.... simply delete all the table links in the app and IMPORT the table links fom the test Access you created.

When you want rollout - simply delete all the table links in the app and IMPORT the table links fom the real Access you created.

It's not pretty, but works... easily and fast.

Scott C
0
 
Nick67Commented:
@Scott C

The funny thing, which you can sometimes discover the hard way, is that when you import ODBC-linked tables from one db to another, most times the Unique ID that Access prompts to you supply when you go through a 'create a linked table' wizard does NOT import successfully.

So you have a lot of read-only tables
Which isn't a lot of fun
Been there, done that.

There are a variety of ways to do the deed in VBA.
They involve the TableDefs collection, some criteria to decide that a particular TableDef object needs a new connection string, changing the Connect property and issuing a RefreshLink command.

My code looks for ODBC tables -- because those are the ones that I need to update.

@Scott C is on to something though.  If you created a stub database with JUST the production tables and other unchanging objects, you could copy that and then import the forms, reports and other changed items.  THOSE don't break on import and export.

And that could be coded too, opening the Dev db as an a database object and then walking the AllForms, AllReports, etc. collections and doing stuff based on a criteria (DateModified, DateCreated etc.)
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
AWESOME TOOL! Thanks. This is just what I needed!
0
 
Armen Stein - Microsoft Access MVP since 2006Commented:
You're welcome!  Glad you found it useful.  It's our most popular download.

Cheers,
Armen Stein
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now