Re: Access and Linked tables to Oracle

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" <tim_frawley@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:1112650595.339689.71090@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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: Very slow query
    ... indexed for all the secondary tables, only the primary key is indexed). ... create a new blank database and immediately turn OFF the Name ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in forms, base the form on the primary data table, and use combobox or ...
    (microsoft.public.access.queries)
  • Re: SQL Injection Legalities
    ... I am currently writing the SANS Step-by-step guide to securing Oracle ... Oracle database, application server et al to secure it. ... >This was being discussed as a legal issue in pen-test, but I was curious if>anyone else has thought of using SQL Injection to cause a DoS. ... The second OR is>only to maintain a valid SQL statement (assuming the final character in the>query will be a single quote). ...
    (Vuln-Dev)
  • Re: Limiting Large Result Sets
    ... Oracle 9i - Database ... Using query based approach, i was able to retreive all the million rows ... Fetch Size ... rows at a time from the database cursor. ...
    (comp.lang.java.programmer)
  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)
  • RE: Update Query with Oracle Table
    ... change the oracle tables so there really isn't anything I can do with regards ... primary key either but the query works correctly. ... "Jerry Whittle" wrote: ...
    (microsoft.public.access.queries)