Solved

Changing linked table location programatically on acccess 2013

Posted on 2014-02-25
9
815 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 39887805
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 35

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 39887896
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
ID: 39887947
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 35

Expert Comment

by:PatHartman
ID: 39889002
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
 

Author Comment

by:bill201
ID: 39889181
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 35

Expert Comment

by:PatHartman
ID: 39889201
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
ID: 39889222
But the code didn't work to refresh a link to a database if it's not a back end
0
 
LVL 84
ID: 39889435
<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
ID: 39889873
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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