Consistently having to repair a database >:< Thoughts? 5

[newyorkny]

Hi guys,

I have had work stop several times because of the ol' (paraphrase) "this is either not a database file or it needs to be repaired" message.

I have created a new database and slurped the data in, and still got the doggone message in the "all new" database! This has always worked in the past.

Can anyone think of why this is repeatedly occurring? The database is not ponderous in size -- just over 1.4MB.

Thanks for any thoughts.

NY

 

[newyorkny]

Sorry -- to clarify, I did not get the error message directly after I recreated the database. I should have said that it happened again soon after.

what causes this??!?! Thanks!

 

[KenReay]

Hi

How many users at any given time?

Is your Network stable?

Are you sure users are exiting Db cleanly and not just (say) rebooting PC ?

Less likely, but I have seen it happen, faulty Network Interface Card

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[newyorkny]

Ken,

Network is like a rock.

Up to 30 users at a given time, but, interestingly there are two databases: one is called XXX2004 and one is called XXX2005. Same number of users, but 04 is fine.

I suspect it could be folk who do not log out cleanly at night? This is a constant battle with people who either wilfully or through misunderstanding "lock" versus "log out" in the eves.

Thanks!

NY

 

[newyorkny]

Update:

Recreated the database this morning. It was at 1.4MB. Soon after being reopened for use, it ballooned to 3.8.

It is now giving the same "needs repair" message.

Is it definitely a hardware problem or are there other tricks that might reduce the likelihood of this recurring?

thanks.

 

[MoLaker]

NY,

If getting people to close your DB is a problem, you might try a hidden form and use the form's timer to monitor time of day. Have the form close the database at a certain time. You might include a warning message with a shutdown bypass in case someone is working late.

Question: Are the same 30 users using both DB's? If so, is there something about XXX2005 that would lead people to leave it open, but not XXX2004? If someone has a faulty NIC, network gurus might be able to help by looking at packet error rates for various connections. They might be able to tell you if someone is staying logged on, too.

 

[KenReay]

Hi

In addition to the items I mentioned earlier, poorly written applications can exhibit this behaviour if record locking contention is not handled correctly

Are you sure record locking "Works"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[newyorkny]

MoLaker:

I think you suggest a good approach. I will determine who is using the 2005 database and not the 04 and see if there is something up with his network connectivity.

Ken:

I did not write the application, as everything I write is like a binary Shakespeare folio and would never be adjudged "poorly written" (hehe...yeah, right!), but I didn't explain to you that 05 is just a cleaned-out 04. It is exactly the same program. However, along these lines, could you suggest any tests and diagnostics that might help me determine if record-locking IS in fact, working? The ldb file appears to be the same as any I've seen.

Thanks again, guys.

NY

 

[Hap007]

Hi,

I am entering this post a little late, but here are some thoughts that may help you.

First:
I did not see what version of Access you are using, but Access 2000 and later is very sensitive to renaming and copying forms and code. This also is true for the database name itself.

If you do copy/rename forms or code, you must open a 'Code Window' and compile the complete database. Note: you must get a clean compile or you still can have problems.

Second:
You mention that you made a copy of the prior good database.
Did you create a new db, with a new name or did you just make a copy using windows explorer.

Depending on your code, the 'Name' of the database is critical to access. So, when you first 'Create' a database, the name follows that db for life. So, later if you make a copy or rename the database, you need to open the database, then open a form or module with code, go to the Tools and database properties and set the name correcly. Then you must compile the database to set everything right.

Good Luck,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[newyorkny]

Thanks, Hap. That suggests a couple of promising avenues.

I usually create a database with a name like db1 then rename it to the desired "original" if you will name after I have slurped the data out of the ailing one.

It is A2K.

Thanks again. Will let you know how it goes. NY

 

[newyorkny]

Hap:

1) She compiled up just as happy as can be...took all of about .25 seconds and no complaints.

2) I created a new db with the desired name and then slurped data into her. I.e. "database2005."

Hopefully I have followed your guidance and the database will henceforth behave!

Thanks a mil. NY

 

[newyorkny]

Guys,

It happened again!

Is there a particular sort of network traffic that might be suggestive of a NIC malfunctioning in such a way as to cause the constantly corrupting database?

Only 14 users were on when this happened.

Does anyone have an idea as to how I might test if record-locking works?

Thanks in haste. NY

 

[MoLaker]

Depending upon the network O/S, there is typically a report available that shows errors on any given network connection (NIC). It may be viewable as real-time. Errors are usually recovered from by throwing out suspect packets and requesting a resend. But, Access is not as forgiving as MS Server or Novell when it comes to this. Have your network gurus look for a high error rate - particluraly with these 14 suspect users.

 

