Solved

Changing linked table location programatically on acccess 2013

Posted on 2014-02-25
9
790 Views
Last Modified: 2014-03-01
hi

how can i change a linked table location programatically for example table1 in my database was before to linked to  a database be on c:\direcory1\database1_be.accdb and i want to change the location to c:\direcory2\database2_be.accdb
0
Comment
Question by:bill201
9 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 300 total points
Comment Utility
try this

Sub RelinkTables1()
  Dim dbs As DAO.Database, strBEPath As String
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
  strBEPath = "c:\direcory1\database1_be.accdb"

  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
      tdf.Connect = ";DATABASE=" & strBEPath
      tdf.RefreshLink
    End If
  Next
End Sub


Sub RelinkTables2()
  Dim dbs As DAO.Database, strBEPath As String
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
  strBEPath = "c:\direcory1\database2_be.accdb"

  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
      tdf.Connect = ";DATABASE=" & strBEPath
      tdf.RefreshLink
    End If
  Next
End Sub
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
Comment Utility
Is this something you need to do only once?  Do you need to allow your users to do it?  If this is just for you or you will be the only one who can relink, you can use the linked tables manager.  Just right-click on a table.  If you want to allow the user to do the relink, you need to provide a form to control it.  You will need to give him the opportunity to navigate to a new BE and then you can run the code Rey supplied using form controls instead of hard-coded references to another database.

I attached a database with a form you might find useful.  Add it to any database that has linked tables.  When you open the form, it will show one line for each different BE.  So, if you have 10 linked tables that come from 3 different databases, the form will show three lines.  The awkward part of using the linked tables manager is that you must relink a single BE at one time but the dialog doesn't give you any clean way to isolate the tables of a single BE.  You would have needed to foresee this issue from the beginning and use a prefix so you could group all the "a" tables together to make them easy to select and relink.  I never plan that far ahead so it is always an annoyance for me and that's why I built this form.

The form uses Access Themes.  If you don't have the theme I chose, it defaults to Microsoft or whatever you are using for your application.  You can change it's coloration and fonts by choosing a different theme.  The form works in A2010 and A2013.  I haven't tried it in earlier versions but it should work in A2007 also.  It probably works for A2003 but you might have to get rid of the conditional compilation.  I had to add that when one of my clients went rogue and installed the 64-bit version of Office against my specific instructions.
RelinkMultipleBackEnds.zip
0
 

Author Comment

by:bill201
Comment Utility
Many thanks to both of you, your code  works excellent, and PatHartman i love your form, it's seems nice and orderly.

but the code (from both of you) works only to link to a back end (like i have asked) but i need also a code to connect to another table (not a _be). my database it's complicated, and it's have  several tables that are linked to 3 databases, one database is a back end but the other two are regular databases. i used it's for my self, because i can't stand the regular linked table manager.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
A database is a database so I don't understand the issue.  If you are saying that you have other Access applications that have embedded tables, there is no problem linking to them.  However, it is poor practice to have a monolithic database.  In all cases, even for single user applications, best practice is to split the tables into a separate database and have everything else in what we refer to as the FE (front end) database.  The FE links to the tables in the BE.  That gives you the most stability and flexibility when it is time to make FE changes.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:bill201
Comment Utility
For some reasons I connected my database the other databases it's for other uses and I don't want to make changes to them it's just for some data tracking). I am  looking for a simple code to linked them together manually with vba (like I said I hate the normal linked manager).
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Use TransferDatabase with the acLink option to create new links.  Use the form I attached earlier to change/refresh existing links.
0
 

Author Comment

by:bill201
Comment Utility
But the code didn't work to refresh a link to a database if it's not a back end
0
 
LVL 84
Comment Utility
<please no points>
An Access database is just a database, as Pat said earlier. You don't have "Backend" databases, and "Frontend" databases - you just have databases, and you can use an Access database for either (or both - it's entirely possible to have local tables in a database being used as a FrontEnd, that are also linked in other databases).
0
 

Author Comment

by:bill201
Comment Utility
I think I realized the problem, it's a database that protect with a password so what i have to add to this code to open the database to link to a table
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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