Need ODBCDirect-ion

[Guest]

I'm a mainframer who taught himself VBA/Access. In years of my non-mainframe
time, I managed to write a couple of decent apps with nice features and
functions for my workplace. My apps could become departmentally used but I
need to go the next level and put the databases on SQL.

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.

Am I facing a huge re-write? I've got scads of DAO going on, so I get how I
can redo that code, but how do I get all of my forms to populate with data,
and all of the dropdowns to fill? Do I need to code what Access used to take
care of naturally with regards to Form Recordsource and listbox/combobox
Rowsource properties?

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

Chris

 

[Albert D. Kallal]

SQL server is indeed a high performance system, and also a system that can
scale to many many users.

If your access appicaton is well written, and runs well on an netowrk now,
then moving the back end to sql server should also mean that it will run
well.

If you write your application in c++, or VB or assember, or in your case
with ms-access, in GENERAL the performance of all of these tools will BE THE
SAME.

I repeat, sql server is a heavy duty mainframe like system, and using
vb.net, ms-access, or assumbler will prouce the same perfaonce.

In other words...sql server is rather nice, and is a standard system used in
the IT industry.

However, before you convert..how well does your applciton run now?

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

The other issue is how well is the database setup?

Further..how well are the forms designed?

How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKallal/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Don't use quires that require more then one linked table

(this ONLY applies to odbc to sql server...you CAN and are FREE to do this
with a mdb JET file share..and also with ADP projects to sql server).

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

ODBC and dao is just fine. Converintg to ado will not normally make one hoot
of diffence. So, dao is useally EQUAL in performance if you do things
right).

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc.

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right even with sql server
(you don't have to re-make those forms

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

 

[Albert D. Kallal]

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

 

[Guest]

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]

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?

Yes, the above is about right. There are some exceptions, but that
just means you deal with queries, or forms that are now slow..not
have to change everything.

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.

Actually, if you have a form that based on a table linked to sql server, and
load up that form using the "where" clause..the data transferred down to the
form is quite minimal. The problems arise if you have lots of sub-forms, and
even combo boxes can hurt things.

1) use pass-through queries for all form recordsources and list/combo box
rowsources

Pass-through quires are for the most part read-only. so, for list/combo
boxes
etc, it not a bad idea. In fact, I find that combo boxes tend to load slow
(a stop gap solution that works well for combs was mentioned in this thread)

2) use call-back functions to load my list/combo boxes (alternate to
pass-through queries in 1) above)

Sure, but call-backs is a lot of extra coding work. Use pass quires, or
the trick I mentioned in this thread. I would only go to call backs when
the simple and easy approaches have been exhausted.

3) use appropriate ODBC connections in my DAO code (in place of
currentdb.openrecordset calls)

Again, I would only do this on a case by case biases.

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).

If the sql query does not involve multiple tables and joins, you can well
get
away using the current linked table, and not modify your code.

Do I just add
an in-line SQL statement to my querydefs, then change my form's
recordsource
to point to that?

As mentioned, you can leave the form bound to a linked table. The critical
concept
is how your open the form, and do you keep records restricted when opening
(eg: using the where clause).

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.

Performance is not too terrible. Because I don't want to re-write much code,
then as mentioned, using a linked view to sql server makes the combo more
snappy. so, this approach is *less* work. I would simply make the combo box
pass-thought before I went to call back.

So,

1) do noting to combo box
2) if there is more then one column, or more then one table involved, then
create view sql side. link and specify the view as the source.
3) change the query used for the combo box to a pass-through.

Note that using a sql source for the combo box that has to use a linked
table is slow (but, a form can still usually have 1, or 2 of these...you
might not have to change this - it depnds on the query used in the
combo..and how many).

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?)

Late binding refers to if you have any code references to word, or
excel..not data.

 

[David W. Fenton]

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.

This is where many people make a mistake -- they assume that when
you remove Jet form the equation, you no longer have as many layers.
But that's not correct. ADO/OLEDB used form ADPs has a layer in
between that allows the ADP to communicate with ADO/OLEDB, and the
reason MS is now deprecating ADPs is that this additional layer is
just as problematic the Jet/ODBC layers.

Jet is *much* better at all of this than people give it credit for.