We help IT Professionals succeed at work.

Setting up Access to use SQL backend tables

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I have been developing in Access for several years but never tried to set up a databse that has SQL backend tables. What is the quickest way to do this given that I have no SQL experience.
Do I use the functionality in Access to convert the database to SQL? Which version of SQL is easiest to set up for this? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
The Upsizing wizard is probably not the best choice, at least in my experience. You'll have better results with the SQL Server Migration Assistant:

http://www.microsoft.com/download/en/details.aspx?id=959

After you do that, check out the article titled "The Best of Both Worlds: Access-SQL Server Optimization" by Armen Stein:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

Commented:
You can use MS Office Access Project ,

you can use MS Access as front end and MS SQL server as back end, but all the work you can do in MS Access.
answer.jpg
If you're already comfortable creating strong table definitions, I actually find the upsizing wizard works pretty well, but I'd recommend learning a bit about the recommended variations on SQL data types (notably the numerics and the char/varchar/nvarchar variations) because I find that for data viability, I occasionally want to tweak a field type the auto procedures come up with.

The more current the version of SQL, the more you'll feel at home in its interface.

Over time, you'll want to do a little work learning to change Access side queries to server side views to get the best resource use out of the DBs, and then gradually may find some code should move to stored procedures, so I'd say learn in this order:

Basic field structure differences
Indexing server side
Views (read up on the "nolock" element before getting in too heavily)
Stored procedures

By the time you've gotten practice with all those things, you'll have discovered a lot more about what you want to learn beyond that, if anything.

-Rachel
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thank you all with the diverse and informative feedback