Access 2007 SQL over WAN

[Arthur]

I am currently using Access 2003 adp files as a front end for SQL 2000 DBs
located on a remote server over a T1. This is of course a little slow, but
not too bad. We are planning a switch to Access 2007, but the performance
for the same adp files in 2007 is extremely poor. Recreating a new adp
within access 2007 does not help. Any Ideas?

 

[Sylvain Lafontaine]

You're not the first one to come with this problem. Unless you find the
magic solution to correct this problem, I'm afraid that your only
possibilities would be to stick with A2003 until a *possible* fix from MS,
to switch to Terminal Server or a similar product like Thinsoft or to change
your ways of accessing data over the WAN (for example using unbound forms or
replace Access with something else like a web site or .NET).

 

[Tony Toews [MVP]]

You're not the first one to come with this problem. Unless you find the
magic solution to correct this problem, I'm afraid that your only
possibilities would be to stick with A2003 until a *possible* fix from MS,
to switch to Terminal Server or a similar product like Thinsoft or to change
your ways of accessing data over the WAN (for example using unbound forms or
replace Access with something else like a web site or .NET).

How would unbound forms improve performance? Or .Net?

While a website is of course possible you can lose a lot of functionality depending
on how much code you have behind the forms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[aaron.kempf]

uh then can't you keep it in Access 2003? use the runtime?

have you called Microsoft and asked for help on this?
I think that you should return you Office 2007 software and get a
lawyer and look for punititve damages.

Newer software should be FASTER not slower..

I mean COME ON MICROSOFT

tell us more about your database

have you run profiler and then the index tuning wizard?
do you use a bunch of custom datatypes?

I had some really really poor performance with ADP against SQL 2000
recently; of course it was a higly normalized schema with TONS of
custom data types and it took a while just to display the database
window, for example

can you run the 'generate scripts' wizard from SQL Server 2005
Management Studio and say 'change custom data types base types' and
see if that helps?

PS - what version of ADO do you have referenced in Access 2007? I
think that Office 2007 includes 'ADO 6.0' and I might try using an
older version of ADO; it's at least worth investigating.

hope that helps

-Aaron

 

[Sylvain Lafontaine]

One of the problems with bound forms is the number of useless queries made
by Access against SQL-Server. A quick look with the SQL-Server Profiler
reveals that this number is much greater with Access 2007 than with Access
2003 (probably by an order of magnitude ten times greater).

In the case of A2007, it's look like a firework but even with A2003, the
situation is - I could I say? - « perfectible ».

By using unbound forms or .NET, only the queries that you made from VBA code
or .NET code will be sent against SQL-Server; hence the improvement in
performance.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[aaron.kempf]

I've never had a problem with bound forms and ADP. I've used them
every day for 10 years

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

One of the problems with bound forms is the number of useless
queries made by Access against SQL-Server. A quick look with the
SQL-Server Profiler reveals that this number is much greater with
Access 2007 than with Access 2003 (probably by an order of
magnitude ten times greater).

In the case of A2007, it's look like a firework but even with
A2003, the situation is - I could I say? - ® perfectible ¯.

By using unbound forms or .NET, only the queries that you made
from VBA code or .NET code will be sent against SQL-Server; hence
the improvement in performance.

Why is Terminal Server not the obvious answer?

 

[aaron.kempf]

BECAUSE OF LICENSING COSTS?
BECAUSE OF PERFORMANCE?
BECAUSE OF LACK OF SECURITY?
BECAUSE OF LACK OF SCALABILITY?

USE ACCESS DATA PROJECTS

 

[aaron.kempf]

Terminal Server is the obvious choice if you're a fucking retard that
can't write stored procedures

 

[Sylvain Lafontaine]

If I remember correctly, I did mention TS.

Also, don't forget that you must not look only strictly from the point of
view of one client but also at the impact that this overhead over the
network and on the SQL-Server will have for other users.

You must also take a look at the price of TS, CAL licenses and the possible
necessity to have a more powerful machine to run all this stuff.

 

[Tony Toews [MVP]]

One of the problems with bound forms is the number of useless queries made
by Access against SQL-Server. A quick look with the SQL-Server Profiler
reveals that this number is much greater with Access 2007 than with Access
2003 (probably by an order of magnitude ten times greater).

In the case of A2007, it's look like a firework but even with A2003, the
situation is - I could I say? - « perfectible ».

Oh my, I had no idea A2007 was so bad.

By using unbound forms or .NET, only the queries that you made from VBA code
or .NET code will be sent against SQL-Server; hence the improvement in
performance.

Ok, but if you had two forms with identical controls such as combo boxes, list boxes
and subforms. But one was bound to a single record and the other was an unbound form
handling it's own updates what is the difference there?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Sylvain Lafontaine]

