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
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
305 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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 …
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…

809 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