1

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.

  • I think you should describe what oracle/sql server do and what access does in this project. – kevinsky May 9 '14 at 12:39
  • @kevinsky done. have a look at the edit. – Andy May 9 '14 at 13:10
0

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:

enter image description here

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!

| improve this answer | |
  • Similar to my thinking so far and I would doing this on a test-copy database as proof of concept first , let me just ask you a few questions with this. I would definitely possible to rewrite all the queries but considering some of the Access databases have 100s of queries, IS it possible to simply modify the Msysqueries table? I was thinking a macro that went and changed each column name to its sql server equivalent? – Andy May 9 '14 at 14:18
  • You could probably do that if you open the Access database using .Net code and manipulate the system table from there. Access itself won't let you modify the table directly. – Max Vernon May 9 '14 at 14:48
  • There are 100's of queries PER Access database and about 100 or so databases so editing Msysqueries would be the fastest approach. Do you think it would be DEFINITELY possible to write a small .NET program that takes in an access database as input and modifies the Msysqueries table to modify query column names to the correct convention e.g. if the program spots ‘POLICY_NUMBER’ it will change to 'PolicyNumber'? – Andy May 12 '14 at 7:25
  • It has been quite a long time since I last attempted to do something like that so I do not know if it is possible in the more recent versions. You'd need to try it. – Max Vernon May 12 '14 at 13:53
  • 1
    I will investigate for myself. That was very concise help and its put me on the right path I feel. Thank you. – Andy May 12 '14 at 14:49

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.