Show Linked BackEnd

[CutAndPaste]

Show Linked Back End Database - How?

I'd like to show users which backend database they are linked to (e.g. "Training Data" or Live Data") by showing a message on a form.

Can this be done?

Thanks,

Simon

 

[MrTibbs]

Use MSysObjects tables in Access '97

Which version of Access are you using?
The following query works in Access '97 and lists all attached tables with the database they are linked from.

SELECT MSysObjects.ForeignName, MSysObjects.Database, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Database) Is Not Null))
ORDER BY MSysObjects.ForeignName;

 

[billyr]

If its DAO

For fun, I added a cmdbutton to a form and put this in the onclick event. Works for DAO.

Private Sub Command1_Click()
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
MsgBox tdf.Connect
Exit Sub 'Just need 1
End If
Next tdf
End Sub

You could put this in a form event just as easily.

 

[CutAndPaste]

Mr Tibbs - I'm using 2000.

billyr

Private Sub Command1_Click()
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
MsgBox tdf.Connect
Exit Sub 'Just need 1
End If
Next tdf
End Sub

Great thanks, I'm assuming that this will go to the first table in my db. If I've got more than one linked database for my front end (and I have!), can I specify which linked table I want to report on?

Cheers,
Simon

 

[billyr]

Specific table

As written, the procedure will report the FIRST linked table it finds. There is a name property in each table def. In this case, I think you would use tdf.name. So, inside the "if len(tdf.connect)>0" you can add another "if" to test tdf.name ( if tdf.name = "tblMyTableName" ...).

 

[CutAndPaste]

billyR,

Thanks, that worked.
Thinking about it, now I know which table I'm linking to, what I really want to display is the folder name that the database I'm linked to is residing in.

I've got two folders "Training Data" and "Live Data" each with identically named back ends in them. What I'm wanting to flag on the main menu form (switchboard) of the application is a message saying e.g. "You are connected to: c:\My Documents\Training Data"

Or just another thought, is there a way I can access the database properties that I could set in each that could be interrogated in an easier fashion? e.g. Title, Subject etc. etc. So in this case my message would read "You are connected to: [DatabasePropertyField]" where DatabasePropertyField was the database property field value set in the database properties.

Just trying to think out of the box......

Simon

 

[billyr]

OK one more

Private Sub Command1_Click()
Dim dbs As Database, tdf As TableDef, strName As String
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
If IsNull(InStr(1, tdf.Connect, "TblSet1")) Then
strName = "Set 2"
Else
strName = "Set 1"
End If
MsgBox "You are connected to: " & strName
Exit Sub 'Just need 1
End If
Next tdf
End Sub

Substitute a unique characater sequence in the connection string of one of the 2 "_be"s for my "TblSet1".
Change "Set 2" and "Set 1" to make your msgbox read like you want.

 

[CutAndPaste]

billyr,

Firstly, thanks for the swift replies, it's greatly appreciated!

I follow all you have suggested apart from:

Substitute a unique characater sequence in the connection string of one of the 2 "_be"s for my "TblSet1"..

Sorry, but exactly where does the unique character string go?
========
On the other tack (i.e. using database properties) I found some code in Access help to show properties of the current database on a form (see attachment) but I don't know how to get this to work on a linked database...

Many thanks,

Simon

(ps. The code needs a reference to DAO to work)

 

[billyr]

Here's how

Lets assume that the path to the ("_be") live data tables is c:\LIVEDATA\db1_be... If there is no other folder on c: named "LIVEDATA", those characters are unique enough. The if statement will look like this:
If IsNull(InStr(1, tdf.Connect, "LIVEDATA")) Then
strName = "Training Data"
Else
strName = "Live Data"
End If