Ok, but if you had two forms with identical controls such as combo boxes,

list boxes
and subforms. But one was bound to a single record and the other was an
unbound form
handling it's own updates what is the difference there?

The first difference is when you are going from one record to another:
often, many of these combo boxes and list boxes doesn't need to be requeried
when navigating to another record. When using bound forms, they are often
automatically requeried - even if they don't need to - with the effect of
slowing down the navigation.

The second difference is when you dynamically change the record source of
these controls when opening the form. You expect that when one of the
control have been queried, changing the record source of another one won't
affect the first one. It has been observed that with ADP, that's not true;
so the same control can be queried multiple times.

There is a similar effect with subforms: when their displays depends on the
controls in the parent form, they are often queried to early if the record
sources for these controls on the parent are changed and the situation is
worse if there are other controls on the subforms to be manipulated, too.

Finally, there is the combination of all this, ie. changing the record
sources of controls, subforms and controls on subforms when navigating from
one master record to another.

With ODBC linked tables, I don't remember exactly but a speed comparaison
between two identical GUIs against the same database, one built with a MDB
file and linked tables and the other built with ADP has show me the MDB
solution to be slower over the WAN by a factor of something like three to
four times - give or take - and that the situation was getting worse and
worse while increasing the complexity of the GUI (I was working on both at
the same time).

On a LAN, a factor of 3 to 4 times is not a big deal; however, the
interesting point here is that both the ADP and the MDB file solutions are
slower if we would have compared them to a solution using unbound forms
because only the queries that would make explicitely by the VBA code in the
exact order required to load/refresh all the controls/ subforms/ subcontrols
with the minimum number of operations would be sent over to the SQL-Server.
With unbound forms, I don't expect that I would see any difference of speed
between an ADP or a MDB GUI because the exact same number of requests would
be made in both cases.

Finally, with ADP 2007 (using bound forms), the situation seems to have got
totally out of control while it (probably) remains the same Access 2007 and
ODBC linked tables. This conclusion is based on the fact that's only with
ADP 2007 that we are now seeing a number of complaints about speed problems
(as compared to ADP 2003) popping out like mushrooms (and this not only over
the WAN but also over a LAN when the required charge is anything but light).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[Tony Toews [MVP]]

The first difference
The second difference
There is a similar effect with subforms:
Finally, there is the combination of all this, ie. changing the record
sources of controls, subforms and controls on subforms when navigating from
one master record to another.

Very interesting detailed explanation snipped.

Thanks for the detailed explanation. I suspect I can see why MS would've done what
they did in requery the combo boxes. Easier to requery too often rather than exactly
when required. And difficult to test. Although why A2007 would have so much more
network traffic is beyond me.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Sylvain Lafontaine]

Not only it's easier to requery to often but it's often mandatory because
Access doesn't have any real understanding of your program and the more
complicated your GUI will be, the fartest the solution given by any
automated method will be from the ideal solution.

In the same way that's your responsability to learn how to drive with a
manual transmission if you want to conduct a big truck with over 50,000
pounds of stock to drag behind, if you responsability to take the situation
in your own hands and go with unbound forms if the situation become too
complicated to be adequately fulfilled by the automated process of Access.

However, with ADP 2007, there is clearly a problem of follow-up here because
this speed problem is not present with A2003 when using exactly the same
forms. I don't know if this is a simple very little bug hidden somewhere
that could be corrected in a few minutes or the effect of a major
redesigning of some components but the fact that this problem easy to
notice - easy because it's a direct out-of-the-box experience - has crawled
into the final release of Access 2007 is clearly an indication that ADP is
no longer in any kind of priority at MS. In the worst case - a bug
impossible to correct without a major rewriting - all they have to do was to
drop a copy of A2003 into the box to get at least the same level of
performance as ADP 2003 (and any talk about compatibility with SQL-2005 is
only gna-gna).

I don't know if this problem will be corrected someday but even if it's
corrected in the next SP for Office, I would no longer suggest to anyone to
invest any effort into going with ADP for any new project against
SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

Also, don't forget that you must not look only strictly from the
point of view of one client but also at the impact that this
overhead over the network and on the SQL-Server will have for
other users.

TS adds almost none of that, at least not a significant amount in
comparison to connecting to SQL Server over the WAN (TS can be run
over dialup and be completely usable).

You must also take a look at the price of TS, CAL licenses and the
possible necessity to have a more powerful machine to run all this
stuff.

In my experience, those costs will be minuscule in comparison to the
costs of redeveloping an app for use over the WAN directly, or in
conversion to a browser-based app. No changes are required in your
Access app at all, in fact.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

