Re: ACCESS very slow at loading Oracle tables

I've been tinkering with connecting Access 97 and Oracle 7.1, and have found that linking an Access table to an Oracle table is very slow. I'm using ODBC and SQLNet 2 from Personal Oracle 7.3. The client PC is running Windows 95, and the server is an Alpha running OpenVMS 6.xx. I'm connecting over a WAN - I believe it's a T1 line. Here are some timings:

1. File/Get External Data/Link Table

Select ODBC driver for Oracle, and enter username and password. Previously I had set up a machine data source that included the SQLNet connection string.

This brings up the entire table catalog from Oracle that I can access (I have DBS privs), which is about 10,000 tables! The time for this is six minutes.

2. I select a table to link to and hit Create Link. This takes 10 minutes! I'm left with a globe icon next to the Oracle table name.

3. When I double click on the linked table it comes up in 15 seconds.

4. If I exit Access, and then restart, and double click on the linked table, it again comes up in about 15 seconds.

Has anyone gotten steps 1 or 2 to go faster?

Thanks,
Peter Mroz

Troy Perchotte wrote:
>
> When I try to view the contents of an attached Oracle8 table from within
> Access 97, the computer takes about 10+ seconds just to display the
> first page of records, and 2 seconds thereafter to move the cursor
> between records. I have about 100 fields in this table, and if I reduce
> the number of fields, the display time also decreases a little; however,
> nothing changes with respect to the number of records.
>
> I thought after posting this problem here last month, that I found a
> solution. :( When linking an oracle table, Access prompts the user to
> select which field or fields identify each record's uniqueness. By not
> selecting any field, the display time was instant, and my problem was
> solved; however, I found out after, that the linked tables weren't
> updateable this way.
>
> How do I link a table from Access97, with reasonable speed, and not be
> read-only?
>
> Thanks in advance,
>
> Troy Perchotte
Received on Fri May 22 1998 - 21:18:09 CDT