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
Solved

VBA Relinking of Tables from multiple back-ends

Posted on 2013-05-16
9
1,151 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 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
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)

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

839 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