because
Access doesn't have any real understanding of your program and the
more complicated your GUI will be, the fartest the solution given
by any automated method will be from the ideal solution.

I no you intended an "h" to be included in that one word, but I
think it works OK as it.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

In the same way that's your responsability to learn how to drive
with a manual transmission if you want to conduct a big truck with
over 50,000 pounds of stock to drag behind, if you responsability
to take the situation in your own hands and go with unbound forms
if the situation become too complicated to be adequately fulfilled
by the automated process of Access.

I don't quite understand the logic here. Why would you do the work
to engineer an unbound app for running across a WAN when it's going
to take much, much less work (and far less cost) to simply run the
damned thing on Terminal Server? Yes, of course, once you've already
made the investment, TS becomes more expensive comparatively, but it
looks to me like a bad decision to begin with.

[]

I don't know if this problem will be corrected someday but even if
it's corrected in the next SP for Office, I would no longer
suggest to anyone to invest any effort into going with ADP for any
new project against SQL-Server.

Why is anyone surprised at this? MS has been deprecating ADPs for
several years now, starting with A2K3. They clearly had no clear
development path for them, and nobody on the development really on
top of them (there wouldn't have been so many reversions of fixed
bugs and new bugs in the 2nd and 3rd generation of ADP if that were
the case), and, frankly, the whole justification for the ADP never
made any sense to me in the first place. It all seemed that it was
based entirely around an irrational fear of Jet, and in taking Jet
out of the equation, they had to add a layer to replace it that
introduced problems of its own. And, of course, in somce
circumstances, ADO/OLEDB did just as much incorrect guessing about
what you wanted as Jet did.

To me, the handwriting was on the wall a long time ago, so I just
don't get why people went so heavily into ADP development as they
did.

 

[Sylvain Lafontaine]

For TS, I agree with you that's often the right decision and one of my
client is precisely using it. However, like any other tools, it cannot
solve all problems and there is a lot of situations where JET with linked
tables and with or without TS can't do it; either because of a security
problem (I wouldn't trust any company who would give access to a table
containing my credit card number using an ODBC linked table), a capacity
problem (if the box or the network are already saturated, adding JET over
the equation will only be suicidal) or a performance problem (queries are
already too complicated to be made with JET/VBA in an efficient way).

Like any toolbox, the more tools you have in it, the better your capacity to
find the right tool for each job. You know the old proverb: if you only
have a hammer in your toolbox, everything around you will start looking like
a nail.

For ADP, they have started developing it for solving many problems with JET
and ODBC linked tables but obviously, the navire changed its direction at
mid-course and is now steered toward .NET technologies. Three years ago,
the possibilities for working with SQL-Server were JET/ODBC linked
tables/Passthrough queries at one side of the balance and ADP at the other
side but now, ADP seems to have been replaced with .NET for the other side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

In the same way that's your responsability to learn how to drive
with a manual transmission if you want to conduct a big truck with
over 50,000 pounds of stock to drag behind, if you responsability
to take the situation in your own hands and go with unbound forms
if the situation become too complicated to be adequately fulfilled
by the automated process of Access.

I don't quite understand the logic here. Why would you do the work
to engineer an unbound app for running across a WAN when it's going
to take much, much less work (and far less cost) to simply run the
damned thing on Terminal Server? Yes, of course, once you've already
made the investment, TS becomes more expensive comparatively, but it
looks to me like a bad decision to begin with.

[]

I don't know if this problem will be corrected someday but even if
it's corrected in the next SP for Office, I would no longer
suggest to anyone to invest any effort into going with ADP for any
new project against SQL-Server.

Why is anyone surprised at this? MS has been deprecating ADPs for
several years now, starting with A2K3. They clearly had no clear
development path for them, and nobody on the development really on
top of them (there wouldn't have been so many reversions of fixed
bugs and new bugs in the 2nd and 3rd generation of ADP if that were
the case), and, frankly, the whole justification for the ADP never
made any sense to me in the first place. It all seemed that it was
based entirely around an irrational fear of Jet, and in taking Jet
out of the equation, they had to add a layer to replace it that
introduced problems of its own. And, of course, in somce
circumstances, ADO/OLEDB did just as much incorrect guessing about
what you wanted as Jet did.

To me, the handwriting was on the wall a long time ago, so I just
don't get why people went so heavily into ADP development as they
did.

 

[aaron.kempf]

David;

are you fucking kidding me?

Terminal Server is not a valid option for wide-spread deployment.

Reports should take a minute to open and run in a non-obtrusive
manner.
Logging onto a terminal session is called UNNECESSARY COMPLEXITY

ADP works great.
Lose the training wheels, kids

 

[aaron.kempf]

I disagree.

Microsoft understands the importance of ADP. First and foremost.

they just haven't given us the new version yet.