Hi Nick
Well, blow me away! You are absolutely right! They don't appear in the
relationships window and they're not in the Relations collection. I shall
report this as a bug.
As a workaround, you will need to open the backend database as a separate
database object. Here is a function that will return you the filename and,
optionally, the connection string (you will need this for the password) and
the name of the source table (in case it's different from the local name).
Public Function GetBackEndName( _
sTable As String, _
Optional sConnect, _
Optional sForeignName _
) As String
Dim rs As DAO.Recordset
On Error GoTo ProcErr
Set rs = CurrentDb.OpenRecordset( _
"Select Database, Connect, ForeignName from MSysObjects " _
& "where Type=6 and Name=""" & sTable & """", dbOpenForwardOnly)
If rs.RecordCount = 0 Then
Err.Raise vbObjectError, , "'" & sTable & "' is not a valid linked
table"
Else
GetBackEndName = rs!Database
If Not IsMissing(sConnect) Then sConnect = rs!Connect
If Not IsMissing(sForeignName) Then sForeignName = rs!ForeignName
End If
ProcEnd:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description, vbExclamation
Resume ProcEnd
End Function
You can use it like this:
Dim db As DAO.Database, rel As DAO.Relation
Dim sBackEnd As String, sConnect As String, sSourceTable As String
sBackEnd = GetBackEndName("TableName", sConnect, sSourceTable)
Set db = OpenDatabase(sBackEnd, False, True, sConnect)
For Each rel In db.Relations
If rel.Table = sSourceTable Then
... do something here
End If
Next
BTW, did you know that if you link tables in a "password-protected"
database, then anyone can read the password in plain text in your
front-end's MSysObjects table??
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Nick said:
Graham
Have read your comments with interest. Like scubadiver, the group level
permissions scares me so I have also adopted a simple password on the
back-end mdb file. This works Ok except that I find that the back-end
relationships are no longer visible in the front-end database, though stil
enforced of course.
As I use the relationship collection to check on associated records when
deleting items, this causes me problems.
I've tried deleting and re-attaching the linked tables to no avail.
Any idea what's going on?
Nick
scubadiver said:
I have a problem with the front end file. I have tried opening the main
form
and it says "not a valid password". What is going on?
:
Ultimately you can make the decision about what goes in the front-end
and
what goes in the back-end, but the rules are simple:
1. All tables (with a few possible exceptions) go in the back-end and
everything else goes in the front-end.
2. The exceptions are tables which contain data that drives the
application,
rather than data which is managed by the application. These might be a
table of menu/switchboard items, or a table of names and descriptions
of
monthly reports.
The method you describe will not protect the back-end data. Unless you
properly implement user-level security on the back-end, anyone can
create an
empty database and link your back-end tables to it, thus gaining
unrestricted access to the data.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Security concerns are not my forte.
When I split the database can I decide what goes in the front end and
what
goes in the back end or does Access do it for me?
Is the best way to protect the back-end file simply a start up screen
with
a
password login and password button to enable and disable the shift
key?
thanks
