i Have reading a plethora of articles about Access and MSDE. It seems MSDE is a better database then access. Although i would like you database experts here to give me your opinion on what is going to work better for my situation. Its a vb.net project.
What I have is about 20 dbase III databases. Now, i can access them just fine using jet on my local machine or across a network, the problem is, is that jet doesnt support multiuser access for dbase III. No lock file is created like there is when you use a access database.
Now, I am going to distribute this app to our different companies. Each company will have its own databases. i am just guessing here, but probably 75% of the companies, there will be no need for multiuser access to the databases. The other 25% there will be some type of multiuser access. I would venture to guess that 20 users will probably be the max, and of course these users most likely arent going to be writing or reading all the time.
Now i did some testing and using dbase accross a network on a file share was VERY fast. Adding 10,000 records only took .2 seconds longer then a database local on my harddrive. And thats over a 22 mbit wireless connection. But like i said earlier, multiuser = no no.
So i have thought of 3 different options i could do. Write a class to import and export the dbase data into an access database then use the access database. I am familar with ado ( not ado.net ) and using jet.
Write a class to import/export data into MSDE. I am not familiar with SQL type databases. Although i wouldnt mind learning as long as database access is going to perform on par with access.
Use my own XML file to "lock" records and i will have to check the XML file to make sure no one else is using that record everytime before i add or do a update. - dont like this one.
I like the idea of using MSDE but, i have some concerns. Such as it was kinda pain to setup on my local machine, so it might be a pain for the other 75% who dont need the multiuser access anyway. I know you can include it in a setup file, but i am not familar with doing it. It seems to that MSDE runs as service which uses more resources then acccess.
And from my testing so far, adding 10,000 records is quite a bit slower then using access or dbase. I used an insert command, then did a executequery using a sqlcommand object for msde.
Access at first using front end and linked table backends
Remember you are going to have to support what you distribute - experimentation is not to acceptable to your clients - go with what you know, and develop towards an msde or sql solution (if the data load supports that decision)
Multiple OS platforms has never been an issue with Acces - but SQL and even MSDE administration can give you some headaches when you distribute to multiple platforms