Access over p2p

[Adam Milligan]

I have a relatively small (only a handful of tables) Access 2003 application
split between front end and back end working very well right now. We have a
second location about 10 miles away and a dedicated 1.5 Mbps T1 line running
between them. Does anyone have any input as to how well an Access
application would perform with a front end application at one location
connected to a back end mdb in the other location. I will be testing
performance this week, but was wondering if anyone else had experience with a
similar set up and what the "gotchas" might be. Thanks!

Adam

 

[Steve Schapel]

Adam,

There is some good information on this here:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

In summary, bad idea, forget it.

 

[Adam Milligan]

Steve-

Thanks for the article. I was afraid that would be the case. Do you know
if having a SQL server back-end would make a difference or if Access still
uses the same "connected" model that it does with an mdb backend? Thanks
again.

Adam

 

[Scott Lichtenberg]

I've used Access as a front end to remote SQL databases. The trick is to
not link your tables, but to tie your forms to queries or recordsets which
pull only the necessary records (and fields from those records) from the
server. I tend to work in a drill-down structure. I'll have a form with a
listbox - customers, for example, that has two or three fields. When the
user clicks on a customer, I'll update the SQL property of a query ("SELECT
* FROM Orders WHERE Customer = " & MyCustomer). This then becomes the
recordsource for my orders selection form. When the user selects an order,
I update my orders query and use that as the recordsource of the orders
form.

As a result, I rarely pull down more than a couple of thousand characters of
data at a time. You should have no problem over a T1 line.

 

[Armen Stein]

As a result, I rarely pull down more than a couple of thousand characters of
data at a time. You should have no problem over a T1 line.

I agree, using a SQL Server back-end will allow this to work, if some
client-server techniques are used.

The key is to limit the amount of information that is sent over the
wire. There are a lot of ways to do this - I review many of them in
the slide deck "Best of Both Worlds" at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.

When we are careful to limit our bound updatable forms to just one (or
a few) records, use passthrough queries for long lists and heavy
comboboxes, use RowVersion and NOLOCK, etc. we see our applications
perform quite well over DSL-speed connections. Maybe not as fast as a
local LAN, but usable.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Steve Schapel]

Adam,

How many users at the second location? I myself would seriously
consider providing them with RDP access, aided by something like
http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp.aspx
.. The first obvious advantage being that you could use your application
"as is" with no further development or modifications required.

 

[Tony Toews [MVP]]

I've used Access as a front end to remote SQL databases. The trick is to
not link your tables, but to tie your forms to queries or recordsets which
pull only the necessary records (and fields from those records) from the
server.

However you must still use linked tables right? You just don't have
any forms or reports which are bound to the complete table but rather
queries.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

When we are careful to limit our bound updatable forms to just one (or
a few) records, use passthrough queries for long lists and heavy
comboboxes, use RowVersion and NOLOCK, etc. we see our applications
perform quite well over DSL-speed connections. Maybe not as fast as a
local LAN, but usable.

I am just curious. Do you still use the Where clause on the
docmd.openform or openreport? For example:

DoCmd.OpenForm "FAQ Details", , , "[fID]=" & Me![fID]

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Armen Stein]

I am just curious. Do you still use the Where clause on the
docmd.openform or openreport? For example:

DoCmd.OpenForm "FAQ Details", , , "[fID]=" & Me![fID]

Hi Tony,

Yes, we use the WhereCondition to open an updatable detail form
limited to just one record. Access is quite efficient with this. We
can also have updatable continuous subforms with child records as long
as there aren't too many (dozens or hundreds of records are okay).

Since the detail form is open for just one record, we can't use the
built in record navigation or PgUp & PgDn. Instead, we provide our
own DVD navigation buttons that requery the form with the desired
record.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Adam Milligan]

Steve et al-

