558
A COBOL programmer explains that this migration is impossible without catastrophe unless they want to destroy social security.
(lemmy.world)
this post was submitted on 29 Mar 2025
558 points (98.9% liked)
Bluesky
977 readers
319 users here now
People skeeting stuff.
Bluesky Social is a microblogging social platform being developed in conjunction with the decentralized AT Protocol. Previously invite-only, the flagship Beta app went public in February 2024. All are welcome!
founded 4 months ago
MODERATORS
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
I work with a ten year old Access database I'm trying to migrate to SQL. Should be easy, right?
Except it's got over 170 macro driven subquery chains accessing multiple legacy ERP systems, is the only source of their KPIs (these legacy systems can't generate them on their own), and is the only system that can publish the KPIs to the multiple Sharepoint lists that the entire company relies upon.
Despite the fact I have over 20 years experience migrating legacy systems, it's taken me close to a year to track down all of the dependencies, and this system is built in an easy language (SQL vs COBOL), is a tiny fraction of the size of the SSI databases, and is 30 years younger than the systems running social security (at least).
There is absolutely no way they have any chance of modernizing the social security system in under five years. It would take at least a decade to do it properly.
DOGE won't just break the system. They will fuck it up beyond all belief, to the point where it will never work properly again.
If you're on Social Security, be ready for payments to just stop. Expect it. It's their end goal.
And pray the last administration took backups.
You up to talk shop? I'm staring down the barrel of a similar task I've been putting off until I have a puzzle mood type of day. But also worried about doing it well because there's definitely more. How do you usually start to approach migrating legacy systems?
Access SQL is just slightly different syntax to what I know, I can usually translate but it's slow because it's all one big block. I started by using PowerShell to dump all the objects and query text into Excel so I could find all the connections. Some broken down at some point so half the data is on the server. This thing serves as kind of like an ETL tool and they have two access DBs for front and back end seems like, using data from server, file system, local access tables. There's a bit of looks like VBA in there too propping up the forms.
What thread do you pull first to unravel the Gordian knot? Access is a bit out of my wheel house. Bravo to the cowboy business users who were able to get the job done, but it's hard for me to parse and of course they are retired so no SME to speak of.
Lend me your wisdom, please.
You're on the right track, but I wouldn't use Excel for the query text as it's going to want to break up your statements.
What I did was use a VBA module to pull out all of the queries into a master .txt file, then converted it a .sql so that Notepad++ could highlight the statements for me to help with readability. This serves as a master query library, allowing text searches of field and query names (Notepad++ has an excellent search tool).
For conversion from Access SQL to regular SQL on the queries, it's usually pretty easy, but tools like this can help.
Second step is to break out your macros and list the query chains as your primary goalposts for creating SSIS jobs (because you'll likely want to be able to automate those queries in your new system). Note that it's very likely that you'll also need to walk down through each query to find additional subquery chains embedded in the top level query coding.
Tables are usually a snap - you can just do a 1 to 1 import by the SQL Workbench import tool to bring it in directly from the .mdb (or .accdb).
For forms, right now I'm looking at PowerApps just because I'm working in a Microsoft shop, but depending on your final architecture, anything that interacts with SQL should do the trick.
The reason they have a frontend and backend database is because once older versions of Access hit 2gb, it starts to crash. If you're getting crashing issues and your backend is nearing or over 2gb, you're likely hitting this limit (likes to throw a 2950 error). It can be resolved by moving your larger tables to a new Access database and then using the Linked Table manager to retarget the new tables and fields. The one I deal with has had to go through this process four times - it's got 5 backends.
Also, if your frontend is in a different location than your backend, putting them in the same directory will drastically improve your load times.
Hope that helps - good luck!
Yes this is tremendously helpful! The syntax highlighting alone is going to make this much easier. I totally overlooked this, but it's clearly a winner. Even just having the vocabulary for a few of these things feels like an incoming boost.
We are big on Microsoft too, so power BI and power apps is the destination. I'm coming around to these tools, if nothing else it gives some visibility so they can't loom in the shadows like these Access leviathans.
Really appreciate the time, and on the weekend no less. Have a great one.