Updating Relationships

[James]

How do you delete a table and then add one with the folowing code while
updating the relationships. When it reaches the - DoCmd.DeleteObject
acTable, FileName - it gives an error message stating the the table can't be
deleted do to the relationships. The code is below. I am trying to import
a backed-up table when the current table is bad. Everything works great but
this. Here is the code:


Public Function ImportObject(ObjectType As Integer, VersionNumber As
Integer, FileName As String) As String
On Error GoTo ErrHandler
Dim strFilePath As String
Dim strNewObjectName As String
Dim strImportName As String

'First read all files for this object
Call sInitCollection(ObjectType)

'get teh complete path to the object folder being asked for
If (ObjectType = acTable) Then
strFilePath = mcolFolders(ObjectType & vbNullString) & "\" _
& FileName & "_" & VersionNumber & ".txt"
Else
strFilePath = mcolFolders(ObjectType & vbNullString) & "\" _
& FileName & "." & VersionNumber
End If

'Make sure we know under what name was the object imported
strNewObjectName = Application.Run("acwzmain.wlib_stUniqueDocName", _
FileName, ObjectType)

If Not strNewObjectName = FileName Then
strImportName = strNewObjectName
Else
strImportName = FileName
End If

If (ObjectType = acTable) Then
DoCmd.DeleteObject acTable, FileName
DoCmd.TransferText acImportDelim, , strImportName, strFilePath, True
Else
Application.LoadFromText ObjectType, _
strImportName, _
strFilePath
End If

ImportObject = strImportName
ExitHere:
Exit Function
ErrHandler:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, vbOKOnly Or
vbCritical
ImportObject = vbNullString
Resume ExitHere
End Function

 

[Emilia Maxim]

How do you delete a table and then add one with the folowing code while
updating the relationships. When it reaches the - DoCmd.DeleteObject
acTable, FileName - it gives an error message stating the the table can't be
deleted do to the relationships. The code is below. I am trying to import
a backed-up table when the current table is bad. Everything works great but
this. Here is the code:

James,

here's an example from Access DAO help for deleting a relation:

Dim dbs As DAO.Database
Dim rel As DAO.Relation
Dim fdl As DAO.Field

Set dbs = CurrentDb
For Each rel In dbs.Relations
If rel.TABLE = "MyMainTable" And _
rel.ForeignTable = "MySubTable" Then
dbs.Relations.Delete rel.Name
Exit For
End If
Next rel

And here's an example for creating a relation:

Set rel = dbs.CreateRelation("MainTableSubTable", "MyMainTable", _
"MySubTable")
'Attributes for referential integrity
rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade

' Create a field to hold the key from main table
Set fld = rel.CreateField("MyKeyFromMainTable")
' Set the name of the foreign key
fld.ForeignName = "MyForeignKey"

rel.Fields.Append fld
dbs.Relations.Append rel


However, I'd be very careful with importing single tables when there
are relationships with relational integrity. If a table is bad, there
is a chance, some related table is also bad. Also, if the imported
older data does not match the existing data in a related table with
newer production data, you will have a (serious) problem.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de