Access as server

[Petr Danes]

I've kind of an odd idea, and was wondering if someone could provide a few
thoughts on it.

I have a database that has grown from a Q&D fix into a serious application,
used by an entire department, and am now faced with with converting it into
a true network app. I have already split it into a backend on a server and
frontends on users' machines, but the dataset is fairly large and since this
was not initially designed as a network app, many of the queries look at an
entire table. In the new configuration, that means sucking the entire table
across the network, which naturally causes delays.

Fortunately, the network is very underutilized at the moment, so it's not a
huge concern, but it's annoying and I do want to fix this so it works
properly. That will likely mean SQL Server, stored procedures and all that.
But it's a sizeable step and I was wondering if I might be able to do this
another way.

I've read posts from people wanting to execute queries stored in an Access
backend, which obviously doesn't work, when Access isn't running there.

BUT-------

What about if Access IS running? I have written a number of Office apps that
use automation between members to, for instance, generate a dataset in
Access and pass it to Word for formatting, or pull an Excel worksheet into
an Access table for grouping and querying. It occurs to me that just maybe,
something of the sort could be done in a RUNNING Access backend.

Several possibilities occur to me:
1. Have the frontend directly latch onto the running instance of Access on
the server, via automation. This works very nicely on one machine, even
allows the caller to directly execute code in the callee, but I don't know
if it would work accross a network.
2. Have the frontend change some values in a shared table set up
specifically for that purpose, which the backend would poll regularly and
frequently, and on that basis take some action, like populate a table with a
filtered dataset, which the frontend could then read.
3. ODBC connections to a query instead of a table, although I don't even
know if the ODBC engine would do that.

Has anyone tried anything like this, or have I finally overdone it with the
drugs?

Pete

--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and I
use yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

[Stefan Hoffmann]

hi Petr,

On 20.03.2010 17:22, Petr Danes wrote:
> I have a database that has grown from a Q&D fix into a serious application,

Imho this is not possible, you will still carry around some legacy stuff
from the Q&D, so it is still D.

> In the new configuration, that means sucking the entire table
> across the network, which naturally causes delays.

This would be only true, if your tables have no indices. So proper usage
of indices will reduce this kind of network traffic.

> It occurs to me that just maybe,
> something of the sort could be done in a RUNNING Access backend.

Yes, this can be done. But it's not worth the time.

> Has anyone tried anything like this, or have I finally overdone it with the
> drugs?

Nice phrasing :)

How many concurrent users do you have? In the first step I would
recommend a plain backend migration to SQL Server 2008 Express which is
for free. This means only migrate the tables and test it. In many cases
this was sufficient enough.

You can in a further step consider which queries and logic you migrate
to views and stored procedures.

mfG
--> stefan <--

[Petr Danes]

"Stefan Hoffmann" <ste...@ste5an.de> pí?e v diskusním príspevku
news:uNh$6KFyKH...@TK2MSFTNGP06.phx.gbl...

> hi Petr,
>
> On 20.03.2010 17:22, Petr Danes wrote:
>> I have a database that has grown from a Q&D fix into a serious
>> application,
> Imho this is not possible, you will still carry around some legacy stuff
> from the Q&D, so it is still D.

It's been in use for years and I have been tuning and adding all that time.
No doubt there are corners that I have missed, but I have gone through it
repeatedly and cleaned up anything that wasn't the way I wanted, any time
the users wanted a change. It's always possible to find details that are not
perfect, but there is nothing in the fundamental design that I would do
differently if I were building it from scratch today.

>> In the new configuration, that means sucking the entire table
>> across the network, which naturally causes delays.
> This would be only true, if your tables have no indices. So proper usage
> of indices will reduce this kind of network traffic.

All the tables are indexed properly, but I do joins, grouping queries and
the like, which require all the data to be examined. Since the backend is an
Access file, not a server, the only way to examine all records is to bring
them across the wire to the frontend machine executing the query. If you
know of a way around that, I'd be happy to hear about it.

> How many concurrent users do you have? In the first step I would recommend
> a plain backend migration to SQL Server 2008 Express which is for free.
> This means only migrate the tables and test it. In many cases this was
> sufficient enough.

There are 14 people in the department, but I would rarely expect more than
two or three to be using the DB simultaneously, and even then, mostly for
lookups. There may be up to a half-dozen or so copies open at any one time,
but mostly due to people simply leaving it running, like an email client,
and only occasionally looking something up. Active updates are not done
often and even when they are, the traffic is light.

I most likely will go to SQL Server eventually, right now I'm just finishing
a fairly sizeable design upgrade to accomodate a new and expanded set of
requirements, including the network use, (previously it was a single-user
system, where they all took turns), so my current priority is getting all
that running correctly, even if not quite as fast as I would like.

But if I only migrate the tables to SQL Server, how have I accomplished
anything with regard to the speed issue? When I offload the work to the
server, obviously it should help a lot, but if I still do the joins locally,
I'm still bound by the time necessary to copy entire datasets, no matter
what software is managing the tables on the other end.

Pete

[Philipp Stiefel]

"Petr Danes" <FUSpa...@no.spam> wrote:

> But if I only migrate the tables to SQL Server, how have I accomplished
> anything with regard to the speed issue? When I offload the work to the
> server, obviously it should help a lot, but if I still do the joins locally,
> I'm still bound by the time necessary to copy entire datasets, no matter
> what software is managing the tables on the other end.

SQL-Server is not just "managing the tables". Access will try to
pass your whole query, including all the joins, to the SQL-Sever
which is then processing the query and returning only the results.
This will work with many queries but not with complex joins and
aggregations.

