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
276 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
9 Comments
 
LVL 9

Accepted Solution

by:
borki earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I use 2003
0
 
LVL 9

Expert Comment

by:borki
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:hidrau
Comment Utility
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 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

13 Experts available now in Live!

Get 1:1 Help Now