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