Linked Tables path.

[Nick J]

Hi,

I have my front and back end database (they are in development) on a network
drive. Everytime I start the database up I get #Error? on DLookup objects
and such. This is solved by 'updating' the tables through the Linked Table
Manager. The path is S:\databaseBE.m db. Can I just not set the path to
database.BE.mdb ?

Because when i distribute the database to others users, the back end
database is going to be in the same folder as the front end database, and on
top of that they are probobly not going to have network drives, especially
ones with the drive letter 'S' if you get me.

I also have that problem with linked images. I have a report that keeps
changing the path I specify. I specify db_files\logo.J PG and access keeps
changing it to the full path (eg: S:\db_files\log o.jpg).

Any thoughts and help would be appreciated. thanks!

--

Kind Regards...

Customer Services Team
Blue Bell Trading

+++ WHEN REPLYING PLEASE DO NOT DELETE ANY OF THE TEXT AS WE NEED IT FOR
REFERENCE +++

Blue Bell Trading

[AP]

I havent used this for a whiile, but I used to setup a button to
refresh links it would open a form with a text box where the user could
browse to the back end db and press OK, then the following code would
run. It basically would check for one table and if that table existed
in the backend it woudl continue trying to link to all of the. I would
store the name of the linked tables in a table named 'tblClientTable s'

Private Sub cmdLink_Click()
Dim dbs As Database
Dim tdf As TableDef
Dim connections As Recordset
Dim connect_string As String
DoCmd.Hourglass True
' change the connection to the control_informa tion table
' and check if there is a problem with that. If not, change
' all the other connections. If there is, warn user and kick them
back to
' the choose valid connection.

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs(" tblEvents") ' Checks for this single table
to make sure it is a valid backend, replace with any table of your
choice that is in the backend

connect_string = Me.txtFileName ' could add a browse function to
populate this box or type it in manually

tdf.Connect = ";DATABASE= " & connect_string
' Err = 0
' On Error Resume Next
'tdf.RefreshLin k ' Relink the table.
'If Err <> 0 Then
' MsgBox "Data path is invalid", , "Operation Cancelled"
'DoCmd.Hourglas s False
'Exit Sub
' End If

Set connections = dbs.OpenRecords et("tblClientTa bles")
If connections.Rec ordCount = 0 Then
Exit Sub
End If

connections.Mov eFirst
While Not connections.EOF
Set tdf = dbs.TableDefs(c onnections![table_name])

tdf.Connect = ";DATABASE= " & connect_string
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
MsgBox connect_string & " data path is invalid", , "Operation
Cancelled"
DoCmd.SetWarnin gs True
DoCmd.Hourglass False
Exit Sub
End If
connections.Mov eNext
Wend

MsgBox "Tables Linked to " & connect_string & " were Refreshed", ,
"Operation Successful!"
DoCmd.Hourglass False
DoCmd.Close

Exit_cmdchange_ Click:
Exit Sub

Err_cmdchange_C lick:
MsgBox Err.Description
Resume Exit_cmdchange_ Click

End Sub
Let me know if this works for you.