Does anyone know of any good Access/SQL classes on the web?

I am needing to learn how to use SQL as the backend for some of my Access Databases.   Does anyone know of some good web based classes that teach you how to work in this SQL/Access environment?
CRGmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Since the SQL people don't acknowledge Access as a life form, you may not find any classes let alone have a choice.  It doesn't matter though.  If you are working with linked tables, you will be using Access SQL in queries for most of the FE work.  Occasionally you will need to create a pass-through query for some bulk update.  If you have heavy batch processes, you will almost certainly need to use stored procedures.  Pass through queries use T-SQL syntax as do stored procedures, so finding a class on straight SQL Server SQL will work for you.
Jim HornMicrosoft SQL Server Data DudeCommented:
SQL Server

Pragmatic Works Free Webinars - The focus is on Business Intelligence (SSAS, SSRS, SSIS), but includes a lot of SQL.

PASS - 24 Hours of PASS - Every once in awhile developers around the world will create a one-hour free presentation on a SQL topic of their choice.

PASS - SQL Saturday  Coming to a city near you, a one-day, five-ish session of free SQL Server training.  I've done this for four years, and in 2013 presented on my article SQL Server:  T-SQL recipe to create a million sample people

And you can always eyeball my article Migrating your Access Queries to SQL Server Transact-SQL, and search EE's articles to see if anyone else wrote an article that deals with both SQL Server and Access.  There are a lot of articles written by some excellent EE experts in both SQL and Access, not sure how many include both.

And if you have four grand to burn and four days in October, come join us at the premier training conference for SQL Server PASS Summit.  EE SQL Server expert ValentinoV and I attended in 2012, and I'm going back this year.

Good luck.
Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DoveTailsCommented:
Two links that I have bookmarked you may find helpful:

http://www.w3schools.com/sql/

http://www.fontstuff.com/access/acctut14.htm

Cheers
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

CRGmanAuthor Commented:
Thanks everyone for your comments!
Jim HornMicrosoft SQL Server Data DudeCommented:
Thanks for the split, but why the 'Good' answer?  
The question was 'What are Web-based classes to convert from Access to SQL', we've given you a lot (other than one comment which looks like somebody just did a Google search and posted the results) and I don't see any feedback here that our answer was less than an A grade.
CRGmanAuthor Commented:
Jim,

I liked your post the best but I was not able to find a specific class on creating any Access FE/SQL BE databases.   I have come to the conclusion that there are no good solutions for this.   I have to make a decision now about how I should proceed with several databases I have.   I am not an IT guy, I am a finance guy that knows a little about Access and virtually nothing about SQL.  

I probably should have allocated more points to you than others.

Royce Pruitt.
Jim HornMicrosoft SQL Server Data DudeCommented:
>I liked your post the best but I was not able to find a specific class on creating any Access FE/SQL BE databases.  
Come to think of it I don't think there are, but just in case I'll invite a couple of experts that are real Access rock stars into this question to see if they know of any.
CRGmanAuthor Commented:
Thanks!!   I appreciate that.

Royce Pruitt
Jim HornMicrosoft SQL Server Data DudeCommented:
Just to frame this up better, it would be helpful if you can explain to us why you wish to go Access FE - SQL BE, instead of a straight Access solution.
CRGmanAuthor Commented:
Ok,  My current database that I am having a problem with is bumping the 2 gig barrier.   The database is part of our payroll processing and contains a data warehouse for payroll data that is used to do several things.  One of which is computing eligibility for Obama Care.   It is also used to track Tips (we are a restaurant company) that is used for various reporting to the IRS.   In addition it is used to store data needed for reporting on Obama Care at the end of the year.   We have around 2000 employees on any given year so there is a lot of data.    Unfortunately there are no solutions out in the marketplace that integrates with our payroll software that does not cost less than $60,000/year (subscription fee).

I have never seen any user friendly FE for SQL.   I have been told that Access can be an front end for SQL.   I am willing to learn how to make that happen but I will need structured classes to help.  

Royce Pruitt

Hope this helps.
CRGmanAuthor Commented:
P.S...how can I allocate more points to you for your help in this matter?   Royce.
Jim HornMicrosoft SQL Server Data DudeCommented:
I'll un-accept the answer, but hold of on awarding points for now and let's see what kind of other interest we can get here..
Jim HornMicrosoft SQL Server Data DudeCommented:
Access expert LukeChung-FMS, who owns the FMS company of Access addins, has a couple of articles that you might find useful
When and How to Upsize Microsoft Access Databases to SQL Server, which is a decent explanation of whether you should upsize or not.
Microsoft Access within an Organization's Overall Database Strategy, a whitepaper I've read a couple of times to explain to those Access antagonists the role Access can play in a company.

There's lots of 'Here's how you can upsize...' articles out there, but most of them are pretty weak on 'Should you upsize or not!'.

However, you still need to explain to us your needs of wanting to go down this road, as the costs of upsizing may or may not be worth the benefits you're looking for.
PatHartmanCommented:
Do you currently have an Access application that links to your payroll system?  What does your payroll system use as a database?  Can you link to it?  You shouldn't have to store the data a second time in a different database.

Although I don't doubt your ability to learn how to do this, it will be a bigger job than you might imagine.  Perhaps you can hire a professional.  It won't be cheap but you will own the product and not have to pay a subscription fee each year.  If you elect to do it yourself, you'll need two separate courses.  One for Access development and one for SQL Server.  Make sure the Access class covers VBA.

Working with linked SQL Server tables in Access is very much like working with linked or local Jet/ACE tables.  But, you will have to understand how Access interacts with SQL Server so you can optimize the application.

And finally, since this is data that comes from another application and all you are doing is aggregating, you probably won't need referential integrity.  If you don't need RI, you can split the data into as many back ends as you link.  So the actual Access limit turns out to be 2g per table which will probably not be a problem for you.
CRGmanAuthor Commented:
Yes,  current Access database is ODBC linked to Payroll system.   The payroll system uses SAGE software's database engine called "Providex".   I have to store the data in the database for several reason,  One, our payroll system purges the history tables after W2's are printed.   For Obama care I need a rolling 12 month minimum look back on historical data.

I agree with you, learning what I need to learn will be a large undertaking.   But I consider it an investment into the future.   The more I learn about SQL the better.   More and more applications seem to be utilizing SQL as there database engine.

I am fairly familiar with VBA my weakness is in the SQL realm.   RI is not an issue.

Royce Pruitt
PatHartmanCommented:
Sounds like this application is only used by a small number of people.  With RI out of the way, you can solve the size issue easily enough by segregating some of the larger tables into individual back end databases.  Nothing in the app will change.  

When dealing with large record counts, you are likely to be unhappy about performance if you upsize.  Search for "Optimizing Access for SQL server".  You should get a few articles that will help you understand what things cause problems.

And finally, don't forget to compact regularly.  Access doesn't clean up after itself so if you are constantly appending/deleting rows from tables, the database will bloat quickly.
CRGmanAuthor Commented:
Thanks!   Yes there are about 5 users that are in this database.  Yes, we routinely compact.

Thanks.
Jim HornMicrosoft SQL Server Data DudeCommented:
So ... how's it going?  Can we close this question?
Jim HornMicrosoft SQL Server Data DudeCommented:
btw I ran with my comments here and kicked out an article SQL Server Training
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.