bypassing jet engine with oracle backend 1

[DBrack]

Hello,

Will the performance of an access application degrade after the backend is changed to Oracle 9i (from access)?
Is bypassing jet engine necessary? If so, are the below steps (given in the documentation) required?

Modify all SQL statements within the VBA code to comply with Oracle SQL syntax.
? Change SQL statements containing Microsoft Access specific expressions to Oracle syntax.
? Replace built-in Microsoft Access functions used within a SQL statement with the equivalent Oracle functions.


Thanks in advance.

 

[Pampers]

What do you mean by AccessBack-End?

Pampers
Just let it go...

 

[DBrack]

Pampers,

I mean, we continue to use forms and reports while pointing to Oracle 9i instead of access.

 

[Pampers]

I meant, was the former back-end MsSql or Jet (the last is not really a back-end).

Pampers
Just let it go...

 

[DBrack]

It is ms access 2003

 

[Pampers]

You can use Access 2003 roughly in two ways.
1. where front end and back end are integrated and stored in one mdb-file (or (mde). The proccesing is done by the Jet-database engine

2. or as an adp-file (access project) where the front is the adp-file and the backend (data, sp) resides at the Microsoft SQL 2000/2005 server.



Pampers
Just let it go...

 

[DBrack]

1. where front end and back end are integrated and stored in one mdb-file (or (mde). The proccesing is done by the Jet-database engine

We are currently using the above approach and are planning to move to Oracle 9i. The forms will continue to exist, however they will use oracle 9i tables instead.

The problem is with the jet engine. When using Oracle 9i, it causes performace degradation. Is that true? If so, do we need to change to ODBCDirect and change the code as I mentioned in my first post?

Thanks again.

 

[Pampers]

If you use Access with the JetEngine as desktop applications, redesigning can/will decrease performance - Since the JetEngine can be very fast in the above setting. But if you want to have a lot of concurrent users, a client/server archicture will become more attractive. But you have to redesign because programming on JetSQL can be done very differently then when programming agains an sql-database. How much work the transferring will take depends how the original was done. And not only the vba can be different, the queries made in JetSQL are different too.

Before you move to an OracleSQL backend you might want to consider first upsizing it to an MsSQL. The advantage of this is that the Upsize wizard already gives you a lot of information on the differences between AccessJet and AccessMsSQL. You can find the upsizing wizard in Access under Tools, Database Utilities, Upsizing Wizard.



Pampers
Just let it go...

 

[DBrack]

Pampers,

Thanks ... But, Oracle is the only choice that we have. SQL server is not available. So, in a nutshell, move from access to Oracle, change all the code -- otherwise jet will be slow. I just can't let it go!

 

[Pampers]

Maybe you can use the free MsSQL server that ships with Office. Path on the CD: /OFFICEENT2K3/MSDE2000. It has full functionality, but is datalimited to 2GB.

Pampers
Just let it go...