Re: Access 2003 to SQL Server 2000 over a VPN

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

  • 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
    ... you're careful about your database design, though, that may be the way to ... As a result of the difficulties I've had, I try to avoid SQL Server ... Would you consider using an ADP talking across an fast broadbank link to ... A thin client interface has been suggested, ...
    (microsoft.public.access.adp.sqlserver)
  • RE: SQL Server 2000 & Access 2007 Project
    ... You have indicated that the Access database is an Access Project file (ADP) ... occurs when we attempt to open the same table's design view in the Access ... so that SQL server cannot get the type of the data in the design view. ...
    (microsoft.public.sqlserver.clients)
  • Re: Upsizing design considerations
    ... > I work from home, where I can access the VPN, and would like to ... Anyway, so save you some grief, you can read the following about ms-access ... > with Jet or use ADP? ... file that works with sql server. ...
    (microsoft.public.access.gettingstarted)
  • Re: Access awake after 7 years?
    ... Some liked the idea of a "streamlined SQL Server client", ... _any_ ODBC-compliant database. ... I caution again, aaron, don't bet the farm on ADP / classic ADO / DAP being ...
    (comp.databases.ms-access)
Loading