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.
LVL 1
Anthony6890Asked:
Who is Participating?
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.

Jim HornMicrosoft 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.
0
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?
0
Jim HornMicrosoft 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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Anthony6890Author Commented:
Makes sense.  I'll begin exploring this option.

Do you recommend any specific version of SQL?
0
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.
0
Anthony6890Author Commented:
Sounds good.  Thanks.

I'll see if any of the Access people have things to add as well.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )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:

http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html

Most people use the SQL Server Migration Assistant when upsizing:

http://www.microsoft.com/sqlserver/pt/br/product-info/migration.aspx?mstLocPickShow=True

That link takes you to a page where you can choose your environment specifics, and will lead you to the SSMA.
0

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
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:

http://www.JStreetTech.com/downloads

Cheers,
Armen Stein
0
Anthony6890Author Commented:
Great solutions.
0
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 SQL Server

From novice to tech pro — start learning today.