Solved

GOOD ONE! Disapearing records in Access

Posted on 2001-06-07
18
154 Views
Last Modified: 2010-05-02
This is not going to sound possible but I've been doing shi* like this for years and do know what I'm doing. I'm stumped. I've never been able to replicate the problem it's happended about 4 times in 9 months in full blown production. Here goes.

There's a program that adds records to a Access97 table with dao3.5 addnew method (no trasactions).All errors are displayed and logged. Another program updates thoses records shortly after. Those updated records are later inserted to another MDB on another machine and marked. The issue is every once in a while the records just aren't there. There are no errors and all the programs run as normal but I look in the table and there is nothing there. The records generated from the first app use a sequance number kept in a table as aviliable sequance numbers. As they are used they are deleted. The first generating program is recognizing them as being deleted as it uses a Select min(num) SQL to get the lowest one and the number keeps incrementing. But if I go look at the table they are still freaking there! It will do this until someone notices. I reboot the machine and start over and the sequance numbers start over like nothing was ever deleted and every thing works. There is a lot more detail to the setup but most of it seems failrly irelevant.

It's almost like the changes are being kept in a transaction log and never being commited to the datafile and are lost once the programs close but from what I know
Access doesn't do that really. I don't get it. And I can't spell, sorry.

Ahhhhh!

Help me!

thanks in advance
adam
0
Comment
Question by:calla
  • 6
  • 4
  • 4
  • +4
18 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Just a clue, Are those number in an Autonumeric field?
0
 

Author Comment

by:calla
Comment Utility
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
0
 
LVL 2

Expert Comment

by:mmcmillen
Comment Utility
Are you closing the connection to the sequence number file after using it?
0
 
LVL 1

Expert Comment

by:eab111098
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:superchook
Comment Utility
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.


0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
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
0
 

Author Comment

by:calla
Comment Utility
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!!
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
DanRollins,
Suggest to look at the link posted by me and you can decide if worth it or not.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Do you mean an AutoNumber field? If so, calla does not use Autonumber field. Anyother possibilities?
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
No, I do not mean an AutoNumber field.  calla specifically rejected that possibility (though it is the best technique).  -- Dan
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
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
0
 

Author Comment

by:calla
Comment Utility
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
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 200 total points
Comment Utility
calla,

Just check these links, if it makes sense:

http://www.superbase.com/support.htm
(Though it states of superbase, look for Windows 2000 write caching setting not persistent)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q281672
(Possible Data Loss After You Enable the "Write Cache Enabled" Feature)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q290757
(Write Caching Settings for Hard Disk May Not Persist After You Restart Your Computer)

In brief, the links state, something is not saved to hard disk, when a reboot is done, at certain circumstances.

Cheers.
0
 

Author Comment

by:calla
Comment Utility
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Thanks calla & DanRollins.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now