Switching from Access To Oracle

[Djblois]

My company already uses Oracle for its main data and now I want to switch the tables of my program over to Oracle and keep Access as the DBMA. I know that I would get a lot of benefit from this like:

1) Access (Jet Engine) has a 2 GB limit, Oracle does not
2) Access (Jet Engine) I have read is not very reliable on a Server. What does this mean?

What are some other benefits that I would get from switching over to Oracle? So I can sell the idea to my boss.

If he doesn't like the idea than I will switch it over to SQL server express? Which would you guys recommend? I wanted to use Oracle because we already use it here.

 

[KenHigg]

a. Server processing is inherently faster, hence your apps can scale much better. Which means you can work with larger tables and more complex data models.

b. I'm guessing your oracle server(s) are on some kind of back up process meaning the data is safer. Your files servers are probably backed up but probably on a nightly basis. This means if you loose your tables you may have to restore from day old copies.

c. Once you get your data in database servers like Oracle or SQL Server you can use web apps to hit them. Meaning you can create something like ASP web pages to access your data.

There are other reasons but those just came to mind first...

 

[Djblois]

Ken,

I already figured out how to get the Oracle tables into Access.

1) I am wondering do I interact with the tables in the same way? Do I have to change the SQL or any of the VBA code? Theoretically of course, I will still test it and do modifications but was wondering if it would be a major task?



2) Do you know if there is a way to just import or convert my access tables into Oracle?

 

[KenHigg]

1. You should be able to do most basic operations with the Oracle tables the same way you work with the data if it were in Access tables.

2. As I am not an oracle person I'm not sure. If you have an Oracle dba they should be able to help.

 

[SimonB1978]

Hi,

I think everything should be working, except for one thing you will have to check. The Oracle tables don't have the 2 GB limit, however I'm not sure what will happen if you try to open a recordset in VB on a larger table.

I don't know if it is even possible, but linking the tables to the Oracle server will not bring you performance as the processing will still be done locally. You will have to actually connect to the server to send your queries in order to see a performance gain.

As for the SQL, it is pretty much the same syntax with some differences. If you need to use VB fonctions like "SELECT Year(date_column) AS ...." you will need to define those functions in Oracle. And you NEED the ; at the end of the query. There are other differences too... Be careful though if you have tables of fields with blanks in name they are not handled the same way. If you follow SQL "standards" you should not have much problems.

I think the migration is very possible although do not expect to just connect to the Oracle server and have everything work the first time.

Simon B.

 

[Djblois]

How do you do this:

I don't know if it is even possible, but linking the tables to the Oracle server will not bring you performance as the processing will still be done locally. You will have to actually connect to the server to send your queries in order to see a performance gain.

 

[SimonB1978]

Honestly, I'm not too sure.

But I can explain. When you link your tables, it acts as if they were local. If you do a query on a table that is linked, the server will send you the whole table. Then Access will do whatever you asked and show the result. Ex: you will received a 500,000 record table that you are going to filter to, say, 200 records.

If you send the query straight to the server, it will process it (much faster than your workstation) and return the 200 resulting records. Saving time and bandwith.

I have never done that in Access but it sure can be done. The ADODB.Connection can possibly handle that. Although your saved queries won't work...

Simon B.

 

[KenHigg]

I think you're referring to a pass-through query.

Link

 

[Djblois]

Thank you I am looking that up now. It looks interesting.

 

[Banana]

Just to correct a popular misconception: linking tables does not mean passing the entire table for local process. A simple way to prove this is to have two linked tables, one with 500 rows and other with 5000000 rows and open them. You'll find that both tables take just about equally to open. This is because Access usually ask for just enough rows to fill the screen and a bit more and does lazy fetching.

You can also see how it works by looking at the logs from Oracle, SQL Server, and MySQL.

For more info go to support.microsoft.com, search for a whitepaper on ODBC/Jet connectivity.

 

[KenHigg]

I didn't think that was big picture. I thought I read a post from Pat that said Jet would attemp to pass the sql for an attached odbc table to the server for execution and if it failed the it would attempt to execute it local...?

 

[Banana]

Well, that is true but I'd put this way; a badly written query will force a local evaluation because you're giving Jet no way to pass it off to the source. Thus if we take care to write the query correctly, Jet will be more than happy to pass it off to the server.

The whitepaper I mentioned gives several examples and exposition on this.

 

[KenHigg]

I wonder if there is a mechanism in SQL Server to test this theory...

 

[Banana]

VanThien and datAdrenaline already demonstrated this over at UtterAccess. Ill try and dig it for you.

 

[Banana]

Here's VanThien article.

He does good job of describing Jets behavior but I'd still encourage anybody to read the ODBC/Jet whitepaper for specific info on writing good queries.

 

[KenHigg]

Good read - Thanks...