Headlines!!!! Compact errors and corruption in the database! 2

[PROXI]

Ok. I have a FE/BE Database. It is in a networked office with about 20 people that use the database. About 3-10 times per day the backend corrupts itsself and I have to repair it. It builds a table when it corrupts called "MSysCompactError" and has a list of errors. The 2 error numbers that I am getting are -1206 and -1504. Does anyone know what these are or how to fix them. If anyone can let me know I would greatly appreciate it.

Thanks,

PROXI

 

[willir]

Info on errors...
1206 Non-db file or corrupted db
-1206 0xFFFFFB4A JET_errDatabaseCorrupted non-db file or corrupted db 4294966090

1504 Null not valid
-1504 0xFFFFFA20 JET_errNullInvalid Null not valid 4294965792

A review of the literature suggests that you should make sure verybody is running the same version of Access, same level of patches, and the same Jet Engine.

Crashing 3 to 10x a day is very excessive.

You probably could run the Microsoft updates for the local OS and Office. One stop shopping at ...

http://www.microsoft.com/downloads/updateservices.aspx?displaylang=en

Other possibilities include somebody with a bad network connection, caching writes (caching writes is a no-no for databases)

 

[PROXI]

what exactly are caching writes? We do often have network problems in this office (even though we are a frikin multi-billion dollar company). So that could be the problem is just our network?

Thanks,

PROXI

 

[willir]

Proxi

When you commuincate across the LAN or WAN, you send packets of information. The packets are encapsulated by each "layer" from the application layer to the hardware layer. The data packets include header information, among other things, the desitination, etc. On the recieving end, the encaspualtion is stripped off, hardware to applicaiton layer. This is a load of overhead.

In order to improve network performance, server performance, and desktop performance, the systems like to make sure they are carrying a full load so to speak. Why carry a quarter of the load and make four trips when you can make one trip with a full load - so to speak. The buckets are referred to as buffers.

This works fine when working with large spreadsheets, or word documents, etc. But it can be a serious problem with databases (including eMail databases located on a server). This is because the caching, or storing information in buffers while waiting for bufer to get full, means that the "writes" can be delayed.

One user access a record, and starts editing it - the record has been cahced, and the record action has been cached on the desktop. Consquentlty, the system at the server end is unaware the record is being edited. Another user accesses the record, edits it and commits the change, the "write" is updated on the server end. Now the first user also updates the record. This can lead to changes by the original user over writing the second user's changes. but it can also lead to corruption, especially, if the primary key gets messed.

Back to network issues. A bad network connection can lead to chatter. In complete packets are sent. If this happens while the end user is updating the database, it could also cause corruption.

I would work on the version of Jet Engine first, and the version MDAC. (The web site I provided in my earlier post should address this problem provided you do not have a locked down environment)

It would also be helpful to try and isolate which user or users is causing the problem. Time of day and who is on and did you have the Access open type of detective work may help. Perhaps having the end users exiting out of the application each time may help you isolate the problem.

 

[PROXI]

Ok... I was thinking of a solution to this after reading your previous post. Would it help with the overhead to do something like this --
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 -- in the afterupdate of each field?

Or would that cause too much growth in the database? I have that in a few choice fields right now and didn't know if that would help or hinder this task?

Thanks,

PROXI

 

[willir]

PROXI

Saving the record is fine. But if the data is cached, it may not update the BE database in a timely manner. You have 20 users, and I don't know how heavy they are hitting the database. There are a lot of variables to consider. Regardless, committing the record should not hurt the system.

For example, with Novell, one of the default settings for the Novell client is to copy the file locally - one step deal greatly improves performance, especially when working on large spreadsheets. Now I have not seen this happen, but imagine if the entire database was cached locally for all users....

One other piece of info. Access, in it's native format, does a pretty poor job effecient networking. When you open a form tied to a table, Access copies the entire recordset -- all records in the table. This can genegerate a lot of traffic if you have a large tables. (Recent post on thi issue:

http://www.tek-tips.com/viewthread.cfm?SQID=840745&SPID=702&newpid=702&page=3

)

...Moving on.
In trouble shooting, it is useful to "divide and conquer" -- isolate the problem, rule out causes. The fact that you are crashing so often suggests you have a major issue. Typically a major issue can be traced to one user, or one event that consistantly causes the problem.

If this database was running fine for a period of time, then you recently started having problems, I would look at what changed - new user, recent patch rollout, recent rebuild of a desktop, etc.

 

[PROXI]

Ok... now building on the previous stuff... Would it help to change the forms to unbound instead of binding them to a table or query?

Thanks,

PROXI

 

[willir]

That is certainly one way to do it. Using totally unbound forms takes a bit of work - you have to populate every field manually. This way, very little data is copied across the network.

You can also bring across a minimum records set - the primary key, and a couple of search fields type of thing. Don't forget -- any combo or list boxes on a form will each bring acros their own records set. If this is a small set, say - States or Provences, it is not a big issue, but if a combo box for example hits the invoice table, then this information. Remeber that a combo or list box usually only bring 1, 2 maybe 5 fields over, and not the full record - look at the query used for the record source for the combo / list box for specifics.

But do we know if this is what is causing your corruption yet? This is a lot of work, and it may or may not solve your corruption problem.

Richard

 

[mikey69]

I run a FE/BE system with 30 plus users, mostly Access XP, without problems. Reading through all the posts, I would suspect network software, rather than Access. I'm not aware of different versions of Access causing problems, provided, of course, that the BE is the same version as the oldest FE.

Access makes all things possible. It even makes them intelligible