delete row blocked by non-existant relationship

[Leslie S.]

Running Access 2003 on Windows 2000.

I have developed a database, no data in it yet. Once I was almost finished
with my basic design, I had to change the field name and size that I am using
for my primary key in one table and delete the unused field of the same name,
size, type from another. I checked the relationships window and selected show
all relationships. In the first table, no relationships had been established
yet. In the second table one relationship exists but not to the field in
question.

Next, I opened the first table in design view. I changed the name of the
variable and then tried to change the size of the field. An error message
popped up telling me I couldn't change the size of the field because of a
relationship that had been established. It told me to modify the relationship
in order to make the change.

In the second table I tried to delete the field and received the same error
message.

I double checked the relationship window and it was exactly as I stated
above.

On an off chance the last thing I did was select Tools > Database Utilities

Compact and Repair Database. No change.

Please help. I can recreate these tables but would rather not.

 

[Sharkbyte]

Leslie:

I'm going to guess that there is still a relationship out there, somewhere.
You could try to look up dependencies, and see if it is there.

As an option, you could create a second field, with all the correct
information, and switch it to the PK, and then remove the offending field.
However, I would expect Access to tell you it needs to delete a relationship
to do so. And I expect you will find the broken relationship as you begin
testing the db.

Good luck.

Sharkbyte

 

[John Vinson]

Running Access 2003 on Windows 2000.

I have developed a database, no data in it yet. Once I was almost finished
with my basic design, I had to change the field name and size that I am using
for my primary key in one table and delete the unused field of the same name,
size, type from another. I checked the relationships window and selected show
all relationships. In the first table, no relationships had been established
yet. In the second table one relationship exists but not to the field in
question.

You might need to use this small nuclear device (back up your database
FIRST):

Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = DBEngine(0)(0)
For inti = db.Relations.Count - 1 To 0 Step -1
Set rel = db.Relations(inti)
Debug.Print "Deleting relation "; rel.Name, rel.Table,
rel.ForeignTable
db.Relations.Delete rel.Name
Next inti
End Sub


It does just what it says - deletes all relationships, visible or not.
You can adapt it to be more selective (for instance by checking the
Table and ForeignTable properties before deleting the relation).

John W. Vinson[MVP]