Wow! THanks for all of the responses and input. It looks like I have more
options than I originally thought. To answer your question, I am expecting
about 5-10 users (not concurrent) at each of the two locations. When budget
time comes around, we might see 5 or 6 concurrent users on this particular
application. However, the future is SQL server no matter how I slice it
since I want to build on the table structure of my little budgeting program
to eventually create an entire student information system for a boarding
school of about 200 kids (reprot cards, teacher comments, scheduling,
medicatiion, etc.) I absolutly love developing in Access, though, so I hope
to keep the Access front-end. Again thanks for all the input. I will check
out the RDP article you provided at well.

Adam

 

[Scott Lichtenberg]

However you must still use linked tables right? You just don't have

any forms or reports which are bound to the complete table but rather
queries.

Tony

Hi Tony,

Actually, for my production apps I don't use linked tables at all. I have
local access tables that are copies of the SQL tables. When a user requests
a form, I have a function which retrieves the data and puts into the local
tables. If a record is added, updated or deleted, I fire off code in the
AfterUpdate/AfterDeleteConfirm event to update the SQL Server.

I ended up doing this because I used to run into locking problems with
linked tables in Access 97. The original concept of linking to SQL tables
sounded great until someone opened a large recordset. A97 would load a
couple of hundred records and then sit with the table locked until the user
scrolled to the bottom of the recordset. You can imagine the fun I had with
deadlocks. I'm under the impression that A2007/ADO has addressed this. I'm
doing some new development so if you can point me to any resources on this,
I'd greatly appreciate it.

I've been fooling around with some Access 2007 applications that use ADO
recordsets as the rowsource. I create the recordset outside of the form and
set the forms recordsource property when it opens. As a result, I don't
have any linked tables. It works, but it's a pain - I have to create ADO
recordsets / passthrough queries for everything, including combo boxes, etc.
It would be really nice if A2007 doesn't have the locking problems that A97
had with linked tables.

Scott

 

[Armen Stein]

I ended up doing this because I used to run into locking problems with
linked tables in Access 97. The original concept of linking to SQL tables
sounded great until someone opened a large recordset. A97 would load a
couple of hundred records and then sit with the table locked until the user
scrolled to the bottom of the recordset.

Hi Scott,

This can definitely be a problem with large recordsets opened for
update. We avoid the problems by always displaying an "index"
continuous form that lists all the records and allows the user to
sort, select and find. This form is based on a very fast passthrough
query using the NOLOCK hint. Of course you also need appropriate
indexes on SQL Server for the best speed.

Then when the user drills down to see & edit the detail on a record,
the detail form is bound to a normal linked table, but limited to only
that one record and maybe some related records in subforms. There are
no locking problems in this case.

We almost never need to copy data locally or use forms bound to
recordsets. We try to use Access as natively as possible to avoid
extra work. As a side note, we are not using Access 2007 for many
clients yets, so the techniques I'm describing are useful for previous
versions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Scott Lichtenberg]

Hi Armen,

On the index portion, that's pretty much what I do - forms and list boxes
bound to pass through queries. Lots of NOLOCKs on the SQL side, although
SQL2005 handles locking far better than its predecessors (or so I am led to
believe) and doesn't need NOLOCKs in every proc.

I did most of my major development in A97/SQL6.5 and I had locking problems
with forms based on linked tables, even when the records brought in were
restricted by filters or WHERE clauses in the OpenForm command.

I'm glad to hear that this has been taken care of. It's going to make my
life a lot easier. Thanks for your input.

Regards,
Scott

 

[Armen Stein]

I did most of my major development in A97/SQL6.5 and I had locking problems
with forms based on linked tables, even when the records brought in were
restricted by filters or WHERE clauses in the OpenForm command.

I'm not exactly sure how all the plumbing works, but I believe ODBC
drivers have also gotten a lot better at allowing Access to pass Where
clauses through to the server. So even Access queries and filters on
linked tables can behave more "client-server" with SQL doing the
selection on the back end.

