| re: Linking Tables in Access
"Keith" <keith.wilby@baeAWAYWITHITsystems.com> wrote in message
news:432055d8$1_1@glkas0286.greenlnk.net...[color=blue]
> "John Ortt" <johnortt@noemailsuppliedasdontwantspam.com> wrote in message
> news:432052e0$1_1@glkas0286.greenlnk.net...[color=green]
>> Hi Everyone,
>>
>> Just wonderring if it is possible to link tables in access using
>> 'relative' terminology rather than 'absolute' ?
>>
>> For example the path would become '\Sourcefile\MyPets.mdb' rather than
>> 'C:\folders\Sourcefile\MyPets.mdb'.
>>[/color]
> Do you mean in code? You could always set a string variable to contain
> the path - is this what you're getting at?
>[/color]
Not quite Keith, I found the code at the bottom of the post previously and
it has been extremely useful, particularly for removing mapped drive letter
and replacing them with server names which ensures people with different
mappings can still access the files.
What I am wonderring is if I can use a partial path (ie. relative to the
folder location of the database in use).
I ask this based on the ability to do it with html. For instance you can
either say the full path for the location of an image or you can give it
relative to the current folder.
Hope that makes sense,
John.
Option Compare Database
Option Explicit
Function ChangeLinksCode()
Dim Db As DAO.Database
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String
Dim Check As String
Set Db = CurrentDb() 'Announces that the code works on this database
oldBE = "P:\"
newBE = "\\Maasams01\Projects\"
'The two lines above give the location of the old file path (oldBE)
'and then the desired location of the new file path (newBE)
'The following code loops from i=0 (Line 1) until the last line of the table
definitions (db.TableDefs.Count - 1)
'It checks that the link is to a database (Left(lnk, 9) = ";DATABASE")
'and if so replaces all instances of the oldpath with the new one
For i = 0 To Db.TableDefs.Count - 1
tbln = Db.TableDefs(i).Name
Set tbl = Db.TableDefs(tbln)
lnk = tbl.Connect
Check = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
If lnk <> Check Then
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
End If
Next
'The next section of the code repeats that above but for non database files
(i.e. text and excel files)
oldBE = "P:\"
newBE = "\\Maasams01\Projects\"
For i = 0 To Db.TableDefs.Count - 1
tbln = Db.TableDefs(i).Name
Set tbl = Db.TableDefs(tbln)
lnk = tbl.Connect
Check = tbl.Connect
If Left(lnk, 9) <> ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
If lnk <> Check Then
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
End If
Next
End Function
Function Replace(ByVal Valuein As String, ByVal WhatToReplace As
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P > 0
Temp = Left(Temp, P - 1) & Replacevalue & Mid(Temp, P +
Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function
| 
