Allowing Runtime users to change links

[David Crossland]

Using Access 2000/2002.
I'm designing a front end database that links to the backend data tables, I
need a way of allowing the end users (they will be using runtime) to select
which back end database they link to.
Ideally by using a Combo box drop down, or by clicking a button to select
which back end they want to connect to.

I've tried looking through the VB help files but aren't having any luck.

Can anyone help, thanks in advance.

David

 

[Nikos Yannacopoulos]

David,

Here's a piece of code I use to do that (which assumes you know what the
current back end is - se there's gotta be a default). My code checks the
Windows logon name (so it can tell if I'm home or at work, which was the
purpose for which I put it together), but should be easy to modify the
code for getting input from a user selection.

Sub change_links()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()
usr = Environ("UserName")
If usr = "ABCDE" Then
cp = "C:\Documents\Sales Statistics\"
np = "K:\Common\Sales Statistics\"
Else
cp = "K:\Common\Sales Statistics\"
np = "c:\documents\Sales Statistics\"
End If

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, cp, np)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Sub

Note: the code requires a DAO 3.6 reference, add it if not already there
(Tools > References i your VBA window).

HTH,
Nikos

 

[David Crossland]

Hi Nikos,

Thanks for your help.

I've used your code (amended it a bit), and it works ok, (as below). A
similar piece of code resets the links to "Blank" when the user closes the
front end.

A couple more questions, I have a table which has two fields, Site ID, and
file path & name, is there a way that when the user enters the Site ID, the
code looks at the table and returns the file path & name.

Instead of having to reset the links to "Blank" when the user closes, is
there a way to change the file path & name without knowing what links (cp)
are already in place.

Thanks again.

David

Sub change_links()

Dim db As Database
Dim tbl As TableDef
Dim Site As String
Dim cp As String
Dim np As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()
Site = InputBox("Which Site do you want to connect to?", "Site Required")
cp = "C:\MIP - Blank.mdb"
np = "C:\MIP - " & Site & ".mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, cp, np)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Sub

 

[Douglas J. Steele]

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
tbl.Connect = ""
tbl.Connect = ";DATABASE=" & np
tbl.RefreshLink
End If
Next

 

[David Crossland]

Hi Douglas,

Thanks for the below, its perfect, just what I was looking for,

Cheers
David