linked tables

[Katrina]

I have a database set up in a front end, back end manner.
The tables in the front end are linked to a read only copy
of the database on our network. The file systems is set
up with these folders:
Databases (has the front end database in this folder)
Read-Only COpy (Has back end)

We want to have the capability to copy the whole folder
Databases (which includes Read-Only Copy) to a hard drive,
and have the linked tables look on the hard drive instead
of the network. I know how to fix this temporarily, but I
would like to know if there is a way to set up the links
so that the database looks to the folder underneath the
current folder.

I need to be able to use the database without access to
the network and I would like to be able to copy the folder
at any time with out having to make any changes to the db
after copying the folder.

Katrina

 

[Roger Carlson]

Here is a function I use that runs at startup. It determines if the linked
tables are available and if not, tries to like them from a subdirectory
called PivotData below whichever directory the database is in. The form
"frmLinkingTables" is just a little static form that informs the user it is
relinking tables.

Public Sub Startup()
On Error GoTo Startup_Err
Dim tdf As TableDef
Dim strD As String
Dim strC As String
Dim strIMEX As String
Dim db As DAO.Database
Set db = CurrentDb

strD = getpath(db.Name) & "pivotdata\"

DoCmd.OpenForm "frmLinkingTables"

For Each tdf In CurrentDb.TableDefs
If Len(Nz(tdf.Connect, "")) > 0 Then ' non-local
If InStr(tdf.Connect, strD) = 0 Then ' need to
reconnect
Set tbl = db.CreateTableDef("DetailRemote")
tbf.Connect = (";DATABASE=" & strD & tdf.Name & ".mdb")
tbf.SourceTableName = "Detail"
db.TableDefs.Append tbf
End If
End If
Next
DoCmd.Close acForm, "frmLinkingTables"

Startup_Exit:
Exit Sub

Startup_Err:
If Err.Number = 2450 Then 'form is missing
MsgBox "Cancelling Linking"
Resume Startup_Exit
Else
MsgBox Err.Description
Resume Startup_Exit
End If
End Sub



--Roger Carlson
www.rogersaccesslibrary.com

 

[Katrina]

Thanks, this looks like it will be helpful, but I can't
quite get it to work.

I don't think I use the DAO method, but I changed it a bit
to look like the code below. The problem is that it pulls
up the form, and then tell me "Object Required" Do you
know what it's looking for? I can't find the error,
because I don't really understand all of the commands


Function startup()
On Error GoTo Startup_Err
Dim tdf As Object
Dim strD As String
Dim strC As String

strD = Application.CurrentProject.Path & "Read-Only
Copy\"

DoCmd.OpenForm "LinkingTables"

For Each tdf In CurrentDb.TableDefs
If Len(Nz(tdf.Connect, "")) > 0 Then '
non-local
If InStr(tdf.Connect, strD) = 0
Then ' need to reconnect
Set tbl = db.CreateTableDef
("DetailRemote")
tbf.Connect = (";DATABASE=" & strD &
tdf.Name & ".mdb")
tbf.SourceTableName = "Detail"
db.TableDefs.Append tbf
End If
End If
Next
DoCmd.Close acForm, "LinkingTables"

Startup_Exit:
Exit Function

Startup_Err:
If Err.NUMBER = 2450 Then 'form is missing
MsgBox "Cancelling Linking"
Resume Startup_Exit
Else
MsgBox Err.Description
Resume Startup_Exit
End If
End Function

 

[Roger Carlson]

Yes. In my haste, I gave you incorrect code. I pieced it from a couple of
apps without checking it well enough. Try this instead:

For Each tdf In CurrentDb.TableDefs
If Len(Nz(tdf.Connect, "")) > 0 Then
strFilename = Dir(Mid(tdf.Connect, 11))
If InStr(tdf.Connect, strD) = 0 Then
tdf.Connect = (";DATABASE=" & strD & strFilename)
tdf.RefreshLink
End If
End If
Next

Here's what the code does:

1) If Len(Nz(tdf.Connect, "")) > 0 Then
This checks the length of the Connect property of tdf. If it is greater
than zero, it is a linked table (ie, non-local)

2) strFilename = Dir(Mid(tdf.Connect, 11))
This line reads the connect string and returns just the filename from it

3) If InStr(tdf.Connect, strD) = 0 Then
If the value of strD cannot be found in the Connect string, then it is not
linked to the table in the subdirectory

4) tdf.Connect = (";DATABASE=" & strD & strFilename)
This redefines the connection to link to the subdirectory

5) tdf.RefreshLink
Refreshes the link

Now, this also supposes that the version on the network is also in a
subdirectory similar to the local machine.

--Roger Carlson
www.rogersaccesslibrary.com

 

[Roger Carlson]

Actually, there is one more correction <sigh>. The following line:
strFilename = Dir(Mid(tdf.Connect, 11))
doesn't always work with mapped drives. This is better:
strFilename = Mid(tdf.Connect, 11)
strFilename = Mid(strFilename , InStrRev(strFilename , "\") +
1)

It could also be done on one line, but it is more confusing:
strFilename = Mid(Mid(tdf.Connect, 11) ,
InStrRev(Mid(tdf.Connect, 11) , "\") + 1)

--Roger Carlson
www.rogersaccesslibrary.com