How do I change big VBA/Access project with more then 1 millon lines of code to C## solution
BackEnd: data is stored in SQL Server / Microsoft and applicated via an ODBC link, but auxiliary tables with intermediate results are often stored in MDB databases.
FrontEnd: thousands of forms and reports, and of course with a large amount of VBA code in an Access databases.
Changing of FrontEnd to a modern C ## platform would require at least partially automated tasks, otherwise the time for a complete redesign is for me unavailable.
FrontEnd: thousands of forms and reports, and of course with a large amount of VBA code in an Access databases.
Changing of FrontEnd to a modern C ## platform would require at least partially automated tasks, otherwise the time for a complete redesign is for me unavailable.
AndyAinscow
Why do that unless there is a very good reason. If there is a very good reason then you are probably talking of a major redesign anyway.
ASKER
This project has been running for 25 years (from Access 2.5 to 2016). New capacities of VBA programmers are zero on the market. If it should work for another 10-20 years, the project will only live to life and has no chance of further development. And of course the design is outdated. Competitiveness is constantly declining.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have some initial knowledge of C # and I understand that we will have to completely rework the code. But what about design forms and reports (withou VBA code)? Is there a tool for conversion?
Well for many years this is a hot question and a number of answers float around but judging from some early tests they are just dead ends.
If it's actually 1 million lines of code then you need to create that kind of migration utility...and for that you need absolute knowledge of your application and ninja skills in both VBA and C#.By the way there are lot of great VBA programmers but no good jobs available...
Unfortunately not. Data's always easy, but because Access tightly binds objects with associated business logic, there is nothing that really does the job.
Jim.
There was a mention of "thousands of forms and reports". This fact alone speaks in favor of a redesign.
The question is: Do you have any statistics on what is actually used and what is obsolete or not needed?
The question is: Do you have any statistics on what is actually used and what is obsolete or not needed?
ASKER
This is a good idea to build automatic statistics into the system and find out which parts of the system are unused and which are used to the maximum. And then, based on the results, they propose redesign priorities. Thank you all for your answers, although I have been confirmed by the unfortunate suspicion that I will not find or buy a tool to speed up the conversion of an Access / VBA project to C#
If you introduced logging into the routines, you could create your stats from the log data.
The frequency of usage is of little use.
In finance, for example, a form or report may be used once a year only and still be mandatory.
In finance, for example, a form or report may be used once a year only and still be mandatory.
@Gustav
I think frequency of use is to be used to order the conversion/upgrade effort. The most frequently used forms/routines should be at the top of the list of code changes.
I think frequency of use is to be used to order the conversion/upgrade effort. The most frequently used forms/routines should be at the top of the list of code changes.
In general reports have little if no user interaction for choices and code behind.
One thing you could consider to ease the task is to convert forms and leave the reports within access and then automate access from your new C# app(s) to display/print/export the reports. Then convert the reports to some other reporting engine format at a later date.
Ahhh, statistics. So useful and yet so easy to abuse.
Also look at common functionality of forms. eg. you have three forms A, B and C. A is used about 30% of the time, B about 30% and C for the remaining 40%. Clear you recode C first don't you. False. Looking more closely: forms A and B are structurally exceedingly similar, use the same functionality and similar code - so with just a tiny bit more effort than needed for C you have forms A and B recoded - with the 60% of user time covered rather than the 40% for just form C.
One thing you could consider to ease the task is to convert forms and leave the reports within access and then automate access from your new C# app(s) to display/print/export the reports. Then convert the reports to some other reporting engine format at a later date.
Ahhh, statistics. So useful and yet so easy to abuse.
Also look at common functionality of forms. eg. you have three forms A, B and C. A is used about 30% of the time, B about 30% and C for the remaining 40%. Clear you recode C first don't you. False. Looking more closely: forms A and B are structurally exceedingly similar, use the same functionality and similar code - so with just a tiny bit more effort than needed for C you have forms A and B recoded - with the 60% of user time covered rather than the 40% for just form C.
Omitting forms / reports based on usability is the logical thing to do but "according" to Murphy's Law it will be the least needed form/report that will be the most needed at the worst possible time.
To my this is just a big project that requires planning and plenty of work.
I would start with creating a rough map of the application .. hopefully someone has good knowledge of the application but with 1 million lines I reckon plenty of people are involved..so maybe Visustin : https://www.aivosto.com/visustin.html should be used. A rough estimate between 6 months to maybe 1 year.
Maybe an additional developer is needed to have a thorough look at how the data organized..table design, normalization, optimization and so on.. since the move is performed everything should move to SQL.... probably 6 to 12 months.
Of course some discussion should be made in the concept .. how we want the new FE...is it going to be a pure desktop solution or we will invest some more to make it either web or hybrid : desktop/web...in that case an application server is needed.
Then you need to create some kind of parser that will scan the entire application.. reading the controls and and isolating subs/functions ...now here is the tricky part...it won't be easy..based on a good scenario at least a year..and the catch is...we are doing..the migration.. should it be static or we will apply some optimization (classes/design patterns/functional programming...etc).
I would say it's a 2 - 3 years project but it could go up or down depending on a million reasons.
To my this is just a big project that requires planning and plenty of work.
I would start with creating a rough map of the application .. hopefully someone has good knowledge of the application but with 1 million lines I reckon plenty of people are involved..so maybe Visustin : https://www.aivosto.com/visustin.html should be used. A rough estimate between 6 months to maybe 1 year.
Maybe an additional developer is needed to have a thorough look at how the data organized..table design, normalization, optimization and so on.. since the move is performed everything should move to SQL.... probably 6 to 12 months.
Of course some discussion should be made in the concept .. how we want the new FE...is it going to be a pure desktop solution or we will invest some more to make it either web or hybrid : desktop/web...in that case an application server is needed.
Then you need to create some kind of parser that will scan the entire application.. reading the controls and and isolating subs/functions ...now here is the tricky part...it won't be easy..based on a good scenario at least a year..and the catch is...we are doing..the migration.. should it be static or we will apply some optimization (classes/design patterns/functional programming...etc).
I would say it's a 2 - 3 years project but it could go up or down depending on a million reasons.
I am afraid i have to object, instead of proposing to do the work by someone else i even had a working plan.