Varibale backend locations still not working

  • Thread starter Thread starter Richardson
  • Start date Start date
R

Richardson

I recently posted to this group asking for help linking one front end
database to variable backend end databases. I have attempted to implement
the solution that was posted, but I can't seem to make it work right. I am
including the setup information I have used and I am looking for any help
you can provide to get this functioning this week.

Thanks in advance,
Lori

Table with backend locations (File Locations)
BeName - text field with the name of the backend selection
BeLocation - text field with the location on the backend

Form where the user can select the locations (frmFile_Location_Selector)
cbBeName - combo box with 2 columns, BeName and BeLocation
txtBeLocation - Text box that references column 2 of the combo box
cmdSelect - Command button on the form with On-Click event procedure as
follows: ( got this code mostly from the previous post)

Private Sub cmdSelect()
With Me.txtBeLocation
RelinkTables (Forms![File_Location_Selector]![txtBeLocation])
End With
End Sub

The Module is RelinkTables with the following in it

Public Sub RelinkTables (NewPathName as String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = Currnt Db
Set Tdfs = Dbs.TableDefs
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then
Tdf.Connect = ";DATABASE=" & NewPathName
Tdf.RefreshLink
End If
Next
End Sub
 
Lori,

I 've been using this method extensively... I believe the problem is you
need to reset the connect string before you assign a new one, like this:

If Tdf.SourceTableName <> "" Then
Tdf.Connect = "" 'ADDED LINE
Tdf.Connect = ";DATABASE=" & NewPathName
Tdf.RefreshLink
End If

Give it a shot.
Also, I noticed that whereas your form is called frmFile_Location_Selector,
by what you say below, in the main sub the reference to the combo is:
Forms![File_Location_Selector]![txtBeLocation]
This doesn't seem correct; you have to have the exact form name there.
I suggest you check the value of NewPathName passed to the second sub, to
make sure it is what you expect, so you know your reference above is
correct.

HTH,
Nikos
 
Back
Top