Solved

VBA Relinking of Tables from multiple back-ends

Posted on 2013-05-16
9
1,041 Views
Last Modified: 2013-05-17
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
Comment
Question by:Ei0914
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39173089
Our free TableLinker utility might be what you need: http://peterssoftware.com/tlk.htm
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39173100
0
 

Author Comment

by:Ei0914
ID: 39173128
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
 
LVL 26

Expert Comment

by:Nick67
ID: 39173386
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 500 total points
ID: 39173976
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
 
LVL 20

Expert Comment

by:clarkscott
ID: 39174781
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
 
LVL 26

Expert Comment

by:Nick67
ID: 39175463
@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
 

Author Closing Comment

by:Ei0914
ID: 39175533
AWESOME TOOL! Thanks. This is just what I needed!
0
 
LVL 9
ID: 39175973
You're welcome!  Glad you found it useful.  It's our most popular download.

Cheers,
Armen Stein
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)

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now