Re: Access and Oracle

mhakimjavadi_at_hotmail.com (Mohammad) wrote in message news:<cc59b627.0310060501.61f69086_at_posting.google.com>...
> Hi
>
> I have to read information from Access DB and insert
> record to Oracle9i table. I used VB and ODBC for Access
> and Oracle and I did that. It is working find but it is
> working very very slow!!!!!
>
> Could you please tell me any other way to do that job and
> faster or maybe I missed same setting for ODBC.
>
> Thanks for your help.

You can take a look at OpenLink Software's high-performance ODBC Drivers for Oracle which are available on a free 30 evaluation basis from http://www.openlinksw.com .

One of the things to bear in mind about the ACCESS JET Engine (the part that talks ODBC to the underlying ODBC Driver) is that it is correctly emulating a Scrollable Cursor Engine (KeySet variant). If JET doesn't provide this facility then you will encounter data consistency problems when any of the following occur via ACCESS: 1. Data Updates (Keyset Cursors ensure that at update time ACCESS is sensitive

   to changes in your backend ODBC data source)

2. Data Deletions (ditto but applied to DELETES)

If you don't use a KeySet cursors or higher (dynamix or mixed mode) then you cannot update and expect sensistivity to backend database changes.

The one issue with JET is that it doesn't give you all the Cursor Models so you are also stuck with some of the downsides of the KeySet Cursor model:

1. Insensitivity to INSERTS and DELETIONS (handled by the Dynamic Cursor Model)
2. Insbility to configure KeySet size (a Mixed Model Cursor is a combination of the KeySet and Dynamic Cursor models based on a configurable KeySet Size)

The trouble SQL Data Access is that this subject isn't broadly understood, and so instinctively we end up assuming good things are bad things (ODBC and ACCESS are two classic examples).

Regards,
Kingsley Idehen http://www.openlinksw.com/blog/~kidehen/ OpenLink Software Received on Tue Oct 07 2003 - 14:51:37 CDT