Re: Access and Linked tables to Oracle

Michael,

The initial Access database was created in Access 2000. The tables are
linked to our Oracle production database. We have since upgraded to
Access 2003; however, this issue occurs in Access 2000, 2002 as well as
2003.

We are running XP SP2. My machine has Jet 4.0 SP8. I have tested the
database on multiple machines with similar results in all cases.

We are using the Oracle ODBC drivers for the database connectivity. I
have tested Microsoft ODBC for Oracle and the only noticeable
difference was that all of the queries ran a little slower. The Oracle
ODBC drivers I have used are 9.2.0.3, 9.2.0.6 and now 10.1.0.3.1.

I wrote a .NET application to change the linked table connection string
(which was when I found this issue) using ADOX. I tried having this
program create the connection string exactly as it appears in
MySysObjects in the original Access database to no avail. I have
attempted multiple ODBC connection strings but they all exhibit similar
results. This one particular query using one particular linked table
is really slow if the primary key (index) gets created on that table
when it is linked. This Oracle table contians only one primary key and
close to half a million records. Unfortunately there are no other
columns that could be indexed to increase the performance. Also,
Access created the index in Ascending order and I cannot even change
the order as the records we are looking at are going to be last on the
list in this order.

I have tried multiple ways of re-creating this query in new Access
databases by relinking the tables and creating the query manually all
to no avail.

Just last week I realized what could be the cause simply by having
eliminating all other possibilities.

The original Access database, when the linked tables where first
created, did not create a primary key (index) for each of the linked
tables. Access now does create the primary key (indexes) when I
recreate/refresh or update the linked tables. The Linked Table Manager
will also create the index if I simply refresh the linked tables.

The primary issue is that Access does not provide me with a way of not
creating this index when linking the tables or removing the index once
the table is linked. The index is causing one particular query to run
for 2 1/2 minutes whereas, without the index, the query only takes 8
seconds. I need to know if there is a way to setup the linked table
without creating the index or a way to remove the index once the table
is linked.

I will look over the articles you referred to but I believe the problem
is as I have described above.

Thank you for your help!! I would be happy to contact Microsoft and
create a support incident but they charge for the service and we are
not in a position to throw money at this problem. I simply hoped
someone would have a suggestion that may help. Maybe the articles you
referrenced will be some help, I will give it a try. Until then,
again, thank you for the response!

Sincerely,

Timothy Frawley

.

Relevant Pages

  • Re: What so special about PostgreSQL and other RDBMS?
    ... That's exactly the link the licence agreement for the database points to when it ... comes to what wecan expect for paying support. ... > "Oracle may provide additional releases or versions of its programs ... If the requirements are volatile I'd do a long term contract detailing what ...
    (comp.lang.php)
  • Re: Multiple databases - best performance scenario
    ... My company has the need to manage data for multiple customers. ... Multiple small physical servers connected to SAN ... When I'm using the word database ... You might consider checking licensing costs at the oracle store ...
    (comp.databases.oracle.server)
  • A cool DBA job wanted
    ... 7, Oracle 6, Sybase, SQL Server ... Proposed proactive database monitoring through ... strategies to administer remote Oracle databases ... Trained installation and support personnel in basic ...
    (comp.databases.oracle.server)
  • Re: I want to add to myknowledge
    ... 7, Oracle 6, Sybase, SQL Server ... Oracle Database Administrator ... Trained installation and support personnel in basic ... Senior Oracle Database Administrator ...
    (comp.databases.oracle.server)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Before Oracle decided to allow you to write stored procedures in Java, ... Even without Java existing, let alone being hosted in the database, you ... of languages that they developed for the middle tier, ... the .NET developers out there will be faced with this choice. ...
    (comp.lang.java.programmer)
Loading