non updatable recorset problem

[Alan B. Densky]

I have a simple one to many select query: Customers > Invoices

This query has worked properly for several years. All of a sudden I can't
update information on the Invoices side of the query and get a "this
recordset is not updateable" error. It's got to be a data thing because the
query works correctly on other "data" databases, as well as the same "data"
database from an earlier time frame.

I can compact and repair the data file without any problems, and I cannot
see any corruption in any of the fields or the Memo fields. I tried
exporting all of the tables to a new container, but to no avail.

Does anybody have any ideas?

Thanks in advance,

Alan

 

[John W. Vinson]

I have a simple one to many select query: Customers > Invoices

This query has worked properly for several years. All of a sudden I can't
update information on the Invoices side of the query and get a "this
recordset is not updateable" error. It's got to be a data thing because the
query works correctly on other "data" databases, as well as the same "data"
database from an earlier time frame.

I can compact and repair the data file without any problems, and I cannot
see any corruption in any of the fields or the Memo fields. I tried
exporting all of the tables to a new container, but to no avail.

Does anybody have any ideas?

Thanks in advance,

Alan

Drop (and note!!!) all the indexes on this table (in a COPY of the database,
keep your original). Compact the database, then recreate all the indexes.

I suspect an index has gotten bollixed... compacting *should* fix it but IME
it doesn't always do so.

John W. Vinson [MVP]

 

[Alan B. Densky]

Hey John,

Thanks! That was the problem. There may be some more problems though,
buecause I see that some of the relationships have been removed from this db
and there are some referential integrity problems now. Have you ever heard
of a db that lost its relationships?

Alan

 

[John Spencer]

Yes.

IF you compact and repair a database that has its indexes screwed up (and
especially if one of the indexes is involved in the relationship) then
sometimes the relationships get dropped. In ten years I have run into this
problem one time.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Hey John,

Thanks! That was the problem. There may be some more problems though,
buecause I see that some of the relationships have been removed from this db
and there are some referential integrity problems now. Have you ever heard
of a db that lost its relationships?

Dropping the indexes will drop the relationships... sorry, should have noted
that!!

I'd explicitly remove ALL the relationships using the code below; compact the
database; and reestablish the relationships. This database may have other
damage... watch it carefully and test it thoroughly!

Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = CurrentDb
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


John W. Vinson [MVP]