Database corruption Issue

[hshah]

Hello All,

I have created a database in A2K. It was working fine for past 2 years.

All of the sudden it start corrupting. When I am testing with these users on the test database for at least 2 hours I do not get any error or corruption but as soon as I start working with other databases these "VERY SMART" users keep corrupting the database and funny thing is they don’t remember what they have done last.

Does anyone know how to find out who is messing with my database?

I got reply from "mmccarthy". to open a hidden form and run "Application.Quit acQuitSaveAll" on form "Unload" event. Still they are able to do the damage.

I am desperate to get some help on this issue.

Thanks

[Rabbit]

The only way to keep track is to create a log-in for the database either through a workgroup or through your own programming and to create a log of what the users are doing.

Note that this is not entirely foolproof as it is far too easy to get around these measures for those who know how.

[hshah]

I am using workgroup file also I am also capturing all the data modification. Users can view only custom menu. Title bar is not visible. BE database is on server which is get corrupted.

Thanks

[Rabbit]

The only thing you can do is to pore through the log and inspect each item scrupulously to see what action could be causing the damage and who's doing it. I doubt there's a .Corrupt flag.

[MMcCarthy]

Check the Options - Advanced and make sure the Default Record locking is set to Edited Record and the Open databases using Record Level locking box is ticked. Do this in both the backend and frontend to ensure integrity.

Mary

[hshah]

Mary,

I checked both database and both have default record locking='Edited' and Record level locking box = 'Checked'.

Thanks

[MMcCarthy]

In that case the problem as you've previously stated is most likely caused by illegal close downs. Create a login/logout routine. When a user logs in insert a record into the table showing user, timestamp and "Login". Assuming you have an exit button then add a piece of code to insert into the table the user, timestamp and "Logout". This way you will be able to see who appears to have logged in but not logged out. They have obviously logged out illegally.

Mary

[hshah]

Mary,

Thanks again.

I did that. On startup of the database "Main Form" opens. On load event I am pulling Username, LoginTime and LoginDate.

On the same form I am adding "EXIT" button. On click of that button first it pull username, logoutdate, logouttime, save all the data and than "EXIT" application. If user do not click on the button Logout info will not be inserted.

Tested and Works great.

BUT still I have to do the copy and repair the database and relink all the users to the new database. Once database is correpted server is not allowing me to delete .ldb file. Is there a solution for that.

As always Thanks a lot.

[MMcCarthy]

Mary,

Thanks again.

I did that. On startup of the database "Main Form" opens. On load event I am pulling Username, LoginTime and LoginDate.

On the same form I am adding "EXIT" button. On click of that button first it pull username, logoutdate, logouttime, save all the data and than "EXIT" application. If user do not click on the button Logout info will not be inserted.

Tested and Works great.

BUT still I have to do the copy and repair the database and relink all the users to the new database. Once database is correpted server is not allowing me to delete .ldb file. Is there a solution for that.

As always Thanks a lot.

I'm going to point a couple of other experts in this direction who may have more experience with scripting ldb files.

Mary

[Killer42]

I'm going to point a couple of other experts in this direction who may have more experience with scripting ldb files.

The whole corruption thing is over my head, but there is one point made in this thread with which I take issue.

Mary, you have suggested setting the default behaviour on both the front-end and back-end databases. But I seem to recall that in a fairly recent thread (within the past couple of months, at least) we established that setting these options sets them for the user/PC, and not for the specific database.

If this is the case (and I'm not saying it definitely is, but we need to be certain) then the OP may not have put in place all of the precautions/safeguards that they thought.

[nico5038]

As long as an Access session is active the .ldb can't be deleted. Even when there's no database visible, an Access session can be running. You'll need to check the running processes (Ctrl+Alt+Del -> Task Manager) and kill the MSAccess.exe.

There's however in general no need to remove the .ldb as it will only hold an active user. Looks to me that you're triggering multiple Access sessions, often caused by starting the same form twice.
I would also check upon the form's that can cause your corruption. I've had similar corruptions when a bound field was deleted from the recordset of the form, but not from the form. Nasty to find, but worth a try.

Nic;o)

[MMcCarthy]

The whole corruption thing is over my head, but there is one point made in this thread with which I take issue.

Mary, you have suggested setting the default behaviour on both the front-end and back-end databases. But I seem to recall that in a fairly recent thread (within the past couple of months, at least) we established that setting these options sets them for the user/PC, and not for the specific database.