[MichaelRed]

A frequent cause of corruption in some versions of Ms. A. has bee the use of Autonumber in multiuser dbs. Some alledge that this has been corrected in revent releases, however I've not had the pleasure of multiuser activities w/ the alledged culprit recently.





MichaelRed

 

[newyorkny]

Hi guys,

Thanks for the ideas. Network problems have been ruled out, supposedly.

As for autonumber, that's going to be a hctib if that's indeed the case...I can't imagine working without autonumber...jeepers.

I am going to attempt to create a new database using the 2004 database, then slurp the DATA ONLY in from 2005.

Pretty sneaky, eh?

I'm sure Access will soon crush this dream, but at least it's worth a try.

NY

 

[Hap007]

Dear NY,

Well, here is another thought, but I do not know why it solved my problem, but I will offer it up. Since you have one database that works and one that fails, I would think this is not your problem, but here it is:

I had an Access97 app. Typical FrontEnd / Backend.
It was shared by 3 users/PCS

Every once in a while, the backend became corrupt.
The Drive to the shared backend was mapped as drive 'Y:'

So, one day we removed the reference to the drive and linked using the server name

Original was Y:\SomeDirectory\Somefile.mdb
New was \\ServerName\DriveC\SomeDirectory\SomeFile.mdb

This solved our problem.

Second thought, do all the users who access the xx2004 file also access the xx2005 file. I know you said that the same number of users, but are they the exact same users. If they are not, then check the PC configuration of the different users.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[newyorkny]

Thanks, Hap.

I have been attempting to determine if the users are the same, but it is a difficult process. We are a large agency and the networks are managed far, far away.

I rebuilt the database using the original template -- one that is still working for 2004 -- slurped the data in and I got the same error before too long.

It seems to me it must either be:

a) Something in the data...?!?!?!?!
b) a particular user that is using the 2005 but not the 2004.

Thanks again. NY

 

[HFloyd]

NY,

An easy way to figure out who is using which databases would be to create a userlog table in each DB. It can be really simple:
UserName (string)
Timestamp (date)
Action (string)

You can then run code or an append query when the DB is opened that adds this info to the table:

UserName | Timestamp | Action
-------------------------------------------------------
=GetWindowsUserName() | =Now() | "Opened DB"

And when they close the database properly, you can have code run that will log that event as well:

UserName | Timestamp | Action
-------------------------------------------------------
=GetWindowsUserName() | =Now() | "Closed DB"

This way you can see who is using the dbs, who is not closing them properly, and even how long users are in the db, etc.

Here is a function that I use that will get the username:

Function GetWindowsUserName() As String
'=============================================================================
' Copyright by Heather L. Floyd - Floyd Innovations - [URL unfurl="true"]www.floydinnovations.com[/URL]
' Updated 07-09-2004
'-----------------------------------------------------------------------------
' Gets the Windows username of the current user.
'-----------------------------------------------------------------------------
' Returns:  string representing Windows login username
'=============================================================================

On Error GoTo ErrorCode
    
    GetWindowsUserName = Environ("USERNAME")

ExitCode:
    Exit Function

ErrorCode:
    Select Case Err.number
        Case Else
            MsgBox "Error " & Err.number & ": " & Err.Description, vbCritical, "GetWindowsUserName"
            Resume ExitCode
    End Select
End Function

I hope this helps.

Heather



Floyd Innovations

http://www.floydinnovations.com

 

[newyorkny]

Thanks, Floyd! I am going to add that to my database as soon as the users go home tonight!

Guys:

We also have a trouble ticket in with the people who run our network -- which is colossal -- and they are going to look for dirty traffic that might be mucking up the database.

Interesting update:

A number of users were working without trouble within the database, while persons who tried to login later in the afternoon GOT THE 'THIS IS NOT A DATABASE MESSAGE!'

That must be a clue, right?!

Repeat: SOME USERS ARE USING THE DATABASE! Others, who tried to login later, ARE GETTING THE ERROR MESSAGE!

Thanks, NY

 

[MichaelRed]

Have all of the user systems been checked for (Ms. A. in particular) conformance / capatability? The latter error might arise from someone using Ms. A. ver '97 while others were using ver 2K or similar schemes.

Another issue might be references (or even version numbers of references). You may also generate a small addition to Heather's code to log the db version and you could go a lot further by generating a references list for each system.

My experience w/ Ms. A. db systems with corruption almost always resulted from some type of record locking problem. This is usually indiciated by having non alpha/numer chars show up in records.

I would also check that ALL users are referencing the same .MDW (security) file and do so in hte SAME manner.




MichaelRed