In pre-2007 versions the built in Documenter (Tools | Analyze | Documenter on
the main database menu bar) has an option to include relationships. The
result can, if wished, be output as a Word .rtf file with the 'Publish it
with MS Word' button on the toolbar.
It might be worth also mentioning that if the table and its referencing
tables are not already visible in the relationships window then right
clicking within the window and selecting 'show table' from the shortcut menu
can be used to add them.
Otherwise your reply is spot on and should enable the OP to accomplish the
task very easily.
A few supplementary points spring to mind:
1. As the column in question appears not to have had any ordinal or
cardinal characteristics in the first place, it would probably have been
better to have used a text data type from the outset even though the values
contained only numeric characters.
2. Its important that cascade updates be enforced in the relationships so
that any changes to a value in the column in the referenced table are
reflected in the referencing tables.
To largely automate the process a quick, simple and safe option would be to
import the tables into a new blank database using the following 'toolkit'
function in the new file, passing the full path of the existing back end file
into it as a string:
''''code starts''''
Public Function CopyTables(strSourcedb As String)
On Error GoTo Err_Handler
Dim dbs As DAO.Database, dbsSource As DAO.Database
Dim tdf As DAO.TableDef
Dim lngCount As Long, lngN As Long
Dim retVal As Variant
Set dbs = CurrentDb
Set dbsSource = OpenDatabase(strSourcedb)
'loop through tabledefs collection and copy tables
lngCount = dbsSource.TableDefs.Count
retVal = SysCmd(acSysCmdInitMeter, "Copying Tables", lngCount)
lngN = 1
For Each tdf In dbsSource.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strSourcedb, acTable, tdf.Name, tdf.Name
End If
Next tdf
Exit_Here:
' clear status bar
retVal = SysCmd(acSysCmdClearStatus)
Set dbsSource = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Here
End Function
''''code ends''''
Then amend the data types.
Then rebuild the relationships with:
''''code starts''''
Public Function RebuildRelationships(strSourcedb As String)
On Error GoTo Err_Handler
Dim dbs As DAO.Database, dbsSource As DAO.Database
Dim fld As DAO.Field, newfld As DAO.Field
Dim rel As DAO.Relation, newrel As DAO.Relation
Dim lngCount As Long, lngN As Long
Dim retVal As Variant
Set dbs = CurrentDb
Set dbsSource = OpenDatabase(strSourcedb)
' rebuild relationships
lngCount = dbsSource.Relations.Count
retVal = SysCmd(acSysCmdInitMeter, "Building Relationships", lngCount)
lngN = 1
For Each rel In dbsSource.Relations
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
' Create new Relation object
Set newrel = dbs.CreateRelation(rel.Name, rel.Table, rel.ForeignTable)
' Set attributes
newrel.Attributes = rel.Attributes
For Each fld In rel.Fields
' Create field in Relation object.
Set newfld = newrel.CreateField(fld.Name)
' Specify field name in foreign table.
newfld.ForeignName = fld.ForeignName
' Append Field object to Fields collection of Relation object.
newrel.Fields.Append newfld
Next fld
' Append Relation object to Relations collection.
dbs.Relations.Append newrel
dbs.Relations.Refresh
Next rel
Exit_Here:
' clear status bar
retVal = SysCmd(acSysCmdClearStatus)
Set dbsSource = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Here
End Function
''''code ends''''
The old back end file could then be renamed as a temporary backup, and the
new file renamed to the original name of the old file. The application can
then be tested, and once found to be sound the temporary backup can be
disposed of.
Ken Sheridan
Stafford, England
