Migrating ODBC Connections from Access to SQL Database

Hi All,

I don't have much experience using SQL directly; however, I've created an Access Database that I'm considering migrating up to SQL.  In my database, I have ODBC connections to our IBM system that are required.  What I'm trying to figure out is can I continue to have those connections if I were to migrate it.  Also, does anyone know of any resources where I can get a better understanding on the migration process overall?

Thank you.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Access can support linked tables from multiple sources in the same file, so the method suggested by Jim are what we most often see. It's not unusual at all to see an Access file with linked tables from 3 or more databases.

Migrating to SQL Server can involve some modifications, depending on your code methods and style, and what you decide to move up. In general you should move whatever you can to the server. Most tables will upsize easily, but if you've used any Access-specific features (like Attachment fields in 2007-2010 databases) you'll find those won't convert.

If you've used direct form references in your queries, those won't convert either. For example, if you have queries like this:

SELECT * FROM SomeTable WHERE SomeField=Forms!MyForm!MyTextbox

You'd have to convert that to use different syntax, or remove the WHERE portion and instead call the query/view and supply the WHERE clause from your FE.

Boolean fields can also give you some troubles, since they're not directly supported on SQL. The migration generally changes those to Bit fields, and if you've not set a Default value on the field you'll have some issues.

Here's some very good information that might help you in your migration:


Most people use the SQL Server Migration Assistant when upsizing:


That link takes you to a page where you can choose your environment specifics, and will lead you to the SSMA.
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Are you going to continue to use the Access as a front-end application?
Reason I ask is because SQL Server does have linked servers, but DBA's in many companies discourage using this.

So, if you're still going to use Access, there's nothing stopping you from importing all of the local tables into SQL Server, then relinking them into your Access app, and leaving alone the linked tables to the non-SQL Server sources.
Anthony6890Author Commented:
Yeah I would like to continue to use Access for the front-end.  I have dashboards that are really user friendly that I want to leave alone.  

Do you think I would have any performance issues leaving the local tables in the SQL Server?
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<not an complete list, as my Access skills are somewhat rusty>

Benefits of SQL Server tables linked into Access
Support by your company's DBA team, as opposed to 'it's your file, so you're on the hook', which will include backups and other maintenance
Better optimization tools, use of indexing
Keeps your Access app size small (other Access experts can expand on this)
Use of views and stored procedures for more complex querying
Anthony6890Author Commented:
Makes sense.  I'll begin exploring this option.

Do you recommend any specific version of SQL?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Do you recommend any specific version of SQL?
The most current version your employer can afford.
Try to weasel in some developer training into the conversation too.
Anthony6890Author Commented:
Sounds good.  Thanks.

I'll see if any of the Access people have things to add as well.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
(no points please)

I agree with the others that your Access front-end would be fine using tables from both SQL Server and your IBM system.  There's a reason it's called "Access".

To make your Access FE work correctly and perform well with SQL Server, you will need to make some changes to it.  I've written a PowerPoint presentation on this.  It's called "Best of Both Worlds" at our free J Street Downloads Page:


Armen Stein
Anthony6890Author Commented:
Great solutions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.