Hi Richard,

I have questions about “upsizing” my Access 2010 Db to SQL Server. I know that you are planning a course on SQL Server, so maybe my questions might help guide your planning in some way (or not). Anyway, hopefully you don’t mind dispensing some free advice in the meantime. Though my post is long, I'm really just looking for some yes, no, maybe responses to what I am wondering about.

Here is my quandary. I have an Access Db that is growing and growing and getting slower and slower. Adding queries to generate reports sometimes crashes it. At some point, I will try to open the thing and it is going to fail. I can just feel it. I am hoping that running parts of it off of SQL Server will help.

Here is what is in the Db, all living in a folder on the a c drive (that is copied regularly to a network that is triple backed up but not suitable for linking tables to, because it is even slower than my linked Excel sheets on the c drive).

1. Sixteen linked Excel tables with primary key ids, student test scores, dates, exam names. These tables grow and grow and grow. I update these tables in Excel and then use Access to manage the relationships among other linked Excel tables. At the minimum, I think that these tables should be moved to SQL server – but I am not sure.

2. One linked student table with essential demographic information. Like Item One above, this table also grows and grows as more students are entered. Picture lots of records and fields here.

3. A main form that pulls information from the sixteen tables and the demographic table. The form is performs essential calculations, such as showing me students, demographics, exam scores, exam dates, latest exam dates, and a bunch of other stuff that I need. It works great, but it is getting slow as my linked Excel sheets grow. There are lots of IIF statements, Max calculations, less thans and greater thans, and I figure that the form would stay in Access but draw upon the tables that I would upload to SQL.

4. A TON of queries. I have a lot of simple queries and complicated make table queries that I need to generate lots of reports. The queries contain lots of calculations, IIF statements, and other things that probably cant be moved easily to SQL Server.

So with that said, here are my questions.

A. Will I get any speed advantage if I move my linked Excel tables to a SQL server? The server would be dedicated one and I would be on a LAN connection. I am hoping that I could just move them into SQL Server, update them regularly, and have a nice, peaceful existence. Access would still be the front end for the main form and for the queries and the complex make tables, but the data would be piped in from SQL Server

B. Would I also have to move the queries and the tables that I make to SQL server, too, to see any real advantage here? That scares me a bit, because my queries are really complicated. I have to do things like generate random numbers in some fields in the queries, select max random numbers, and retrieve the max numbers for some randomized reporting that I need to do. I don’t see SQL Server doing this for me / I have no idea how to do it / don't know if it can be done.

C. Will I finally be able to share my Access 2010 front end with other users on the network this way? With the main tables on the SQL server, I am hoping that I could create Access forms that I could distribute so that other people can view the information that they need - and that we could all be doing this at the same time (about 6 people).

D. I am a noob, so maybe I am really missing the relationship between Access 2010 and SQL Server. Perhaps you could point me in the right direction or develop some course materials that would help-a-noob out in this situation.

Thanks for reading this, Richard. Looking forward to the SQL Server course that you are planning.