ACC97: Duplicate Relationships Multiply in Back-End Database (164238)
The information in this article applies to:
- Microsoft Access 97
- Microsoft Visual SourceSafe for Windows 4.0
- Microsoft Visual SourceSafe for Windows 4.0a
- Microsoft Visual SourceSafe for Windows 5.0
This article was previously published under Q164238 SYMPTOMS
Advanced: Requires expert coding, interoperability, and multi-user skills.
You notice duplicate relationships appearing in a data (back-end) database
under one or both of the following conditions: - When you import tables into a new database, and the tables you are
importing are links to a data database.
- When you check in, check out, or get the latest version of the Data and
Misc. Objects in a database under source code control that contains
linked tables
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to the "Building
Applications with Microsoft Access 97" manual.
This article also assumes that you are familiar with using the client-side
capabilities of Visual SourceSafe. For more information about Visual
SourceSafe, please refer to the "Visual SourceSafe User's Guide" or the
Help topics available from the Visual SourceSafe Help menu.
RESOLUTION
Run the following custom procedure periodically in your data database to
remove the duplicate relationships that are created when you import linked
tables, or when you check out, check in, or get the latest version of Data
and Misc. Objects in a database under source code control.
Create a module in your data database and type the following procedure:
Function CleanMeUp()
Dim db As Database
Dim relFirst As Relation, relSecond As Relation
Dim bDifferent As Boolean
Dim iField As Integer
Set db = CurrentDb()
For Each relFirst In db.Relations()
For Each relSecond In db.Relations()
' Make sure the names are different, the foreign tables are
' the same, the tables are the same, and they both have the
' same number of fields.
If (relFirst.Name <> relSecond.Name) And _
(relFirst.ForeignTable = relSecond.ForeignTable) And _
(relFirst.Table = relSecond.Table) And _
(relFirst.Fields.Count = relSecond.Fields.Count) Then
bDifferent = False
For iField = 0 To relFirst.Fields().Count - 1
' If any of the fields are different, these two rels
' are not duplicates.
If (relFirst.Fields(iField).Name <> _
relSecond.Fields(iField).Name) Or _
(relFirst.Fields(iField).ForeignName <> _
relSecond.Fields(iField).ForeignName) Then _
bDifferent = True
Next iField
' If you don't find any differences, then delete the
' second rel.
If Not bDifferent Then
db.Relations.Delete relSecond.Name
End If
End If
Next relSecond
Next relFirst
End Function
To run this function, type the following line in the Debug window,
and then press ENTER.
After the procedure runs, click Relationships on the Tools menu, or open
the MSysRelationships system table in your data database and note that
duplicate relationships are gone.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97. This
problem no longer occurs in Microsoft Access 97 Service Release 1.
Modification Type: | Major | Last Reviewed: | 12/10/2003 |
---|
Keywords: | kbbug KB164238 |
---|
|