Question 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!

 

[HiTechCoach]

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!

Welcome to AWF!

By SQL I will assume you mean SQL Server (SS)

SQL is a query language used by most current databases including Access (JET/ACE the default database engine in Access). When you create a query in Access you are using generating SQL.

What version of Access are you using?

IIRC, you will need Access 2007/2010 to work with SQL Server (SS) 2008. Access 2003's ADPs would best with SQL Server (SS) 2000 is you want to be able to modify the tables/views/store procedures from within Access 2003. You can connect to a SQL Server 2005 with Access 2003 ADP but you can NOT modify the tables/views/store procedures from within Access 2003.


For some great links see:

SQL Server links


The issue of CALs depends of the version of SQL Server (SS) you are connecting to as the back end.


If you are interested in converting to a web based front end you might consider using Access 2010. I prefer Thin Client for web access. This may also help: Running an Access database over the internet

 

[G81]

sorry I cant answer all questions, but here's my 2 cents

1. By doing an ADP, and having a SQL backend, will this resolve the network issues and data corruption?

Not necessarily but sql is alot more efficient, especially with stored procedures and I've never seen corrupted data, even with our databases which are a few GBs and are hammered all day long.

2. Will this improve performance?

Almost certainly

3. Since Access is only being used as a front end, will it still be a bottlneck in performance (relating to concurrent users)?

I dont think so. Ive only ever seen bottlenecks on the sql server side when major queries and reports are running. We can usually resolve them by looking at the queries and making them more efficient and/or adding indexes.

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)

depends what version of SQL, but I think if it's hosted elsewhere you need a "per server CPU" licence. That's per socket not per core.