Thread: Upsize Access 2000 Question
06-29-07, 19:17 #1Registered User
- Join Date
- May 2006
Unanswered: Upsize Access 2000 Question
I have been using front-end back-end database, which consists of three .mde files connected to an .mdb backend on a Windows Server 2003 Windows XP Professional network. The database is a flat-file. Because of problems associated with record corruption, related to multiple users and memo fields, I am looking to upsize my backend to MySQL or SQL Server both of which I know little about. Additionally I am hoping that upsizing will help alleviate the slowing down I am currently experiencing as the back end grows.
My question is am I looking in the right direction will either of these correct my problems and if so which of the two should I use.
Thanks in advance..
07-05-07, 17:30 #2Registered User
- Join Date
- Feb 2004
- Chicago, IL
I recently migrated to SQL Server 2005 with a Access 2002 frontend. My database(s) are much faster than before, but I don't know how to measure the change in performance since I rewrote some parts I knew weren't well desinged for Access alone.
My only experience with mySQL is when I tested a website that used mySQL as the backend. I was very impressed with the speed.
The biggest difference between the two is the cost. SQL Server $$$$, mySQL free. I wanted some exposure to SQL Server so I recommended it for us to use at work.
To optimize the performance on a SQL Server backend, I believe you will want to convert any DAO recordsets to ADO. You will also want to create an .ADP instead of a .MDB unless you are willing to manage forms that are unbound. I opted for the ADP route which replaces regular Access queries and tables with those in the SQL Server database that you link to.
Keep in mind if you don't have a lot of users you might be able to get away with the free version of SQL Server (Express Edition). I don't think it has much in the way of managing the databases you create but the previous MSDE version did have a web interface tool for it.
I think you didn't get any responses to your post because there is a lot of information to discuss. I have only hit a few here but I do think you will see some pretty good improvements if you were to upsize to SQL Server. You will also have some growing pains like I did. The terminology is different (views instead of queries), the syntax is different, you have to manage the server (add users, change permissions, etc.).
07-05-07, 17:52 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
If I had to choose, I'd choose SQL Server Express. People may think mySQL is faster but you end up paying for that speed when you want to create triggers, stored procedures, etc..and you end up spending 2-3 days trying to find work-arounds for something you can easily do with SQL Server. Personally, I think SQL Server beats any other database servers for ease of use. But I may be bias since I've used SQL Server for so long.
When I used mySQL, I spent twice as long creating, modifying, setting up backups, user permissions, etc. on a simple database which I did in half the time with SQL Server.
I'm not a fan of ADP. I've gotten along great just linking the tables into an MSAccess mdb file and found I couldn't create those nice complex/nested queries and reports in ADP as easily as I can with MSAccess. An ADP will take twice as long to create as an mdb will (unless it's a very basic application).
Either mySQL or SQL Server will definately increase the speed though (unless you have some really bad code which no database server in the world will fix).
Last edited by pkstormy; 07-05-07 at 18:03.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)