Re: Need ODBCDirect-ion

Albert, thank you so much for the thorough and well thought out reply.

A few follow up questions/statements:

a) to be clear, then, you're saying that after upsizing my database such
that all of my tables become linked to SQL, my application should not suffer
significantly by the fact that all table references will now be passed first
through Jet before going to SQL via ODBC if it is designed well? I can
accept that, I just want to make sure you're saying it's reasonable and ok to
keep this two-stop journey going vs. trying to eliminate it and go only to
SQL, bypassing Jet.

b) Alternately (in addition to making my application more efficient) I do
want to be sure I understand all the things I might do to skip the Jet trip
in the event this helps me. So if I attempt this, do I overhaul my
application so that the following means are used to source my data?:

1) use pass-through queries for all form recordsources and list/combo box
rowsources
2) use call-back functions to load my list/combo boxes (alternate to
pass-through queries in 1) above)
3) use appropriate ODBC connections in my DAO code (in place of
currentdb.openrecordset calls)

My application uses a lot of in-line SQL statements, so I need to bone up on
how to turn these into pass-through queries and applying them to my
recordsources (as opposed to saving the query defs outright). Do I just add
an in-line SQL statement to my querydefs, then change my form's recordsource
to point to that? I definitely want to stick with my bound forms, I get your
part about how using Access is pointless if I want to work with unbound
forms; I just change my recordsources based on users' selection of criteria
and want to understand the best way (see g)) for more.

I see the part where you say that after a couple of combo boxes my
performance will hit the dirt if I don't switch them to views or call-back
fills (if I can master this one :)). So that jumps me back to b.1) and b2)
above.

c) Re: current performance: My application can have a bit of a slow start up
(~10, sometimes even 15 seconds (first time in a sesion); is this late
binding?) but runs well once on our office network once started. However, on
a cable connection via a VPN it's really slow to start and a little sluggish
between form flips (i.e. telecommuter access). Via ethernet cable, it's
pretty bad. So I want to make this better to start with, before I upgrade to
SQL at all, or else I'm doomed. :) It's not a hugely used application, there
are anywhere from 5-20 users on at a time, and no one has complained about
performance (except the slow start up and the VPN connection on
cable/ethernet). My main reason to go to SQL is because my departmental
standard insists on it for security/stability/backup, etc.

d) Re: form design--my busiest forms do have a lot of combo boxes (some up
to ~20) and perhaps there's some performance with filling these? I would
also note that I have one lookup table for just about all my lookup values
(with a "type" column); there are currently 33 different types, and there are
about ~1,400 rows now. I don't know if this design has impacted me (vs.
having 33 tables with much fewer records in each)(I made a post on the
database group asking this question). The "type" column is certainly indexed.

e) I have run the application Performance Analyzer and I've created all the
indexes suggested. I do get the "user fewer controls on your form" message
in several instances. I've certainly packed up some of my forms, I have
always preferred to provide as much info on each form without a bunch o'
clicks to get to things, but I do segregate my subforms onto tabs and in most
cases I don't value the SourceObject property until the user selects the tab
(thus only loading those additional records when requested).

f) My app is fairly small by your record count estimates....it's an issue
tracking system, with ~8,500 issues with child records of ~48,000 "update"
detail rows and ~20,000 contacts. There are a few more tables with much
fewer record counts.

g) Re: design in getting records--the app doesn't bind any form to the
~85,000 records outright. Users are presended with a list of queries that
qualify on huge subsets of these records. The only instance where records
are preloaded is when users opt to select one of the queries as a "start up
favorite", in which case the query result is presented to them when they
start the app. So I don't think I've got any impacts from trying to load all
records.

Thanks in advance for getting through all of this (if in fact you even did).
:)

Chris

"Albert D. Kallal" wrote:

Upsizing slows the apps down too much, so I did some research and stumbled
on some dry documentation on ODBCDirect. I managed to set up the
connection
code but now I'm at a loss as to what's next.

Just as a side note, ODBC, or so called "jet" direct is simply a way to
by-pass
JET.

It is essentially the pre-cursor to ado.

however, using a plane Jane odbc connection, and making a query pass-through
will yield the same performance anyway.
Also, notE that ODBC jet direct has been discontined in a2007.

There is zero gains to be made using ODBCdirect over that of pass-through
queries anyway. Further, none of this odbc direct stuff applies to your
current forms anyway.

Do I need to code what Access used to take
care of naturally with regards to Form Recordsource and listbox/combobox
Rowsource properties?

No, for the most part if you going to adopt un-bound forms, then ms-access
is the wrong tool. (the reason being is that vb, and vb.net has a HUGE
support system, and a bunch of wizards built around un-bound forms. If you
decide to go the un-bound forms way, then ms-access is the wrong tool
because its forms are bound, and all of the tools are desgined around this
type of approach. So, you wind up with the worst tool if you go un-bound.

So, either you get great performance out of your bound forms, or you dump
ms-access. it really that simple....

Can anyone suggest a direction or some "how to" sources?

You need experience with sql server. you have to get competent with it. As
my other post states, the performance of using sql server with c++,
assumbler, a web site, or ms-access sis going to be about the same (sql
server does all of a sudden, go..hey..this is a web server).

It all about you going back to your mainframe roots, and moving an absolute
min of data.

For example, it been about 10 years since I opened a form that is attached
to a large table without a where clause. So often, I seen a form that
attached to a table with 10,000 records. if 5 people open that form, we
potential transfer 50,000 records of data, and NOT DONE ONE BIT of useful
work.

here is some links:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
--



.

Relevant Pages

  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • 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: Sybase Source. What "data flow source" to use?
    ... "Allan Mitchell" wrote: ... when I right-click over a SQL 2005 database and select Import ... datasources defined in ODBC Datasources on my computer, ...
    (microsoft.public.sqlserver.dts)
  • Re: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)
Loading