This may not have a good answer but maybe?

So I have a number of Access databases that query a SQL Server through ODBC to generate reports. The SQL Server backend is being changed out and I need to translate Access Queries to SQL Server queries of the new backend. I have mappings of all the current SQL Server backend to new backend. Some of the fields in the SQL Server are encrypted and only accessible through Access and as far as I know the person that has the password to decrypt the SQL Server fields is MIA/presumed KIA.

The document I have that lists out all the reports that need to be recreated has the Access field names not the SQL Server field names. Is there a good way to programatically get the SQL Code from Access and save it off as a text file or something I can parse with Python? My thought is if I can do this I could parse it to get the original field names not their aliases. Then I can feed that into my program for outputting the new SQL Server table and fieldnames.

  • For saving query SQL text see stackoverflow.com/questions/1275502/… – C Perkins Sep 29 '17 at 3:29
  • You did not explicitly state that you are getting rid of Access, but that's implied since you're needing to "translate Access Queries to SQL Server queries". In other words, you're not only changing the back end, but also the front end, correct? The link I shared only answered the primary question about export queries, but regarding the encrypted fields, why would Access be required to decrypt them? Are there special crypto resources within Access that I'm unaware? Are you certain that you cannot find where the key / password is stored within Access and migrate it also? – C Perkins Sep 29 '17 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.