"Jake Jessup" <wa*********@hotmail.com> wrote in message
news:oB*******************@newssvr21.news.prodigy. com...
I'm using an Access front end to a SQL 2000 database.
I have a form from which I enter data. Everything seems fine, I check the
tables and my records are being inserted.
When I close Access and reopen it, data from three of my tables is gone.
SQL Profiler turns up nothing. If I try to access a problem table from
Enterprise manager while Access is open I get an ODBC error. If I try to
access it after I close Access, the records are gone.
Where do I start looking to figure out where the problem is? I'm at a
lost.
TIA for help.
--Jake
It sounds as if Access is holding locks on the tables, which are stopping
you seeing the data from another connections - perhaps it isn't committing a
transaction when it should? You can use DBCC OPENTRAN to check, although
this is just a guess. Or conversely, perhaps it's doing the INSERTs
correctly, but then rolling back?
In any case, rows do not suddenly vanish, so you might want to look for
triggers or cascading DRI actions which have deleted the rows without an
explicit DELETE statement on the tables themselves. If you get really stuck,
you could put a DELETE trigger on the table yourself, to record the SPID and
any other information which might help you identify the problem process,
although Profiler would normally be the best tool for this.
Simon