Disappearing Data -- SQL 2000

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"Jake Jessup" <watcherdude@.hotmail.com> wrote in message
news:oBc1d.14931$QJ3.11293@.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|||Jake Jessup (watcherdude@.hotmail.com) writes:
> 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.

It sounds like you start a transaction which you never commit, so when
you close Access, the whole transaction is rolled back, and the data
you entered goes poof.

I assume that that ODBC error in EM says "Lock timeout expireed" or
somesuch?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote >
> I assume that that ODBC error in EM says "Lock timeout expireed" or
> somesuch?

Yes, that's exactly it.

I'll look for the transaction processing and make sure that the transaction
is getting committed.

Thanks for the tip.|||That did it Erland, I appreciate your help. I guess I just went brain dead.

Most of my work has been with regular Access and I forgot about commiting
transactions.

--Jake