Solved

GOOD ONE! Disapearing records in Access

Posted on 2001-06-07
18
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +4
18 Comments
 
LVL 16

Expert Comment

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

Author Comment

by:calla
ID: 6165327
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
ID: 6165349
Are you closing the connection to the sequence number file after using it?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Expert Comment

by:eab111098
ID: 6165561
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
ID: 6166188
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
ID: 6166292
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
ID: 6167495
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
ID: 7133432
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
ID: 7136912
DanRollins,
Suggest to look at the link posted by me and you can decide if worth it or not.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7137018
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
ID: 7137041
Do you mean an AutoNumber field? If so, calla does not use Autonumber field. Anyother possibilities?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7137166
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
ID: 7137183
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
ID: 7137565
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
ID: 7138882
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
ID: 7139703
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
ID: 7141879
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
ID: 7142375
Thanks calla & DanRollins.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month4 days, 15 hours left to enroll

636 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