Re: Access 2003 to SQL Server 2000 over a VPN

With WANs, it really becomes a question of latency. If your network has
sufficiently fast response times, ADP may be a good enough client,
especially if you're taking steps to minimize the number of transactions
going back and forth (not retrieving more info than you need, not getting
records one-by-one if you're going through the entire set, etc.). That
said, I suspect that UK and NZ-based networks would be largely
satellite-based, which are probably fairly high latency. Your best bet is
probably to design a small test app and try a few of your common job
functions on it and see how it is.

You may also want to consider VB6, .NET, or something else along those
lines. I'm not overly familiar with front-ends outside of Access, so I
can't really comment beyond the generalities that you can probably pick up
fairly quickly from elsewhere on the web.

Replication can be another solution, though SQL Server certainly doesn't
make it simple. In particular, I've had a lot of trouble with things like
interdependencies, field defaults that were functions (where the lack of
that same function in the partner database caused replication to break), and
other such things. It's much more strict than Access replication was. If
you're careful about your database design, though, that may be the way to
go. As a result of the difficulties I've had, I try to avoid SQL Server
replication, so I don't have a lot of experience there.



Rob

"Guy" <Guy@xxxxxxxxxx> wrote in message
news:OpmuRzduHHA.3376@xxxxxxxxxxxxxxxxxxxxxxx
Rob,

Coming back to the original VPN discussion... and apologies in advance if
a SQL Server newsgroup would be more appropriate for this posting but I'm
an ADPer and I sure other ADPer's would be interested in any responses.

Would you consider using an ADP talking across an fast broadbank link to
access a database on the other side of the world?

As a rapidly growing small company with 40 outlets in Australia, New
Zealand, Ireland and the UK we are looking at options to consolidate
information. Currently each outlets runs their ADP application against a
local database, but ideally we would like to centralise.

A thin client interface has been suggested, and although my experience
with such interfaces is very limited, I have heard that performance across
even a fast broadbank link is often insufficent when we are talking very
wide area networks.

Would SQL Server replication be an option, and what type of replication
would be most appropriate? Unfortunately whilst I have been using SQL
Server as a developer since version 4 my experience with replication is
also limited.

I assume that some database redesign may be necessary to reduce
synchronisation problems (particulary with identity keys) but I assume
that replication wouldn't have any impact on the working of the ADP
itself.

Your thoughts appreciated.

Regards,
Guy





"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23pOPBESuHHA.4440@xxxxxxxxxxxxxxxxxxxxxxx
Glad to hear it, Guy. As I understand it, the problem was that Access
was unnecessarily enumerating a large number database objects and
sub-objects (i.e., all tables and all fields of those tables, etc.). I
might be slightly off in that, as I've only read a couple of posts about
the problem, but I think that was it.



Rob

"Guy" <Guy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:49807A05-1158-4B2F-A67E-79291DFC4ED5@xxxxxxxxxxxxxxxx
Robert, Sylvain,

Not sure that I can fully respond to this question as I'm not aware of
what
aspects of Access 2007 ADP's were regarded as performing slowly.

However, I installed the patch a few weeks ago and whilst startup
performance appears to have improved. Shutdown performance is still
slow, but
once the application is up and running (as an ADP or ADE) it seems to
perform
as well as in previous Access versions, although I am still in the
process of
system testing following upgrading from A2003.

The main performance problems I have are related to the development
environment... in particular form design, just moving between tabs on a
tab
control to access subforms can take up to 30 seconds, and accessing the
VBA
window is also a lot slower than it use to be.

Generally I find the response of most commands even simple things like
Control Alignment to be much slower than previous versions of Access.
Although, not sure whether this is because the ribbon doesn't appear to
react
as quickly as the previous menu system, or the fact that I'm running on
Windows Vista with only a 3.2 GHz P4 and 2Gb of memory.

Having said all this I don't believe the performance problems I've
discovered so far are significant enough to prevent anyone from using or
upgrading an existing Access application to A2007.

Just my 2 cents worth.

Guy





"Robert Morley" wrote:

On a related note, I have no experience with A2007 myself, but I seem
to
remember hearing that the patch didn't entirely address the
sluggishness of
A2007 when opening ADP's...that it was "better", but not "fixed". Can
you
confirm whether or not that's still an issue, just so I know what to
tell
people? Thanks!


Rob

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:O6e3K9AuHHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
This is the aforementionned patch:
http://support.microsoft.com/kb/936519/en-us

However, I don't know personnally if it works properly.

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


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eCmIKSAuHHA.1052@xxxxxxxxxxxxxxxxxxxxxxx
Hi Aaron, bye Aaron.

(For those who may not have come across Aaron before, he is a very
abusive troll in the Access newsgroups whose entire purpose in life
is to
get everybody to use ADP/SQL Server for absolutely everything from
one-person databases to international databases to dating to washing
the
dishes. He has recently discovered the joys of changing his address
in
order to get around the blocks Microsoft has placed on him, and
frequently impersonates Access MVPs in the hopes of adding
credibility to
his abusive ravings.)


Rob

"Tom Wimpernads" <tw@xxxxxxxxxxx> wrote in message
news:O6r9%23W9tHHA.736@xxxxxxxxxxxxxxxxxxxxxxx
Robert;

where did you come up with this crap, bitch?

MS just came out with a patch for ADP a few months ago



"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:OUvd0P3tHHA.3468@xxxxxxxxxxxxxxxxxxxxxxx
With a moderately well-designed database, VPN should be quite
feasible.
The simpler the database, the easier it will be to set up in this
kind
of
environment. I would recommend looking at the possibility of
using an
ADP, rather than an MDB, but there's the distinct disadvantage
that ADP
may not be well-supported in the future. Microsoft has not
clearly
commented on their direction with ADP's. (There's also a bug in
Access
2007 that causes tremendous slowness for ADP's, though it doesn't
occur
in earlier versions.)

