Connecting Access (365) to Sql Server Express

Davisro
Davisro used Ask the Experts™
on
Gentlemen, we've been discussing moving our departmental Access database to a managed instance of Sql Server where my department would own the tables. I know some query SQL and am learning the language. I downloaded Sql Server Express to my PC at home.
1. Can you point me to a tutorial as to how to connect to my SQL server instance from Access?
2. I want to learn how to use SSMS and continue my path to learning the query language and how to create and manage sql databases. I've always learned from books but today people seem to like online resources. Ive been considering a subscription to Safari so I can just browse many books to find ones i like? What would you suggest? Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>2. I want to learn how to use SSMS and continue my path to learning the query language and how to create and manage sql databases.
When I made this conversion a bunch of years ago I wrote an article that helps .. Migrating your Access Queries to SQL Server Transact-SQL

Good luck.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Note too that an understanding of TSQL is the tip of the iceberg. Migrating to a SQL backend from Access often requires a shift in your coding mindset. You'll be working more in a "client-server" methodology than anything else, and using standard Access methods often leaves with a system that is less performant than it was with a bound Access system.

There are several tutorials on the web regarding migrating your Access app to SQL:

https://www.jstreettech.com/files/Best-of-Both-Worlds.zip
https://www.fmsinc.com/consulting/sqlserverupsizing.aspx

There are also several Experts here on EE with extensive experience doing this.
DavisroBudget Analyst

Author

Commented:
Hi Jim. If I remember, you've written seventeen articles, no? Anyway, reading through your article...this is very interesting. How about part 2 of my question? Any suggestions? Thanks
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
just found this site: https://www.essentialsql.com/getting-started/

seems to be valuable!
John TsioumprisSoftware & Systems Engineer
Commented:
I think its better to master SQL ...feel comfortable with working with SSMS and then perform the migration
For learning it would be just great to migrate your application's BE to SQL but only for learning and testing...production should wait.
If you are anxious to make the jump ...SSMA or Bullzip's Access to MsSQL should make the migration a breeze...
After that work on SSMS and forget about Access for a while....when you are ready return here to discuss it some more...there are 2 options...
1. Linked Tables
2. Passthrough Queries....my personal choice....
If you want you  can take a look at my early copy of Access Benchmark (last post)
1510261.jpg
DavisroBudget Analyst

Author

Commented:
Thank you gentlemen for the good advice and Happy New Year!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial