question

MalcolmPGalvinJr-1862 avatar image
0 Votes"
MalcolmPGalvinJr-1862 asked MalcolmPGalvinJr-1862 commented

Access to SQL using SQL Server Migration Assistant

The SQL Server Migration Assistant for Access website states:

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access versions 97 through 2010 to SQL Server 2012, Microsoft SQL Server 2014, Microsoft SQL Server 2016, MicrosoftSQL Server 2017 on Windows and Linux, MicrosoftSQL Server 2019 on Windows and Linux, or Microsoft Azure SQL Database.

I began writing an Access application in 2000 and updated it through 2010, using the newest versions of Access at the time. I now run the application on my current Access 2019. All my Access files are .mdb files.

I have downloaded Microsoft SQL Server 2019 Express edition.


Based on the above, should I be able to use SSMA for the migration?

sql-server-generaloffice-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MalcolmPGalvinJr-1862 commented

Sure it should work.
SSMA for Access works with every Edition of SQL Server, also with the free Express Edition.

There are some limitation, e.g. in MS Access SQL queries you can use VBA functions, in SQL Server it's not possible, so if you have used VBA, you have to rewrite the queries manually.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


OlafHelper-2800 stated, "There are some limitation, e.g. in MS Access SQL queries you can use VBA functions, in SQL Server it's not possible, so if you have used VBA, you have to rewrite the queries manually."

My Access application has:
o tables 46
o queries 265
o forms 69
o reports 262
o macros 79
o module 1

I used Access to design all of the above. Access did whatever behind the design coding in VBA (or whatever) was needed (I assume). Based on your comment, does this mean I have to redesign all of the queries? Does the same apply to all the forms, reports, macros and module, because they too will be accessing the SQL database?



0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @MalcolmPGalvinJr-1862,

Sure, you can use SSMA for the migration.

Quote from Microsoft SQL Server Migration Assistant for Access download link

SSMA for Access is designed to support migration from Microsoft Access 97 and higher to all editions of SQL Server 2012 through SQL Server 2019, Azure SQL Database and Azure SQL Database Managed Instance.

Suggest you reading MS document SQL Server Migration Assistant for Access (AccessToSQL) to get more information about the migration.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.