Deleting relationships with a macro

[hyperiest]

I have designed and support a data reporting system based on Access2K that allows subordinate organizations to report their data via email. unfortunately, as many of the organizations are not geographically co-located near me, I have to write all of my updates and email them to my subordinate organizations (self-extracting zip files and self-loading databases that copy over new tables, macros, etc.). In doing so, I have run across a hitch: I cannot figure out how to write a macro that will copy a new version of a table into the database if the old table had relationships. I can do it manually (a popup window appears and prompts me to delete the relationships), and although I set the SetWarnings value to "No", it will not default to delete the relationships for the old table and allow me to replace it with a new table with the same name.

Can someone out there please tell me how to do this without getting too far into the weeds with VBA?

Thanks in advance for your help.

Captain_D
"Taking Care of America's Sons and Daughters"

 

[Chalco]

Function DropConstraint(ByVal strParentTable As String, ByVal strChildTable, strMDB As String)
'----------------------------------------------------------------------------------------------------
' DESCRIPTION:
'-------------
' This subroutine will delete a constraint between the parent and child tables if it exists in the
' specified mdb file.
'----------------------------------------------------------------------------------------------------
' INPUT:
'-------
' strParentTable - The name of the parent table of the constraint.
' strChildTable - The name of the child table of the constraint.
' strMDB - The source mdb in which the target table exists.
'----------------------------------------------------------------------------------------------------
' OUTPUT:
'--------
' -1 / 0 for error or successful run of function.
'
'----------------------------------------------------------------------------------------------------
' SAMPLE CALL:
'-------------
' Call DropConstraint("ledger","House_costs", CurrentDB.Name)
'----------------------------------------------------------------------------------------------------
' HISTORY:
'---------
' RChallis - 23/07/03 - Wrote function.
'----------------------------------------------------------------------------------------------------

'DECLARATIONS:
'------------
Dim dbs As DAO.Database
Dim i As Variant
On Error GoTo ErrHandler


'MAIN BODY:
'---------
'Setup Database to either currentDB, or specified DB

If strMDB = "" Then
Set dbs = OpenDatabase(CurrentDb.Name, False)
Else
Set dbs = OpenDatabase(strMDB, False)
End If

For Each i In dbs.Relations
With i
If .Table = strParentTable Then
dbs.Relations.Delete .Name
End If
End With
Next i
DropConstraint = 0

'WRAPUP:
'------
WrapUp:
dbs.Close
Set dbs = Nothing
Exit Function

'ERROR HANDLER:
'-------------
ErrHandler:
'Handle the error here
DropConstraint = -1
End Function

 

[hyperiest]

I saw that in your code, you had it set to delete the name of the parent table, so I changed mine to reflect the child table, as it is the child table that I am trying to replace. Then I saved it and put it into my macro after the
DoCmd.CopyObject &quot;C:\CORPS-PSB.mdb&quot;, &quot;&quot;, acTable, &quot;AVAL_CD&quot; in my macro, but it says that it cannot find the find the name <<strParentTable>> that I entered in the expression.
This is what I have:

Function DropConstraint(ByVal strParentTable As String, ByVal strChildTable, strMDB As String)
Call DropConstraint(&quot;MAIN&quot;, &quot;AVAL_CD&quot;, &quot;C:\CORPS-PSB.mdb&quot
'---------------------------------------------
'RChallis: Author of Function - 20030703
'---------------------------------------------
Dim dbs As DAO.Database
Dim i As Variant
On Error GoTo ErrHandler

If strMDB = &quot;&quot; Then
Set dbs = OpenDatabase(&quot;C:\CORPS-PSB.mdb&quot;, False)
Else
Set dbs = OpenDatabase(strMDB, False)
End If

For Each i In dbs.Relations
With i
If .Table = strChildTable Then
dbs.Relations.Delete .Name
End If
End With
Next i
DropConstraint = 0

Wrapup:
dbs.Close
Set dbs = Nothing
Exit Function

ErrHandler:
DropConstraint = -1
End Function

What am I doing wrong?


Captain_D
&quot;Taking Care of America's Sons and Daughters&quot;