I have a need to take an exisitng Access Database and migrate the back end to SQL (I plan to use Express 2008 R2), while keeping the access front end. They want to keep the familiar front end while taking advantage of the obvious benefits of SQL. Currently there are 25 users and that access it and that could double within the next few years. Current issues include network connectivity errors, where the database needs to have maintenance ran to correct errors.
I'm done research, and am a bit overwhelmed by the options that exist as I'm pretty new to the options Access has available to it. I THINK, creating an Access Data Project is the way to go but I'd like to know peoples thoughts.
Also, I have some specific questions about this setup.
1. By doing an ADP, and having a SQL backend, will this resolve the network issues and data corruption?
2. Will this improve performance?
3. Since Access is only being used as a front end, will it still be a bottlneck in performance (relating to concurrent users)?
4. Do Windows CAL's or SQL CALs come into play here on the SQL server if Access is hosted elsewhere? (I'm assuming not)
5. Bonus: Can the Access Front end be easily converted to a Web Front End? This would probably be ideal, but only if it can be automated. The database has quite a bit of objects in it (60 tables, 200 forms, 200 reports and >1000 queries)
Any and all information would be much appreciated. I know it's out there, but b/c of my experience, tailored anwers would be very beneficial at this point in the process. THanks so much!