Unanswered: Translating MS Access queries to Oracle
I'm currently working on some custom updates to one of our older products (depressingly written in VB6 with an MS Access Database). At the moment I'm trying to get the application to work with an Oracle back end (as per the project specifications), but at the same time allowing it to run with MS Access. At the moment I have a few things to try and I'd really appreciate peoples thoughts:
Extract all SQL from inside the VB6 code (it's a bit of a mess at the moment) into a library of SQL statements, and use either the Oracle or Access library depending on which version is being used.
Keep the existing connection to Access, but have linked tables inside the Access db to the Oracle db.
Modify the VB6 code so that all SQL statements go though a 'translator' modifying Access statements to Oracle statements if the Oracle version is being run.
These are my thoughts so far on the above:
Now, the first bullet point is an incredible amount of work when you look at how the previous developers have coded it - building up SQL statements all over the place!
The second bullet point seems to be the easiest option, but I'm unsure if Access SQL statements will work on a db linked to Oracle (I know basic things such as using * as the wildcard instead of %, and join statements written using the Access query builder do work, but that's by no means a comprehensive test!) Does anyone know if this should work?
I have no idea if the third bullet point will work - does anyone know of any code or a library that would translate the SQL for me?
At the very least I'm going to have to go through the Access DB and the VB6 code and rename all the fields before I migrate it to Oracle- there are spaces and all sorts of characters in there which is nasty anyway, but the main problem is that some of the field names are ridiculously long and the max column name length in Oracle is only 30, so if anyone has any bright ideas on this they would be much appreciated.
Try linking in all the Oracle tables into MSAccess, then create a query for each table, aliasing all the table columns so that they match the old MSAccess names - with the spaces, >30 char lengths, and such. Also, name the queries the same as the original MSAccess table names (basically creating VIEWS which preserve the previous table structures).
Then, I'm hoping, you won't have to change any of your code.
Also, for new development, if you do start querying directly against the linked Oracle tables, since you'll likely be using ODBC you won't have to adhere to the Oracle syntax. I don't have a link to any "ODBC SQL syntax rulebook", but someone else might.
Whenever you use ODBC to connect to your underlying tables, there's the chance that you'll get performance problems. It's because the query you write isn't necessarily translated into the SQL that you think you're writing.
But functionally, you should be getting the same results. MSAccess has no problems using a query as a record source, as if it were a table.
Yeah I am concerned about performance, it's something I'll have to keep an eye on. Previously however, we had multiple users connecting to a single Access db, which could end up with big performance issues. With this project we will have each user connecting to Oracle from an Access DB on their machine, s hopefully there wont be too much difference.
I've been playing about with Oracle Migration Workbench- it's really handy. It generates an XML schema for your Access DB, and then loads this into an Oracle Schema. It will also create queries in your Access DB which have the original table and field names, meaning that fewer code changes are required.