To make even complex queries work well with SQL-Server and Access,
you can create the basic queries, without criteria, as views on
the SQL-Server. Then you link those views to Access, build the
actual query with all criteria on top of the view in Access. This
will result in almost all the query processing being done by the
SQL-Server.

Best wishes
Philipp

[Petr Danes]

"Philipp Stiefel" <ph...@codekabinett.de> píse v diskusním príspevku
news:ho830e...@pluto.ksw.codekabinett.com...

> "Petr Danes" <FUSpa...@no.spam> wrote:
>
> SQL-Server is not just "managing the tables". Access will try to
> pass your whole query, including all the joins, to the SQL-Sever
> which is then processing the query and returning only the results.
> This will work with many queries but not with complex joins and
> aggregations.

Okay, I didn't know that. Much of the reading I've done on the subject
indicates that any sort of join or aggregation requires a local copy of all
the data, because unless the join is specifically written as a stored
procedure, the server will not know what the client ultimately wishes to do
with it.

Now you tell me that is not true. My experience with SQL Server is minimal,
so I'm certainly willing to listen, especially since I'm probably going to
have to go that route eventually anyway. But you say it does not work with
complex joins and aggregations. What defines a complex query? What can I do
to avoid having a query regarded as complex?

> To make even complex queries work well with SQL-Server and Access,
> you can create the basic queries, without criteria, as views on
> the SQL-Server. Then you link those views to Access, build the
> actual query with all criteria on top of the view in Access. This
> will result in almost all the query processing being done by the
> SQL-Server.

Do I need to make such queries pass-through, using T-SQL, or will Access
translate it for me? And what about parameter queries, including those that
use a value on a form as a parameter? Surely SQL Server cannot see back onto
my form - will Access pass it as a parameter, or filter incoming records, or
translate it as a query with a hard-coded WHERE condition?

The stored procedure route with parameters seems the most effective route,
according to what I've read, but I have yet to do much meaningful
experimentation.

Pete

[Jeff Boyce]

Yes, SQL-Server cannot "see back to your form" ... but if you create a query
in Access that points to the SQL-Server data and looks at an Access form for
criteria, you can still use both.

Stored procedures and pass-through queries are quite helpful in limiting the
number of records that SQL-Server needs to pass through the network to
Access, ... but you have other options for ways of doing this that require
neither of those techniques.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Petr Danes" <skrusp...@no.spam> wrote in message
news:%23Thjwgd...@TK2MSFTNGP04.phx.gbl...

[Armen Stein]

Many normal Access queries will be handed back to SQL Server and
processed very efficiently on the back-end. The problems come when
you use local parameters, form references, Access/VBA functions, and
aggregate subqueries in your query.

If there's a delay and you see "Query running" in the status bar, you
know Access has taken over and is processing the query in the
front-end.

There are quite a few practices that will really help with a SQL
Server back-end. For example, you can swap out the Where clause in a
passthrough query instead of referring to criteria directly on forms.
I've posted examples of this technique on our free J Street Downloads
page at http://ow.ly/M58Y
See "Report Selection Techniques".

I've also written a PowerPoint presentation on techniques for using
Access as a client-server front-end to SQL Server databases. It's
called "Best of Both Worlds" at our free J Street Downloads page:
http://ow.ly/M2WI. It includes some thoughts on when to use SQL
Server, performance and security considerations, concurrency
approaches, and techniques to help everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Petr Danes]

Thank you Armen, good information there. I've been putting off the jump to
SQL Server, due to concerns about managing it. An Access DB is simple, one
file, portable via flash disk, back up is just make a file copy, etc. But
most of my apps so far have been small scale, single user types. This is
going to require some serious head-scratching, but it's probably
unavoidable. Thanks for the tips, I see I have much to learn.

Pete

"Armen Stein" <Armen...@removethisgmail.com> píse v diskusním príspevku
news:pjmgq5t1jioh5h2rs...@4ax.com...

[Tony Toews [MVP]]

"Petr Danes" <skrusp...@no.spam> wrote:

>BUT-------
>
>What about if Access IS running? I have written a number of Office apps that
>use automation between members to, for instance, generate a dataset in
>Access and pass it to Word for formatting, or pull an Excel worksheet into
>an Access table for grouping and querying. It occurs to me that just maybe,
>something of the sort could be done in a RUNNING Access backend.
>
>Several possibilities occur to me:
>1. Have the frontend directly latch onto the running instance of Access on
>the server, via automation. This works very nicely on one machine, even
>allows the caller to directly execute code in the callee, but I don't know
>if it would work accross a network.
>2. Have the frontend change some values in a shared table set up
>specifically for that purpose, which the backend would poll regularly and
>frequently, and on that basis take some action, like populate a table with a
>filtered dataset, which the frontend could then read.
>3. ODBC connections to a query instead of a table, although I don't even
>know if the ODBC engine would do that.
>
>Has anyone tried anything like this, or have I finally overdone it with the
>drugs?

Trouble is you lose so much of the functionality of bound forms.
They're so easy to use. As Stefan indicates an upsizing to SQL Server
Express is a concept. Also go back and fix up some of those queries
and rough spots.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

[Tony Toews [MVP]]

"Petr Danes" <skrusp...@no.spam> wrote:

>I have already split it into a backend on a server and
>frontends on users' machines,

Are you using a tool such as the Auto FE Updater to distribute your
changes to the FE quickly and efficiently to the users?