Solved

Changing linked table location programatically on acccess 2013

Posted on 2014-02-25
9
805 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
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 34

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

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

 

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 34

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes some very basic things about SQL Server filegroups.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 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

20 Experts available now in Live!

Get 1:1 Help Now