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.
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.
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.
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.