Access and Linked tables to Oracle

[Tim Frawley]

In the past I created an Access database front end for our end users
for the purposes of querying our Oracle data. I noticed that in this
older Access database that one of our large Oracle tables was linked
without an index (primary key in this case).

The interesting part of this issue is that this older Access database
returns results from a query against this large Oracle table very, very
quickly. Something around 2 seconds if Oracle has cached the query
results and 8 seconds if it has not.

My primary issue now is that when I refresh the linked tables an index
is getting defined for the primary key in the large Oracle table. What
this has done is cause this query to take up to 2 1/2 minutes to return
the same results!

I am not able to delete the primary key (index) from the linked table
so I am not able to test to see if removing it would make the refreshed
Access database respond in a more timely fashion.

Does anyone know a way around the index properties screen in Access in
order to make this change?

Is there way way to prevent the defining of indexes when
programatically creating these linked tables?

Any suggestions would be greatly appreciated!

Sincerely,

Tim Frawley

 

[Michael Cheng [MSFT]]

Hi Tim,

From your descriptions, I understood that you would like to why your new
database

Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

For now, I would like to collect the following information narrowing down
this issue
1) What's the differenct between your old database and new database? (for
example, Access 97 and Access 2000?)

2) Make sure you are using the latest MDAC
If you are using XP SP2, you could ignore this point, otherwise, MDAC 2.8
is available in the link below
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en

3) Make sure your are using the latest Jet 4.0 SP8

Information about Jet 4.0 Service Pack 8
http://support.microsoft.com/kb/829558

4) What Dirver you are using for ODBC? Oracle's driver?

5) Please have a look at the articles below and let me know whether it will
help you more

Information about query performance in an Access database
http://support.microsoft.com/kb/209126

Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm



Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

 

[Tim Frawley]

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

 

[Michael Cheng [MSFT]]

Hi Tim,

Based on my knowledge, When you are linking the table, you will be prompt
to add a unique identifier if no primary key exists in the source Table.
The unique identifer is only available in the Access table schema, which
means it should not affect the backend. If you meet this senario, I am
afraid this is by design feature of Access and we are not able to setup a
linked table without creating that unique identifer.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Lynn Trapp]

Tim,
You might want to consider using a Pass-through query for this, as long as
you do not need to update your Oracle tables through Access.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Tim Frawley]

Lynn,

Thanks for the suggestion. The main problem with a pass-through query
is the loss of the designer. This is not necessarily a static report
other wise I would create an ASP report for it on our intranet site.
The users have a difficult enough time just figuring out how to work
with the existing queries without throwing SQL at them too.

No biggie, I will keep looking.

Thanks,

Tim

 

[Tim Frawley]

No problem Michael,

I figured this would be the response but I had to give it a shot.
Thanks for your help!

Sincerely,

Tim Frawley

 

[Michael Cheng [MSFT]]

Hi Tim,

Thanks for your kindly understanding! If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.