Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Translating MS Access queries to Oracle

554499Jan 4 2007 — edited Jan 10 2007
Hello,
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.

Cheers,
Strak
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2007
Added on Jan 4 2007
8 comments
876 views