Database Corruption over a network using VB6 and Access97

I have developed an Inventory and Sales Application for a retailing outlet. I am using VB6 (DAO) and an Access97 Database.
The database is currently 9MB and it holds 50 tables and 30 queries.
It's set up so that 5 users access a single database over a network simultaneously.
Operating system is Windows98.
I am using (IPX/SPX-compatable protocol) and (Client for Microsoft Networks).

The way I handle the mulit-user record locking is as follows. I have a  function which executes a particular SQL string using the execute command. If it throws back an error, I catch the error and check if it is possibly a locking error. If it is, then I retry a few more times, else I exit the function and tell the user to try again later.

Here is an example of this code:
-----------------------------------------------------------------------

Function NetworkExecuteSql(ByRef exeDb As Database, ByRef exeStr As String) As Integer
   
    Dim attempts As Integer
    Dim errNum As Long
   
    attempts = 0
tryagain:
    If attempts >= 300 Then
        DBEngine.Idle dbFreeLocks
        Exit Function
    End If
    On Error Resume Next
             
        exeDb.BeginTrans
        exeDb.Execute exeStr, dbFailOnError
        DBEngine.Idle dbFreeLocks
       
        NetworkExecuteSql = Err.Number
        Select Case Err.Number
            Case 0
                exeDb.CommitTrans
            Case 3186 To 3189, 3196, 3008, 3260, 3261
                attempts = attempts + 1
                exeDb.Rollback
                DBEngine.Idle dbFreeLocks
                GoTo tryagain
            Case Else
                exeDb.Rollback
        End Select
        DBEngine.Idle dbFreeLocks
    On Error GoTo 0
End Function

------------------------------------------------------------------------

There are two problems which are occurring quite often, about every 2/3 weeks at random.

First one is that while the users are using the database, it sometimes corrupts beyond repair. I have tried repairing it in Access, but it doesn't work. Sometimes a single table cannot be opened, while the other times when i go in Access design view to look at the corrupted database, all the tables are simply not there.
(Note: I am more interested in the cause of the problem then then the reparing of the database. This is just a clue to the problem.)

A second problem is where an entire cluster of records disappears from certain tables. In some of these cases database does not even register as corrupt but the records are gone.

Can anybody explain to me what is going on here. Is there a problem in my methodology of setting up the system.
Is it perhaps an Access97 or VB6 problem or a network problem or Windows98 problem or ........??????
aussie_guy_nikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abaldwinCommented:
Hello there aussie
Couple of questions for you.  
1.  Any particular reason you are using ipx/spx?  Just wondering but since it is really only needed by Novell I would suggest running TCP/IP.  Are you running on an NT server LAN or a peer to peer network.  I have found that on  P to P networks that my database apps seem to have troubles because there is no real server to hold the database.  In otherwords the "Server machine" in a P to P is usually someone's workstation.  If they are running slow (because 5 others are accessing data on their machine) they may be rebooting which could account for a lot of your problems.

2.  Do any of your users, or your code do any deleting of records?  I would check to see if a user is deleting records.

3.  Are you setting your recordsets back to nothing when you are finished with them?  If you leave them open and a user shuts down their machine that can cause some serious damage to the database.

I have multiple applications running here at the office and at others.  the P to P ones are always giving me headaches.
0
przemekCommented:
My friend has the same problem over TCP-IP network.
I think the best solution is:
http://support.microsoft.com/support/kb/articles/q172/7/33.asp
0
abaldwinCommented:
True, that is probably a good item to install.  Corruptions happen anywhere and are one of the most difficult problems to troubleshoot and prevent.  I think the main point and answer to part of my question is.  Is this a Peer-to-Peer where an end users is capable of shutting down the host machine while hosting open connections or is this a true LAN connections which would inherintly be more stable?

Just some thoughts and possibilities.

Laterdays all.
Andy

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

aussie_guy_nikAuthor Commented:
abaldwin, yet it is a peer to peer. It is running on one of the users machines. Also there have been instances where this machine where the database is on has rebooted itself for no reason. I have considered this as a major problem but, from what I have been told by the users, corruption happens even when the machine has not rebooted at that moment.

Now, there could be a possibility that it reboots and corrupts let's say in the morning and then the database becomes corrupt but maybe it doesn't throw an error until the afternoon.

Could this be likely?

