GOOD ONE! Disapearing records in Access

Just a clue, Are those number in an Autonumeric field?

No not an autonumber I don't use those things. I like to be in control. I actually keep a LastSeq on a central SQL Server and use that to populate the next X entries in a table on the local machine that has a record for each available SEQ number to the program. The program get's the smallest and deletes it. When it's running all messed up it thinks it's deleteing them because the smallest number it returns with a query increments but if you look in the table after rebooting they are still there. And I'm not using transactions for any of this.

thanks

Are you closing the connection to the sequence number file after using it?

just curious, i suspect that you have some error routines in your code somewhere. i'd check to see if one of those error routines has an err.clear and resume next. error handling in vb can be a pain if you don't understand it. essentially, if an error is encounterd, vb will follow the logic back to its origin. for example, if you have formA that has an error routine in it and forma calls formb which in turn calls formc and then calls a module function or sub and in the sub you get an error, vb will search for an error handler all the way back for forma before it crashes. so, if you have a situation like this, your code may be going places you'd never espect it to.

it also sounds as if you may be busting the upper limits of an integer (32767) and the error is being trap elsewhere.


good luck.

ed.

Hmmm... I like the last comment.
A couple of times, I have 'forgotten' about a counter that lurks in the background, and an overflow has caught me unawares - and the error been trapped as something else.

I doubt that ur changes are not reflected to the db, since access normally update the data after a while. see this link. It states about data not written to disk, depending on flush timeout/dbforceOSflush settings for transactions:

http://msdn.microsoft.com/library/devprods/vs6/vbasic/dao360/damthbegintrans.htm
http://msdn.microsoft.com/library/psdk/dasdk/migr05pj.htm

As you say that you dont use transactions, you may try to use transactions with dbforceFlush option for committrans.

cheers

thanks for the comments

mmcmillin
No I'm not closing the connection the the database. It's in a realtime environment and everything happens very quickly. I don't have the time.

eab and superchook
I'll look at it closly again but I'm pretty confident that's not it. I've been doing this for quite a while and fully understand the error handleing in VB the code is written conceptually like this

sub1
if Function1 = false then
   exit sub
end if
if Function2 = false then
   exit sub
end if
end sub

Function1 as boolean
on error goto errhandler
rsTable.addnew
rsTable!F1 = 1
rsTable!F2 = 2
rsTable.update
Function1 = true
exit function
Errhandler:
List1.additem err.number
Function1 = false
exit Function

Function2 as boolean
on error goto errhandler
"Do Something Else"
Function2 = true
exit function
Errhandler:
List1.additem err.number
Function1 = false
exit Function

I'm not getting any errors anywhere. Every routine is error handled. All routines log any error. I just don't think it's that simple.

Valli An
That's the kind of thing I've been wondering about. I'll read some more on those topics.


thanks for everything so far everyone!!

Hi calla,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Refund points and save as a 0-pt PAQ.

EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer

DanRollins,
Suggest to look at the link posted by me and you can decide if worth it or not.

hi valli_an,
I have confronted a similar problem and using tranactions did not solve it (The only solution I found is to make the 'get  ID' and the 'process that ID' into a single atomic action, such as in an SP).
-- Dan

Do you mean an AutoNumber field? If so, calla does not use Autonumber field. Anyother possibilities?

No, I do not mean an AutoNumber field.  calla specifically rejected that possibility (though it is the best technique).  -- Dan

Well, hope, can consider this:

>> Valli An
>> That's the kind of thing I've been wondering about. I'll read some more on those topics.

Also, I have faced some problems w.r.t. Access, that you do some records insert, things like that, but in Crystal reports, they do not reflect, something like this, not remember well, and then I used Transactions, the problem got solved.

Here, the user is trying to update from diff. programs, but accessing the same seq. table I believe, so using Transactions, should ensure locking and proper updating, I hope.

Thanks for your time taken. I hope, you be awarded with a lump of points for participating in so much of questions. Just the weekend, my inbox got 51 messages, (believe it or not, around 40 from EE). And I think almost all, were like reminder things.

Cheers

Thanks for the help guys. I tried to take the transaction route which didn't not correct the problem. I eventually converted all client machines to MSDE for more reasons than 1 and it has worked flawlessly. I'm convinced it has something to do with data being queued in the ldb file not getting posted and dying after reboot. Like I said during the process the applications accessing the tables were seeing the changes. Program 2 which reads the new records would see the new records written by program 1. And program 1 that would delete used sequance numbers would acknowlege the records had been deleted because the new lowest number would keep incrementing but after reboot all that had happened would disapear. Wild. I'd let this question go because I never got a real answer and killing a question is like buying something at radio shack. I'll give it a day or two for someone to come up with something brillant or just give it to valli_an since he's the on;y one that believes me.

Not that is has anything to do with the question but auto increment in most senarios is crap in my opinion. As are most other wizard like DB functions. Real life doesn't mimic text book examples.


thanks everyone

calla,
Thanks for addressing this issue.  In case you forget to finailize this, I'm leaving a note to the moderator reflecting your above-stated decision.

Moderators,
Suggested disposition:

   Accept valli_an's comment as an avswer

DanRollins -- EE database cleanup volunteer

Not the answer but in the right direction. I really don't think it has anything to do with harddisk cache since the problem would go on for an hour before noticed and then when rebooted data would be gone. Like I also said I never was able to reproduce it so it will live in the hummm basket for eternity. I'm too far removed to spend any more time on it at this point but thanks everyone.



adios
adam

Thanks calla & DanRollins.