SQLTeam.com | Weblogs | Forums

Migrating Access Solution To SQL Server Advice Requested

Other SQL Server Topics
#1

Hi everyone,

I've been tasked to migrate our Access solution's data to SQL Server in which we will continue to use Access for the frontend. I would like to draft a document that describes the approach, order of steps to be taken and accomplished besides the actual transfer of data such as logins, roles, authentication, reprogramming of the application where needed and such general ideas. I used to do a lot of SQL Server development in the nineties in tandem with Visual Basic. So it's been a while. I bought four books on SQL Server to freshen up my knowledge.

Any advice on this document? No has asked me to provide it. I just thought this would be a good idea to help add confidence that this project is being properly attended to and not just slapped together haphazardly.

#2

That is a commendable approach. Having migrated a few access apps to sql, I dont recommend doing it with Access, time to go to a modern server application such as a web app and not a client app if at all possible

  1. Dont write it yourself but see if something can be purchased to replace it

  2. If you opt to do it yourself try a web application written in react or angular or some php framework

But that said irregardless of the technology choaen here are some things to write up

A1. Would it be possible to start migrating one table to sql, reference it in existing app and see if it works? If not can you do a proof of concept new app with only one table for example products with no roles and no security details? Because you night find out access to sql might not work out and u need to abandon before doing too much work. In fact this is key for stakeholders.

A2. Create an erd showing the relationship between the tables
B. How you will create the artifacts within sql, re-runnable scripts that you can use to tear down and recreate it all
C. How you will transfer the data to a staging database, ssis or powershell or python
D. How you will compare visually or otherwise if all your data transferred successfully to staging, you had 150 widgets in aceess but now you have 1500 or 15, something went wrong
E. Then what happens from staging to actual final table
F. Based on A1, start building the app one piece at a time, starting out with the root table of your erd and building the app by moving out to each logical module of your app

Etc

1 Like
#3

yosiasz, thank you sooo much for taking the time to write out your advice. It is greatly appreciated. Unfortunately we have to go with the Access frontend for now. I have studied ASP.Net and would love to finally leverage that knowledge but it's not in the cards now. And we will have to do the frontend ourselves.

I will do the ASP.Net front end for myself for "fun" but it will probably never see the light of day production-wize.

I will definitely utilize the ideas you have presented for the document. Super THANK YOU!!!!

#4
1 Like
#5

I just read the whole above article from Microsoft on SSMA. I clicked on the link to download SSMA and was presented with two choices. SSMAforAccess_8.16.0.msi and SSMAforAccess_8.16.0_x86.msi

I hope it's okay to ask on this thread which version am i supposed to use instead of posting a different thread. My intention is to run this from my 64 bit win10 computer/Access 2016 to migrate to my test local 2019 SQL Server Express for now, eventually the production server.

Which file am I supposed to download?

#6

I would go with SSMAforAccess_8.16.0 since the other one is just _x86. Try it out on a copy of your access.

1 Like
#7

Thanks, I downloaded and installed the non _x86 version. During installation, i received a message claiming that the program couldn't find DAO, and that i can download it then or later. I tried to download it at runtime of installation and then i landed in a "2016 Access runtime download" page instead. So I exited that page and continued with the SSMA install. Now checking into the DAO mystery as my Access installation has DAO 3.6 as an available reference in references.