Solved

VBA Relinking of Tables from multiple back-ends

Posted on 2013-05-16
9
1,174 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
[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
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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