If this is so, would it perhaps be best to buy another PC and use it just to put database on it and not let anyone use it?
0
Dino11Commented:
I have had this happened to me once where a similiar problem was occuring and i simply ran MSAccess/Decompile from the Start:Run menu not from within access and it solved the problem. Dont know if this will work, but for the effort its worth a shot.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just to add to what's already been said.  I'd either dump the IPX/SPX protocol for something else or switch to the Novell client.

  Microsoft has admitted to a bug in it's IPX/SPX protocol that can cause corruptions.

  Also, many virus scanners as of a late try to "fix" a MDB file and thus corrupts it.  If you have virus scanning, make sure it skips MDB files.

  And last, to aid in your troubleshooting, the first thing you need to do is pin down who is causing hte corruptions.  Download JETLOCK.EXE from the micorsoft site.  In there, you will find a utility called LDBVIEW.EXE.  By using this after the MDB becomes corrupt, you can determine which station(s) caused the corruption.

  This may help pin things down.  Just about anything that causes a network timeout (ie. bad cable, hub, NIC, NIC drivers, software, users<g>, etc) can corrupt the MDB.

HTH,
JimD.
0
abaldwinCommented:
Yes that is possible.  If you are going to run databases shared on a network.  Run NT Server, Novell, or whatever you can administer. This will help with the stability of the server machine.  Users never admit to shutting down the machine because they do not understand what it can do to the database.  

Most used excuse.   Well when word shuts down it saves the file for me....why does the database not do that.

Laterdays
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abaldwinCommented:
One other thought,    It is a user thing as well.  I have found over the course of the last several days that if the user is running the Felix the Cat PE program while accessing sQL server or access databases through either a front end or the Access DBMS itself that this has been causing corruptions.  Almost virus like.  If this is running ot the PtoP "SERVER" it is likely to cause even more problems.

We now have a policy to ban these PE programs on end users and are profiling the machines to keep them off.

Laterdays
0
aussie_guy_nikAuthor Commented:
Some good comments. Thanks all.
Just to reply to a few of those, firstly to JDettman. I have already tried using the "Jetlock.exe" and "LDbview.exe". I believe this has to be running before the corruption actually occurs. Once the corruption happens I am not sure if it would actually point to the machine which caused it. However I found you virus scanning comment very interesting. These people are using a later version of McAafee virus scanner. Perhaps that's causing it. I will look into it.

Secondly, Dino11 I have tried this before also. This only would work if there is some compiled code in Access. In this instance, I have done all the coding in VB so doing this wouldn't do much.
Being open minded, I have tried this also but no, it didn't do anything. Thanks anyway.

Last but not least, Abaldwin I have found your comments about users very amusing. You are right, they don't admitt to shutting down the machine, especially if there is trouble afterwards. If I was a user, I probably wouldn't admitt it either.
Tell me more about this Felix The Cat PE program. I don't think these people are using anything like that, but maybe something similar.

P.S. As far as Microsoft Patching is concerned, I have installed all the patches that could be installed. As far as patching is concerned I don't want to hear about it. Somebody should hang Gates by his balls and feed him all those patches. He is the one earning the money while I waste my life away working for free fixing the bugs, waiting for another patch to come out.
0
abaldwinCommented:
Felix is a PE (portable executable i think) program.  You usually see these type of programs passed around via the email.  Felix is a little black and white cat that "plays" around on your screen jumping from window title bar to window title bar.  dropping in from the top, digging holes in the screen etc.  Other PE programs have caused problems on networks before.  This is the first one that I have had a database problem with.   We narrowed some problems down ot Felix by putting it on a machine and running it .  Corrupted "locked-up" first,  No problems when felix not running.   Big problems every time felix runs.

Laterdays
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
aussie,

  No, LDBVIEW.EXE does not have to be running before the corruption occurs.  The only requirement is that you do not delete the .LDB file before using the utility.

JimD.
0
aussie_guy_nikAuthor Commented:
It's just common sense I suppose, but since there were no actual answers to this problem i'll give the points to abaldwin for his good advice.

What I am planning to do instead of installing new netowrk software is leave is as peer-to-peer but i will setup a dedicated PC to act as a server and will not be manipulated by the users. This might help the stability of the system.
0
abaldwinCommented:
Good choice on the "Server"  One step closer to NT or Novell then anyway.

Have a good evening.

Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.