Unrecognized Database Format in Access 2003

[tweetyangel]

I have a front end and back end application. the front end is on the C drive with linked tables, queries, forms and reports. One of the tables that is used daily has a memo field. The back end lives on the server and there seems to be no problems with the server itself. Almost daily and sometimes several times a day the back end database is becomming corrupt and needs to be repaired. The data is not actually getting corrupted but there is a flag being set so the next person who logs in gets the "Unrecognized Database Format" error message. All of the users that are already logged in see no problems. I think the issue is with the Memo field because the data for that field is stored on a different page than the rest of the record.
Does anyone have any ideas on how to resolve this or maybe an idea on another design other than using the memo type field?

 

[lameid]

Typically this is caused by one of two scenarios.

One of your users is not exiting Access in a graceful manner either due to crashes or stupidity.

Second, you have linked your tables to mapped drive letters instead of the FQN network name. In this case the client is not mantaining a persistent connection to the drive mapping. Instead either use the FQN's for your links OR get your networking people to fix it.


If it is the memo field, then ensuring all users are using record locking and not page locking may help. If not, to verify the problem or reduce the problem, you could make a new table for the memo along with the same index as it has now as the Foreign Key. Put this table in a differnt backend file and link it to the frontend and make appropriate changes.

That said, let us know if none of that helps.

 

[sk8er1]

Check the memo field for #deleted.
You will have to re-enter the data

 

[tweetyangel]

I moved the table with the memo field into another database and the other database is still the one crashing. I already use FQN's for my linked tables, I am assuming by FQN you are referring to 'server\path\filename' instead of mapped drive letters. The size of the database crashing now is only 36mg since I took the table with memo field out. I increased the memory on the server from 512mg to 1gig and the database remained stable for the rest of the day and all morning the next day. This afternoon it went down and has required repairing about every 15 to 20 minutes. There is nothing in this database but local tables... the front end app has no problems, and none of the users are using end task to exit it... any ideas on what else could cause it to crash?

 

[lameid]

Hmmm... Is there any information that could be taken out of the memo field and put in separate fields or tables? Perhaps it is a customer log of sorts and you could instead use a table with a date field and text field instead? 36 MB is a lot for a table with 2 fields. If you can't take my suggestion, perhaps you could tell us more about the specifics of why and what it is used for and any relevant data structure comments.

The only other suggestion I would have at the moment is to jump to a database server product for the backend. Access 200x allows you to install and use MSDE although I don't know if there is a datatype equivalent to memo in MSDE/SQL server.

 

[tweetyangel]

It is not the database with the memo field that is getting flagged to repair. I removed the table with the memo field from the 'data' database and it is still going down.

 

[lameid]

Oops, completely misread that.

Record locking instead of Page locking is set on all the machines?

How big is the other file? How many users are in it concurrently? Does reducing the number of users help? How often are records added (i.e. 100 a day, 100 a hour)? If it is one particular user, try him or her on a different machine. If it still occurs, observe the user.

It still stinks of concurrency issues which would be solved by a database server.

 

[tweetyangel]

The file being flagged to repair is 36mg. I have less than a dozen users at a time working in it. Only about 200 records per day are being added to one table. The same number of records are added to another table in another database that is larger and it is not being corrupted. (reminder: i'm not losing data either)
Is it possible for something in the front end app to go bad and corrupt the back end? I have a log in screen and that seems to be where the error is being seen... upon login. I have moved the table referenced by the login to the front end to see if that makes a difference. I'm having the hard drive on the server switched out next Monday night too.
If you can think of anything else let me know.
Thanks!

 

[lameid]

12 users can create problems if there is a lot of simultaneous entry. But on such a small database, it is hard to guess at why. MSDE will be slower if you just upsize but I can almost guarantee your problem will completely go away.

 

[tweetyangel]

What is MSDE?

 

[GingerR]

I don't think I've read yet if you've checked Record Locking?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[tweetyangel]

Yes, Open database using record level locking is checked. Default record locking is set to edited record and default open method is set to shared.

 

[lameid]

MSDE is the crippled version of SQL server that MS shipps to be used with Access and other development packages. It basically is SQL server without the fancy tools and a database size limit (I think 2 GB). The other limitation is that it only supports 5 concurrent users... So maybe it doesn't help in a 12 user scenario. SQL server should work though.

 

[tweetyangel]

sql server isn't an option for us I don't think. We may create the tables in Oracle instead of Access though and leave the front end the same. I am not going to make any drastic changes until we change out the hard drive on the server we are using (Win2K). I will do that Monday night. If anyone has any other ideas of what can cause the database to be flagged as needing repaired when data is not being corrupted please reply.
Thanks!

 

[GingerR]

Do your users all have the same version of access? This happened with me a few years ago when everyone had Access 97 except one person who had 2000.

Another time it happened because of the way the db was designed (not designed by me, but fixed by me!!) It crashed daily. Here's the reason: once a user was done entering a bunch of data, they hit a button which performed some processing. Some of that processing was running an UPDATE query on a table that some users were in, editing records, at the same time. Because this UPDATE query was editing table data at the same time that a user could be editing a record in the same table, it cause the crashes. Daily, multiple times a day. So check to make sure you aren't running some coded table updates or something at the same time that users could be editing records.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html