Unanswered: Anyone ever had access lose data on them?
Has this ever happened to anyone? I've built a client server with an access front end and an access back end(for the moment). There are 20 users that use this program. The problem that I am experiencing this this.. Every once in a while, a value in one field in a table with about 3000 records will mysteriously go to null. I have code in place on the front end that records all user activity in that field in any form that uses it. But it has not revealed any user intervention. I have not eliminated the possiblility that a user is going into the system deleting a value from the table directly. The system in the database is updated everynight but does not always get to compacted.
QUESTION: Does anyone know of any cases where Access can lose values on its own?
Using Access 2000 with SR1 and SR2 already installed.
Yeah, access database will corrupt some data if being used by that many people. Sometimes a very carefull database design can reduce the problems.
If you want to switch database front/back ends... then if you want something easy to set-up and don't have a problem with money, go with FileMaker Server backend and FileMaker Pro front end. If time is not a concern and money is then use something like PostgreSQL as a backend with some web based PHP or PERL front end.
For 20 users, I wouldn't advise buying Microsoft SQL Server.
Are the 20 users adding data to the database!?
If this is truth, then consider to change to Oracle databases, because it's to many data at the same time. Or you have a server that runs very, very, very, fast or don't think about putting anything, else Oracle, as a back end database.
Yes, you're right when you say that's expsensive, but is the best way for never getting problems, as far as i know, all the companys that have more then 20 users and have to dysplay lots of data uses Oracle.
That databases you're refered, are they good? Because i don't know anything about them and how they works!
If you use Access 2000, you also have on your installation CD the Microsoft Desktop Engine MDSE.
That is a limited edition of SQL-server, I believe it won't run database-files larger then 2Gb or more than five concurrent users (not quite sure). Maybe these limitations makes it a test-only option for you. Anyway, it enables you to play around with a SQL Server environment.
MSDE is not installed bij default, you find setupsql.exe on the CD at \SQL\X86\Setup. For testing purposes, transfer your datatables to an MSDE database-backend, that will make sure there is no unexpected dataloss in the data-files. Access has an Upsizing Wizard that does half the job.
Keep in mind that moving from a file-driven Access-database to a server driven datasource (SQL-server, or Oracle or whatever) in general needs quite some study on database server principles.
Keep your Access forms etc as the frontend to the SQL-data, then you are able to check whether it is really not your front-end that is causing the trouble. Personally I would have believed that 20 concurrent users on an Access database should be possible.
I would go with PostgreSQL or MySQL. Those two databases are the back-end of a lot of large websites. The can stand up to 100's of users with the right design and the right server. Best of all... they are FREE (open-source).
Access is just not built for 20 users, and I think Oracle is very good but probably a little more power than you need.
Thanks for all the advice everyone. I looks like the general consensus here is that i should switch to a different back end. I think that's what i will do. RE the question about updating every night and compacting some nights, I have a utility that updates the backend with new data from some of our other systems AND compacts the back end (because i have some make queries). Anyhow becuase our users here do not always log out of the access system the nighly compact does not always run.
Starting a database compact with maybe some users still logged on to the database, or logged out in an imcomplete way (leaving the .ldb file on disk) might initiate your trouble. Normally you would like to be logged on as an exclusive user to perform this kind of maintenance with the Access Jet engine.
You are obviously familiar with using MSAccess...recommend you look into using the MSDE that Marion referenced. It is like a limited version of SQL that is "optimized" for 5 users. What does that mean? Well for 5 users it's performance would be comparable to running a SQL2000 server with 5 users on it. That doesn't mean it is capped at 5 users. You can put more people on it, but the performance will start to degrade. And this of course depends on how heavily people are running the thing. If there are 20 users on an Access back-end, then I'm sure that performance is already degraded, and you are getting data corruption. Using MSDE should give you better performance than your current solution, and solve your data corruption problems....I have heard of people running 40+ users on MSDE with decent speeds...not SQL server speed, but acceptable.