Access Troubles with Read-Only [Resolved]

[space_monkey]

We have a couple access databases on a server that 10 - 15 people need to use daily entering data through a form. We recently started having several problems with data corruption. Once that table would become corrupted we would have to back up from the tape of the night before. To help avoid having data corruption we created a new database and linked it back to the old one. Now everyone who opens the database can only open it as read-only. If anyone has any advice or can offer any insight what so ever, I'd appreciate it.

Thanks
Andy

[Dave Sell]

MSAccess is not designed for more than 10 consecutive users. Have you considered an actual dbase, like MySQL?

[dee-u]

You could try compacting your database every now and then, and optimize you queries as much as possible.... Maybe use ado and action queries in your data manipulation....

Or better yet, upsize your database to MS SQL Server.....

[Ecniv]

Upping to MSDE (Developer edition of SQL server has it) which is a scaled down sql server with the same size limits of Access.

Upgrade to Sql server (As mentioned above)

Access (in theory) cen support 255 users. Reality depends on how you have your database. Probably safe at 5-10 although you could have more like 20-30 (normal team size). Again, depends on how the db was set up.


As to your problem:
Check all users have read/write access to the folder
Ensure noone is openning the db as exclusive.

I am guessing that it is the first one... Possibly there is a problem with the link itself and it is being done as read only (not likely but a possibility).

Post up if its not the folder rights...

[space_monkey]

I know we are pushing the limits of what access can handle safely, but the guy in charge of the database would like to remain in control of it as much as possible.
We are pretty sure its not the folder rights b/c when they backup from the tape everyone has access to it again, but also we've gone over the permissions.
Upgrading to sql server is an option but like i said the owner of the database would like to retain as much control as possible. If we went to sql server the only option i would have (that i know of) is to create a vb frontend for it, is there another option that would allow the users to have access to the database using a form of some kind?
One thought has crossed my mind, in this database they are storing pictures as well, would this cause a problem?

Thanks
Andy

[Dave Sell]

You can set up an MSAccess front-end to MSSQL Server back-end tables. This is a common setup.

[space_monkey]

Do you know of any where i can find a tutorial on how to do that?

[Ecniv]

You have pictures in your access database??
That would expand it hugely !
Personally I would have used a shared area for the pictures and store a path to them (can be UNC if the drive letters change for users).

Tables would be linked to Sql server and act almost the same as in Access.

A link to something?
Or this?

Doubt these are of any help, but may be they are?

[FishGuy]

Firstly I am asuming your database is already split to keep the front end seperate from the backend (if not that should be done as it will reduce corruptions)

With this in mind if you were to use sql server you simply point your linked tables to the sql database instead of your access backend.

Taking this further you could then change your setup from an mdb to an adp to take advantage of stored procedures.

[space_monkey]

Thanks for everyones advice and input. As you can see my knowledge about access is very limited, so i'm sure to have more questions in the future. I think we'll start off by creating a frontend in access and a back end in access as well (split it up) and see how that goes. If we are still having problems we'll put the backend on our sql server. I've also recomended putting just the link to the pictures in the database so hopefully that will ease some of the burden on access as well. Thanks again for all your help.

Thanks
Andy

[dee-u]

This should be a good link for you.