Corruption in Access 2003

[cas20002000]

I just converted an Access 97 db to Access 2003,
it has a SQL Server 7 backend.

Everything seems to be working fine, except the new version gets corrupted frequently.

it always have an avg of 5 users using the database.
(max. 10 users)

Any ideas of why this is happening?

Thanks for your help,

 

[Ed2020]

I'd need to see the database front end to comment properly, but you may have more luck giving the users a copy of the front end on their local drives (one each).

To ensure all users are using the latest frontend version put a version table in the frontend, a version table in the backend and perform a consistancy check between the two.

When I have done this I write a batch file to copy the latest frontend over the to the local drives when necessary.

Ed Metcalfe.

Please do not feed the trolls.....

 

[BNPMike]

Make sure your XP and 2003 environments have all the latest patches. Also, as you only have a few users, why not go to MSDE (ie SQL 2000)?

 

[Ed2020]

From what I understood of the post it's the Access frontend that is getting corrupted. I can't see that moving to a different backend will make any difference....

Ed.

Please do not feed the trolls.....

 

[BNPMike]

From what I understood of the post it's the Access frontend that is getting corrupted.

I still don't see that from what's written, but the XP desktop aspect is more likely to be causing problems.

 

[Ed2020]

I guess we'll have to wait for cas to confirm, but I still think it sounds like the frontend is what is corrupting.

Surely if the desktop environment is the cause then is more likely the frontend.....?

Ed.

Please do not feed the trolls.....

 

[Ed2020]

Corruption in 2003" would suggest the corruption was in the Access end of the app too I would have thought.

Please do not feed the trolls.....

 

[cas20002000]

The corruption is only on Access 2003 frontend.
All tables are linked (to SQL server 7).

Is this problem related to the conversion from 97 to 2003?
Would the database corrupt the same way if I redo all the forms from scratch in 2003?

Thanks for your help....

 

[Ed2020]

Is this problem related to the conversion from 97 to 2003?" - If it is then it'll be the first time I've come across it.

Try releasing an MDB frontend for each user. I have found lots of users accessing a single frontend can cause corruption problems.

Ed Metcalfe.

Please do not feed the trolls.....

 

[cas20002000]

I'll guess that's what I'm going to have to do....

It's frustrating because It's been working perfectly with Access 97 for the last 6 months (since I moved the tables to SQL Server); now that I'm starting to move the users to 2003, gets corrupted very easily with minimum use.

Thanks to all for your help.

 

[BNPMike]

Would the database corrupt the same way

just when you'd all persuaded me it was the front end that was getting corrupted...

If you have your database getting corrupted then you need to consider the way both elements are working together. SQL 2000 might just be more compatible with 2003.

It does however seem odd that ODBC can damage your database. What happens to the data?

 

[cas20002000]

In the beginning, I moved the tables from Access 97 to SQL server because of the same problem, we were loosing too much data. It was the easy way out.
Since I moved the data to SQL server (using Access 97 as the front-end), I got 0.001% data loss.
All of this started with just a couple of users using Access 2003.(all users will eventually will have to move to 2003)

I would like to migrate to SQL server 2000, but this cannot be done due to budget problems.

To move all the code to VB, will take at least 6 months,
and I guess this is the real solution for me.

Thanks again for your support...

 

[Ed2020]

Now I'm confused! lol

What's getting corrupted - the frontend, backend or both?

Have you considered using an ADP rather than an MDB?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

I think if I was in your position I'd try:

1. Give all users a locally held MDB frontend. This will be the minimum amount of effort and, even if it doesn't work, it may tell you if it is specific users causing the corruption.

2. If no. 1 doesn't give the required results migrate the MDB frontend to an ADP - the amount of effort involved in this will vary dependant on how you have designed your frontend.

Ed Metcalfe.

Please do not feed the trolls.....

 

[cas20002000]

Only the frontend gets corrupted.
I need to start reading about ADP's.

Thanks again...