You can't count on this for more complex queries though - I've done
presentations showing a dramatic performance improvement when using
the exact same subquery syntax in a passthrough vs. linked table. Like
from 20 seconds to sub-second.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

I am just curious. Do you still use the Where clause on the
docmd.openform or openreport? For example:

DoCmd.OpenForm "FAQ Details", , , "[fID]=" & Me![fID]

Yes, we use the WhereCondition to open an updatable detail form
limited to just one record. Access is quite efficient with this. We
can also have updatable continuous subforms with child records as long
as there aren't too many (dozens or hundreds of records are okay).

Ok, that sounds very much like how I write all my apps in Access
anyhow.

Since the detail form is open for just one record, we can't use the
built in record navigation or PgUp & PgDn. Instead, we provide our
own DVD navigation buttons that requery the form with the desired
record.

Nah, can't be bothered to do that. Then can just close the form,
which displays the continuous form and then open the next one.

And when I think about it I can't see how often a user would want to
go to the next record.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Then when the user drills down to see & edit the detail on a record,
the detail form is bound to a normal linked table, but limited to only
that one record and maybe some related records in subforms. There are
no locking problems in this case.

Thus taking advantage of all the Access RAD capabilities.

We almost never need to copy data locally

Yes, Scott's approach, while understandable back in those days, is a
lot of extra work.

or use forms bound to recordsets.

Because those are bound to a pass through query.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Actually, for my production apps I don't use linked tables at all.
I have local access tables that are copies of the SQL tables.
When a user requests a form, I have a function which retrieves the
data and puts into the local tables. If a record is added,
updated or deleted, I fire off code in the
AfterUpdate/AfterDeleteConfirm event to update the SQL Server.

I think that's the stupidest thing I've ever read. It makes no sense
whatsoever to make things so complicated, and it adds a lot of
possible problems. Your SQL update code better be pretty
well-provisioned with error handlers.

You might as well not bother with Access if you're going to mis-use
it in this fashion.

 

[David W. Fenton]

How many users at the second location? I myself would seriously
consider providing them with RDP access, aided by something like
http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp
.aspx . The first obvious advantage being that you could use your
application "as is" with no further development or modifications
required.

Let me second the concept behind the recommendation. I've never used
Winconnect, but many here whom I respect speak highly of it.

If the client has only a peer-to-peer network (no Windows servers),
then a workstation acting as a Winconnect remote access server would
be OK. But I'd question whether at that point it might just be
better (if you're going to be setting up a dedicated PC) to just go
with an actual Windows server, instead, and use Windows Terminal
Server. That is certainly likely what I would do in this situation.
The reason is that by the time a client of mine needs to support
multiple users at more than one location, they really ought to be
investing in an actual dedicated server, rather than limping along
with peer-to-peer networks (which are really a pain in the ass to
administer if you give a rat's ass about security; if you don't care
and are fine with using the Everyone group to provide access, then
that works, but I'd consider it malpractice on the part of any
consultant setting up such a network).

 

[David W. Fenton]

=?Utf-8?B?QWRhbSBNaWxsaWdhbg==?=

Wow! THanks for all of the responses and input. It looks like I
have more options than I originally thought. To answer your
question, I am expecting about 5-10 users (not concurrent) at each
of the two locations. When budget time comes around, we might see
5 or 6 concurrent users on this particular application. However,
the future is SQL server no matter how I slice it since I want to
build on the table structure of my little budgeting program to
eventually create an entire student information system for a
boarding school of about 200 kids (reprot cards, teacher comments,
scheduling, medicatiion, etc.) I absolutly love developing in
Access, though, so I hope to keep the Access front-end. Again
thanks for all the input. I will check out the RDP article you
provided at well.

A remote access solution, such as hosting the app on a Windows
Terminal Server, is by far the simplest solution. You won't need to
switch to SQL Server to get it working, and can put that off until
it gives tangible benefits.