Solved

Changing linked table location programatically on acccess 2013

Posted on 2014-02-25
9
848 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
[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
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 37

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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 37

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 37

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

752 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