To give you an idea, I'm using a fairly complex database using and
ADP
as
the front end over a VPN connection. Since I work from home, I
use it
every single day. The VPN speed is nominally 3M/800k, though we
have
bad
lines in our area, so typically get about 2M/500k. The design of
the
database was not well-written for a client-server model, since
that's
not
how it started its life, though it's been tweaked to be at least
somewhat
better. Nevertheless, I find the speed quite acceptable for most
of the
work that I do.

A *very* simple, *very* well-designed client/server database could
conceivably even be used over a slow connection like a modem, but
as a
rule, I wouldn't want to try it.



Rob

"James Franklin" <jimbo.jetset@xxxxxxxxxxxx> wrote in message
news:5qSfi.6657$_l6.2897@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I have a client who has a simple Access database, which he now
requires
to be accessible to up to 50 concurrent users remotely over the
internet, as well as a small number of local LAN users.

I am looking at a solution whereby the data is stored in a SQL
server
database, with front-end Access mdb's on each machine, connecting
via
ODBC, either directly over the LAN or over a VPN for the remote
users.
The client will either have an ADSL or SDSL broadband connection
to
the
server through a managed firewall.

Can anyone tell me if this is feasible, and if so, what
connection
speeds would be required to support this number of users?
Although the
worst case scenario is 50 concurrent users, the likelihood is
that
there
would never be more than about 15 connected at the same time.

Any help is greatly appreciated!
Jim


















.

Relevant Pages

  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... Coming back to the original VPN discussion... ... Would you consider using an ADP talking across an fast broadbank link to ... local database, but ideally we would like to centralise. ... Would SQL Server replication be an option, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Recordsource only available to Administrator on clients
    ... if in the ADP all database items appear ... are you able to double-click and execute the uspPT stored procedure from ... Server instance is readily connected via SSMSE (Server type: Database ... to temporarily set a client user's privileges from, ...
    (microsoft.public.access.adp.sqlserver)
  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... The client runs a VP from Philly to NYC. ... Would you consider using an ADP talking across an fast broadbank link to ... local database, but ideally we would like to centralise. ... the front end over a VPN connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)