Newbie : Need Information on Migrating Data to SQL

[ebarb]

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!

[pkstormy]

Just create an mdb and then link the SQL Server tables into the mdb (via an ODBC DSN). Working on an mdb versus an ADP is much easier and there's no real advantage to an ADP. You can design your MSAccess forms as unbound forms which essentially is the same then as an ADP. I prefer an mdb versus an ADP and it'll make your life easier. ADP is not something Microsoft supports very well. Consider that developing an ADP is usually 2-3 times longer than an mdb.

Yes! It will definately increase performance using SQL Server tables versus using MSAccess tables.

If you want the ideal situation, you can put the frontend on the user's desktop. You then avoid any connection/bottlenecks to the network drive (since it's a direct connection to the SQL Server tables and the mdb is on the user's desktop.) But you have to weigh this benefit with the need to update code and copying a new mdb file to everyone's desktop. The bottleneck comes into play when the mdb is on the network drive and, if the network is slow or problematic.

Use another web development program and php scripts versus MSAccess for your web. MSAccess is not the ideal solution for creating web designs.


There are some good examples I've posted in the code bank here: http://www.dbforums.com/microsoft-ac...code-bank.html

see:
1. The post on using a vb script to launch an mdb file (page 2). Using the vb script, you could have a 100+ concurrent users and the mdb will preform much more efficiently (I recommend compiling the mdb to an mde though for the users.) - http://www.dbforums.com/6274786-post19.html
2. The post on automatically creating an ODBC for the user via vba code (page 5 I think). - http://www.dbforums.com/6282465-post37.html

Regarding your questions:
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? - Yes if you put the mdb on the user's desktop. No to data corruption. This is more of a coding/table design issue. But SQL Server does a much better job of handling data updates than an MSAccess table does.
2. Will this improve performance? - Yes. Definately.
3. Since Access is only being used as a front end, will it still be a bottlneck in performance (relating to concurrent users)? - see above. The network drive is the main bottleneck but most network guys do a pretty good job of keeping the network drive performing optimal. See vb script to handle multiple concurrent users. This will make your life much easier!
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) - not sure. I don't understand.