If this is the case (and I'm not saying it definitely is, but we need to be certain) then the OP may not have put in place all of the precautions/safeguards that they thought.

Problem is that the backend can be anywhere on the network and I'm not sure what instance of Access it will use. It's just a precaution.

Mary

[NeoPa]

I don't think the BE uses separate settings as it is controlled by the FE.
HShah, you mentioned implementing the logging system but not checking it ever. You do realise, I presume, that this is a tool to discover where the problem is coming from, rather than a fix to the problem per se?

[MMcCarthy]

I don't think the BE uses separate settings as it is controlled by the FE.

I agree you are probably right. However as this is an issue of corruption and I wasn't 100% sure I didn't see any harm in suggesting the precaution.

[Killer42]

I agree you are probably right. However as this is an issue of corruption and I wasn't 100% sure I didn't see any harm in suggesting the precaution.

I agree that the precautions are a good idea. My point was that perhaps they have only been applied to the OP, and not the users who are apparently causing the corruption.

Would like to get this point cleared up, if possible.

[NeoPa]

I agree you are probably right. However as this is an issue of corruption and I wasn't 100% sure I didn't see any harm in suggesting the precaution.

I can't argue with that (rare I know). Just trying to clarify though.
My second point to the OP was the important part in that post though.

[missinglinq]

I've never had much experience with corruption but two thoughts came to me reading these exchanges here. The first is that all users need to be made aware of the problem, if they aren't already aware, and warned to exit the database in the approved manner. The second is concerning who is the "culprit" here. Since the db had been running smoothly for two years and then suddenly started corrupting, maybe a look should be taken at which new users came on board recently ! Oh, a third thought! They come so seldom, I like to grab them! The database hasn't grown so much that it's reaching the 2 gigabit limit , has it? I seem to remember that this will cause an increase in incidents of corruption.

[Killer42]

I've never had much experience with corruption but two thoughts came to me reading these exchanges here. The first is that all users need to be made aware of the problem, if they aren't already aware, and warned to exit the database in the approved manner. The second is concerning who is the "culprit" here. Since the db had been running smoothly for two years and then suddenly started corrupting, maybe a look should be taken at which new users came on board recently ! Oh, a third thought! They come so seldom, I like to grab them! The database hasn't grown so much that it's reaching the 2 gigabit limit , has it? I seem to remember that this will cause an increase in incidents of corruption.

Good points. Especially about cehcking who might have started - I always try to watch for suspicious timing at work. Oh look, this program started crashing the very day that new guy changed it. What a coincidence! :D

[ADezii]

Hello All,

I have created a database in A2K. It was working fine for past 2 years.

All of the sudden it start corrupting. When I am testing with these users on the test database for at least 2 hours I do not get any error or corruption but as soon as I start working with other databases these "VERY SMART" users keep corrupting the database and funny thing is they don’t remember what they have done last.

Does anyone know how to find out who is messing with my database?

I got reply from "mmccarthy". to open a hidden form and run "Application.Quit acQuitSaveAll" on form "Unload" event. Still they are able to do the damage.

I am desperate to get some help on this issue.

Thanks

I am making the assumption that you have a Security System established on the Database with individual Log-Ins. If you don't, well, I just wasted 45 minutes of my time (just kidding). What I do in the case of suspect Users is to create an inconspicuous Log File (in this case ODBCLog.txt) buried deep in the Window's System Directory along with many other files. You can re-direct it to a Server Directory, etc. It should at a minimum pinpoint exactly who Logged in immediately prior to corruption. You can also adapt it to register when Users Log Off to pinpoint the culprit(s). Hope this is what you are looking for. Any questions, feel free to ask.

'You must Declare the API Function
Declare Function GetSystemDirectory Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Dim ReturnValue As Long, PathBuffSize As Long
Dim PathBuff As String, intOpenFileNumber As Integer
 
PathBuffSize = 255
PathBuff = Space$(PathBuffSize)     'Buffer for Return Value
 
intOpenFileNumber = FreeFile()      'Next available File#
 
'The Windows System Directory will be returned in PathBuff parameter
ReturnValue = GetSystemDirectory(PathBuff, PathBuffSize)
 
'C++ Strings returned from API calls usually have a Null Terminating Zero at
'the end - it must be removed.
Open Left(PathBuff, InStr(PathBuff, Chr(0)) - 1) & "\ODBCLog.txt" For Append As #1
Print #intOpenFileNumber, CurrentUser; " logged off at " & Time() & " on " & Format$(Now(), "ddd, dd/mm/yyyy")
 
Close #intOpenFileNumber

OUTPUT: C:\Windows\System32\ODBCLog.txt

Admin logged on at 7:26:26 PM on Mon, 12/03/2007
Admin logged off at 7:46:51 PM on Mon, 12/03/2007

[hshah]

Thanks All,

Sorry for tuning late.

Adezii,
I am using .mdw file for the database.
Does your code help to find out when they close the database or when they logout of the server folder?

Missingling, Killer42
I have send all the users an email with complete instruction how to "Log out" with the warning of tracking information will be forwarded to VP of IT.

NeoPa,
I have created an tracking for data change. So I can link users with their changes. For me if "DataChange" table is OK. Than there is no data corruption. It works fine till last month.

Mary,
I am checking all the users with their login and logout time.

Thanks all

[hshah]

Mary,

I am about to give up. Friday it happens again, twice.

I found out two of the users are using WindowsXP and rest are working on Windows2000 as OS. Does this create an issue? All are using Access2000.

Also is there a way to find out if any recordsets are open or not before Application.quit

please please help....

[MMcCarthy]

Mary,

I am about to give up. Friday it happens again, twice.

I found out two of the users are using WindowsXP and rest are working on Windows2000 as OS. Does this create an issue? All are using Access2000.

Also is there a way to find out if any recordsets are open or not before Application.quit

please please help....

If each machine has a different installation of Access go around to each machine and make sure the Default Record Locking is set on each individual machine to Edited Record.

The operating systems shouldn't make any difference.

The only way to check open recordsets would be to see who is on the database. This is done via the ldb file but I'm afraid this is not my area of expertise. One of the others may be able to help.

Just one other thing. Did you recreate the file after corruption by importing everything into a new file. The reason I asked is because in my experience it can be difficult sometimes to identify all corrupt records and only by importing them to a new database can this be identified.

Mary