Solved

is there any way to created a linked table from the same directory and don't have a path in the linked?

Posted on 2011-02-18
9
316 Views
Last Modified: 2012-06-27
Hello guys

I have a database A and Database B in access

I created a linked table in Database B from Database A

The problem is that this linked table kept the path of my database and when I install the program in a computer that will be with another unit, that isn't C:, I have this linked table broken.

Both Databases are in the same fold, this way, I don't need the path.

Is there any way to create this linked table without the path, so that it can get the local fold where the database is?

thanks
0
Comment
Question by:hidrau
[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 9

Accepted Solution

by:
borki earned 300 total points
ID: 34924879
There are various techniques to refresh a link, some are elaborate, same are straight forward.

The easiest is to use the Linked table manager. You did not specify which version of Access you are using, but it is there, if you look for it.

A relatively easy solution using program code is this:

You create a code module and paste the following code, replacing the path and Db names for your situation. You can then create a button or simply run the public function when you install the program on either site.

-----

Public Sub RelinkSiteA()
   Call Relink("c:\pathtoDB-siteA\dbname.mdb")
End Sub

Public Sub RelinkSiteB()
   Call Relink("c:\pathtoDB-siteB\dbname.mdb")
End Sub

Private Function Relink(sDBfile As String) As Boolean
'Relink database tables from one .mdb file to another .mdb file

On Error GoTo HandleErr
   
   Dim catDB As ADOX.Catalog
   Dim tblLink As ADOX.Table
   Dim sDBold As String
   
   If (Len(sDBfile) = 0) Then
      Relink = False
      Exit Function
   End If
   
   Set catDB = New ADOX.Catalog
   'Open catalog of current DB (the one which contains the linked tables)
   
   catDB.ActiveConnection = CurrentProject.Connection
'   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name
   
   For Each tblLink In catDB.Tables
      'Check if linked table
      If tblLink.Type = "LINK" Then
         sDBold = tblLink.Properties("Jet OLEDB:Link Datasource")
         If (sDBold = sDBfile) Then
            Debug.Print tblLink.Name & " (Database already linked to " & sDBold & ")"
         Else
            tblLink.Properties("Jet OLEDB:Link Datasource") = sDBfile
            'tblLink.Properties("Jet OLEDB:Create Link") = True
            Debug.Print tblLink.Name & ": " & sDBold & " -> " & tblLink.Properties("Jet OLEDB:Link Datasource")
         End If
      Else
         Debug.Print tblLink.Name, tblLink.Type
      End If
   Next
     
ExitHere:
   Set catDB = Nothing
   Relink = True
   Exit Function
HandleErr:
   Select Case Err.Number
   Case Else
      MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly
   End Select
   Relink = False
End Function

----

Hope that helps


0
 
LVL 9

Expert Comment

by:borki
ID: 34924890
Ooh, I forget, if you use the above code, you need to set a code reference in the Tools > Reference menu for the 'Microsoft ADO Ext <your version>"

Good luck
0
 
LVL 1

Author Comment

by:hidrau
ID: 34924937
I use 2003
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:borki
ID: 34924956
Yes that code should work, the reference is to be set in the Visual Basic Environment. Also, the Linked Table manager should be installed, if you want to go that way.
0
 
LVL 1

Author Comment

by:hidrau
ID: 34924957
Borki,

I don't know much on access, I use it only for a database and call all tables in delphi.

Could you give me the steps to create this function? Where ?

thanks
0
 
LVL 85
ID: 34924967
borki's suggestion will work, bu in most cases if you're going to maintain your application in Access, you're better off using DAO methods to do this. ADOX will do the job, but Access is much more closely coupled with DAO.

If you wish to relink tables that will be in a database in the same folder as your "Frontend" (i.e. the file with all the Forms, Reports, etc), you can use code like you'll find at http://www.fabalou.com/Access/Modules/refreshtables.asp to do this. It's about as straight forward as it comes, and will work with any version of Access.

Note this would relink ALL tables in the database; if you only wish to link some of those tables, you'll have to modify this somewhat.

To use this, copy the below code into a standard VBA module, then call it like this:

RelinkTables CurrentProject.Path & "\YourBackendDatabase.mdb"


'RelinkTables...Just as the name suggests pass a path to a database to this sub
'eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99 www.fabalou.com
Public Sub RelinkTables(NewPathname As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
       For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
            Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    Next 'Goto next table
End Sub 


From here: http://www.fabalou.com/Access/Modules/refreshtables.asp

Open in new window

0
 
LVL 9

Expert Comment

by:borki
ID: 34925016
If you got limited Access experience but control of both your user sites and there aren't too many PCs involved, I would suggest to use the Linked Table Manager, it is under Tools > Database Utilities. If it is not there, you need to install it, part of the additional wizards in the instal. options

Some help to use it can be found here:
http://office.microsoft.com/en-us/access-help/view-refresh-or-change-the-file-name-and-path-for-linked-tables-mdb-HP005187623.aspx

For me it is getting late, got to go. Good luck!

0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 200 total points
ID: 34933397
Hi Hidrau, I'm coming in late to this conversation, but...

If you want a more automated approach to relinking tables, you're welcome to use our free J Street Access Relinker on our J Street Downloads page:  http://ow.ly/M56Q   It's a lot nicer than the Linked Table Manager.  And it specifically handles your need to automatically relink to a database in the same folder as the application.

It's some simple code that you copy into your front-end application.  The VBA is all there, but you don't need to understand it.  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
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 35020889
thanks
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

695 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