Let me start off by saying I'm relatively new to databases etc. I have been asked to undergo a project is which I will attempt to migrate about 100 or so existing MS access 2003 databases from using linked Oracle 11g linked tables to using MS SQL server linked tables.
If you can imagine these 100 MS Access tables are vital and they currently do their job perfectly however the project is intended to just move away from Oracle in general. the SQL server database and tables all exist currently. The sql server and oracle versions both have the same content the only difference if the column naming convention i.e. ‘POLICY_NUMBER’ in Oracle and ‘PolicyNumber’ in sqlserver. What I want is to this is in the easiest way possible to make the Access queries/reports/forms work by simply changing the link from oracle to SQL server.
My points of investigation currently have been looking one Access database and trying to do a proof of concept on that single table. I have been looking at the MSysQueries table, in thinking I could alter the queries in here to point it to different databases and column names. This way I would not have to change the queries individually. I have not been too successful so far and feel way out of my depth.
The bottom line of this question and the reason for posting is simply asking has anyone ever done anything similar? What would you're approach be? Am I looking in the complete wrong direction?
EDIT:
The sqlserver and oracle both do the same task, they take a copy of production data and have it available to test on test databases. The reason they both do the same thing is the reason for this project, to move away from oracle in general and licensing fees etc. The access databases are used to view and execute certain queries. They work and people are happy with them but since a move from Oracle they need to be altered. It is kind of irrelevant what the function of the Access databases is because they cannot be broken or rewritten plain and simple.
Best Answer
Create new linked tables to the SQL Server, drop the Oracle-linked-tables, create new queries with the names of the original Oracle-linked-tables, and presto.
Having said that, if you do this in the Access user interface, Access will attempt to modify the existing queries automatically. You can change this behavior by deselecting the following options:
Caveat: You'd clearly want to do this on a test-copy of the database, then test all the functionality before declaring this a win!