Access 2003 to SQL Server 2000 over a VPN

[James Franklin]

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

 

[Sylvain Lafontaine]

Unless your forms and the schema for the database are really very simple or
that you know exactly what you are doing, using Access with ODBC linked
tables against a SQL-Server over the WAN (with or without a VPN) will have
all the chances of beeing very slow; up to the point of beeing unusable.

You say that your client has a simple Access database, so you should be OK.
However, if it's not the case and the users start complaining about the
speed of the system when accessing it over the WAN then you will have to use
some more advanced design such as:

Terminal Server (TS)/Citrix or Thinsoft. By far and large the easiest of
all the solutions but at a cost ($). Other advantage: good with Access as
the backend, too.

Views/Passthrough queries.
Unbound Forms.
ADP.
..NET: either ASP.NET or WinForms or both of them.

My suggestion: start with MDB and ODBC linked tables. If this doesn't work
correctly but you have the money then go with TS/Citrix/Thinsoft; if not
then search this newsgroup and others about the other solutions. Don't
forget that with this small number of users (15), you main problem will be
the speed for the remote users.

 

[Robert Morley]

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

 

[Tom Wimpernads]

Robert;

where did you come up with this crap, bitch?

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

 

[James Franklin]

Hi,

Thanks guys for your feedback. It is appreciated.

One thing I probably didn't explain properly. Instead of using bound forms
with ODBC linked tables, I was planning to use unbound forms. Each form
would open an ADO connection in its load event. If the form is a continuous
form, my code would be something like this:

Global Const strConnection = "Data Source='LMS';User ID='sa';Password='sa';"

Dim rstFrm As New ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.Open strConnection
rstFrm.Open SQLstring, cnn, adOpenStatic, adLockReadOnly

Set Me.Recordset = rstFrm

rstFrm.Close
Set rstFrm = Nothing
cnn.Close
Set cnn = Nothing

For single record forms (e.g. adding/editing data) I was planning to use
unbound forms, populating controls with data from a connection to a stored
procedure returning that single record. Likewise, all data manipulation
would also be done by opening a connection and running a stored procedure.

Does this make any difference to the bandwidth requirements and am I barking
up completely the wrong tree here?

Thanks,
Jim

 

[Robert Morley]

Unbound forms will generally tend to reduce bandwidth requirements due to
the fact that Access is never doing anything that's not directly controlled
by you.

The one thing to be aware of, however, is that opening a connection with
each form will tend to increase bandwidth requirements, however, as there
will be the overhead of negotiating the connection every time.



Rob

 

[Robert Morley]

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

 

[Sylvain Lafontaine]

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]

First, replace things like:

Dim rstFrm As New ADODB.Recordset

With:

Dim rstFrm As ADODB.Recordset
Set rstFrm = New ADODB.Recordset

Do the same thing with the connection object but try to reuse the same one.

By using unbound forms, you will reduce the bandwith but you might have
problem with subform and subreport. Also, the difference in complexity
between using an unbound form or ADP up to the point where the queries
become read-only because they are too complex is unknown to me.

Finally, if you want to use unbound forms, maybe you should use a technology
such as .NET that has been designed to work with « unbound forms » from the
beginning.

 

[Robert Morley]

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]

I cannot confirm anything because at this moment, I have no installation of
Office 2007 and before I get one, I have a lot of cleanup to do on my hard
drives.

 

[Guest]

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]

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]

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]

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

 

[Vayse]

Interesting question, Guy. I'm sure its an issue all of us will run into.
I'm no expert in this area, just want to throw out a few of my own thoughts.
My current workload is an even split between VB 2005 and ADPs.
The way I see it, the options are:

1) Stick with ADP, possible latency issues, as Rob described. Use of
timestamps may help - I've never tried timestamps across different
timezones.
2) Use ADP with Replication. Main issue is learning about Replication.
(don't know much about it myself)
3) Use dotnet front end. IMO, you would still have the same latency issues.
I know dotnet supports disconnected recordsets, but I don't see how that
would be superior to ADPs in this case.
4) ASP. Central server, all clients use an ASP frontend which is hosted on
that server. If you have to switch to dotnet, then it will take around the
same amount of effort to switch to ASP as any other dotnet language.No
latency issue. Possible security issues, but you should be ok if using a
private network and learn the security fundamentals.

If you need anyone to test the Irish end, give me a shout.

Vayse

 

[James Franklin]

Thanks Sylvain, and everyone else for all your feedback.

One quick question, you mentioned:

First, replace things like:

Dim rstFrm As New ADODB.Recordset

With:

Dim rstFrm As ADODB.Recordset
Set rstFrm = New ADODB.Recordset

Do these not to exactly the same thing? I always thought one was just a
shortcut for the other?

Jim

 

[Douglas J. Steele]

You may find
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=24747
and http://www.aivosto.com/vbtips/not-optimize.html interesting

 

[mcnews]

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

one gotcha with replication is the you can't have any spaces or non-
alpha characters in your table or field names. if you're a true
software developer then you won't anyway, but sometimes we inherit
things....

 

[DAVID]

Active Directory is probably one of the most widely
deployed multi-site database systems. And recomended
use is not one central database for multi-national
systems: Active Directory uses replication between
multiple local databases.

My opinion about SQL Server is the same. I think
that there are two good ways to use SQL Server with
non-local clients: (1) With all the database activity
local to the Server, or (2) With all the database
activity local to the client. My opinion is that
(3) remote access to SQL Server, is not a good option.

If you go with method 1, people have used lots
of different kinds of servers: VPN, Web, DCOM
HTTP, SMTP etc. They all work for different people.

If you go with method (2), there are a number
of different techniques for replication, but
AFAIK, they mostly predate SQL Server native
replication, so if you want to try replication,
that would be the technique to try first.

(david)