I've been reading everything I could find on this site about switching from Access to SQL databases with a VB6 app.
Background: I market a VB6 app. which uses Access 2000 databases and is used at 300+ sites across the US to score shooting competitions. They all do a local install from an Inno-generated SETUP.EXE and many do not have internet access at the remote site. They operate on a local machine and some run a simple file sharing network to allow multiple operators. The databases aren't huge by any means, but there are a lot of records entered over a short period of time by multiple users . At the end of a large tournament there will be a total of 12-15,000 records in several different tables.
The problem I am trying to solve is that the MDB gets so much input and editing over a short period of time that it rapidly grows in physical size and may become unstable. I include a "Repair" function that users can select, which is a simple rebuild and compacting. But in order to do that, you have to get all other users out of the app, which hampers data flow and throughput AND you have to know to do it. I'd like to avoid that situation, as it can result in very agitated users calling me.
It is my impression that SQL databases are much more stable and that my problem of instability would not be a concern.
Re-coding to ADO from DAO, learning about SQL (I've been reading about MySQL), and changing over 60 Crystal Reports to use SQL databases is a large task. I am certainly willing to make the time investment if it will yield the desired result.
I saw one solution offered on this site where the developer wanted to continue with DAO. It was recommended that he link his MDB to tables in a SQL database. Seems to me I would still have the problem of an unstable MDB, since Access would be managing the table and records.
I don't need all of the capabilites of SQL Server, I just want databases that will stay healthy without manual intervention. The app must remain distributable with Inno or an msi. Having these users go in to ODBC Administrator and set up a DSN is not feasible.
I know this is long, but I would appreciate the advice of folks more knowledgeable than I.
Start Free Trial
