Migration

[HaroldH]

Being a retired mainframe programmer ("Pressed" into MS Access service to help my wife’s store) I would like to stay in a stack that adheres to “Keep it simple”.

What would be a recommended technology stack to migrate too? And, is there a recommended migration tool in the market?

A bit of background about our current access system…

Originally the system I built had a split design with 1 accdb for the data, linked to a separate accdb front end.​

​

As the volume grew and performance slowed, I spread the data across 7 accdb (organized by function) all linked to the front end accdb. This configuration required maintaining some relationships between tables in the front end accdb (not ideal) but splitting the data improved performance.​

​

The front end is now 2 apps. The original which does everything, from managing clients, orders, stock, accounting, employees, management reporting, etc, etc, etc... ). The second app is a touch screen, guided workflow app for the sales force. Both are linked to the shared data accdbs.​

​

In all there are +/- 150 tables, 200 queries, 100 reports. There are over 100 forms (many unbound).​

​

The data is very moralized, supporting several many-to-many, and even some many-to-many-to-many relationships.​

​

The system is very bespoke presenting option based on the role the user has been assigned, and in the guided workflow app based on the selections the user makes.​

Migration thoughts.

If I migrate away from Access to a SQL engine for the data I can also migrate the common “Project Specific objects” (100s of functions and procedures as well as the queries currently executing on the clients) to database objects (views, functions, procedures and triggers) which will run on the server and should improving the performance.​

​

The data structure migration is relatively straightforward, even if I have to manually build the tables again. The migration of the functions, procedures and queries too should be easy (cut, paste, tweak).​

​

Ideally the front-end would not be a client-server technology, but most importantly would have a simple migration path from MS Access. With over 150 forms and 100 reports. I would rather not have to rebuild the front-end from scratch.​

I welcome anyone suggestions...

Harold

 

[Pat Hartman]

The data is very moralized,

I really like my data to be moral also

What would be a recommended technology stack to migrate too? And, is there a recommended migration tool in the market?

No. There is no better product than what you are using if you don't need web access. If you are a competent developer in another platform, the cost of development in the new platform will easily be 7 - 10 times the cost of the initial development using Access. If you are learning as you go, the sky's the limit.

If you have used "Access" methods such as form filters, you won't be able to just convert the BE and be happy with the results. In fact, you will be surprised to find that with the SQL Server BE, the Access app may even slow to a crawl.

If you have a day to waste, try a straight convert. Use SSMA to upsize the tables from the various BE's. When you convert data, unless your validation is top notch, you will almost certainly find errors with dates because ACE and SQL Server use a different range for Datetime. 9/5/204 is a valid date for ACE although clearly a typo but it is not valid for SQL Server.

One thing to watch is SSMA does not copy the hidden indexes Access creates on all FK fields so you might need to add them after the upsize. When you run into data issues, I suggest fixing the data at the source and running the upsize again. Do it until the upsize is error free. And fix the production data also as you find the errors. Then just relink all the FE's to SQL Server. Test. That will give you some idea of what you are up against. I can convert any Access app that I built to SQL Server in an afternoon and it only takes that long because of all the testing I have to do. The reason is that I use good client/server techniques when I build the apps and validation for date fields that catch obvious typos. In summary I use forms that are bound to queries that have selection criteria. So rather than local filtering, a new query with new criteria is sent to the server when the user wants to access a different record. I can probably count on one hand the number of unbound forms I've had to build in 25 years of Access development. I started back in the nineties using DB2 and never looked back.

Once you have done the conversion, look for slow spots and see what you need to change. I find that creating views will frequently solve slow joins. I also sometimes use sp for complex reports. I had a very complex batch process for one application and I ended up building a sp for that. If you do bulk deletes, using a passthru query helps. But, the interactive part of the application is bound forms and very little of the processing ended up having to be moved to the server.

There is no reason to ever start with the intention of migrating everything. Access querydefs and bound forms/reports work just fine once you understand how Access interacts with the server.