Re: ODBC JBDC Linked tables

Access on a local Jet database is blazing fast.
Whenever ODBC and network connections are involved, you give up some speed
in return for other features of DB servers like Oracle and SQL Server. This
assumes the only thing you do is create the back end tables in Oracle, copy
the data over to them and re-link from Access to Oracle. Your app should
work in that situation but it is not optimized to take advantage of the
powerful DB server. Plus, whereas with Jet you can bind to a full table of
data, this is a huge mistake for Oracle. Your forms should always bind to a
single record. You should have search forms or criteria available so the
users can select the record they want to edit. There are also some tricks
for setting the source of a cbo only after the user types in 2 or 3 letters.

How to Handle Combos with Tens of Thousands of Records:

Allen Browne has posted a very useful technique to handle this problem:
http://members.iinet.net.au/~allenbrowne/ser-32.html

Mary Chipman wrote a book on how to optimize Access with SQL Server.
All the same "rules" apply with Oracle.
--
Joe Fallon
Access MVP



"BobT" <BobT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E20738C-2FBB-452C-AE27-3930728D8F9E@xxxxxxxxxxxxxxxx
>I have an Access 2003 test database that I have written. The tables are
> linked to an external Oracle datapase personal edtion running on the same
> laptop. The linked tables contained about in total 1/2 million records.
>
> When the tables reside in Access the application performed very well. The
> response with linked tables is kinda slow even though the database is on
> the
> same workstation. I am using odbc drivers to link to the tables. I have
> eliminated combo boxes, placed indexes where needed.
>
> I still need to speed record retrieval up a bit to make if truely
> functional.
>
> Any suggestions? If I can't get the performance to an accceptable level I
> will have to investigate another language or tool to say the least...
>
>
> --
> BT


.

Relevant Pages

  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Why not Access...?
    ... Next, I DID NOT SAY Oracle was considered a "best choice," only a sufficient ... It is a desktop application and the database in also on the same machine. ... I also want to know why Oracle is considered a best choice as compared to SQL Server. ... > Also, you need to answer the question regarding whether or not each client> will be using an individual database, and individual copy of a central> organization database, or making straight calls to a centralized ...
    (microsoft.public.sqlserver.server)
  • Re: Merge/Replication or Syncing with Oracle
    ... You can't directly access an Access database on the desktop from a WM 5 app, but you can sync between SQL Compact on the WM device and Access using the Access Syncronizer: ... As for the Oracle issue ... ... Where SQL Compact Edition easily does merge/replication to SQL Server using IIS ... ...
    (microsoft.public.sqlserver.ce)
  • Re: Need ODBCDirect-ion
    ... Just as a side note, ODBC, or so called "jet" direct is simply a way to ... You need experience with sql server. ... "Upsizing to Microsoft SQL Server" White Paper Available in Download ... Choosing Database Tools White Paper Available in Download Cente ...
    (microsoft.public.access.modulesdaovba)
Quantcast