Re: Access and Oracle

mhakimjavadi_at_hotmail.com (Mohammad)

> 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.

Performance is primarily an application thing and not a database thing - unlike what some people like to belief.

MS Access and its driver layers are not suited for client-server. Especially not when dealing with Oracle. It often insists on interfering with you SQL. Or telling you that perfectly legal Oracle SQL statement is, according to it, invalid. Add to that, programming in Access Basic without taking into consideration how Oracle deals with transaction and consistency. After all, Oracle does the transaction. Not MS Access.

So no wonder IMO that you have performance issues.

The 1st thing to do is to check that you are using the ODBC PASSHROUGH option. This option tells the applicable ODBC drivers (with limited success) to *ignore* the SQL statement and pass it just like that to the database back end.

The next thing you may want to do is look at how you are interfacing with Oracle. Are you using data bound controls? Are they effective? Are they using the right WHERE clause for an UPDATE or DELETE?

Then also do not forget to use Oracle correctly - i.e. understanding the basic concepts of an Oracle transaction and using SQL correctly (MS Access SQL is not necessarily suited for Oracle use).

Just because MS Access is used, does not change the best practise and optimal methods of using client-server.

--
Billy