Access and WANs

[QB]

I have an access db that has been working for several years on a LAN setup.
I now have new requirement that require that it be useable over a WAN
(1-2mbps max).

I did some reading. Thank you Albert Kallal for your excellent
article(http://members.shaw.ca/AlbertKallal/Wan/Wans.html)! I was sure I had
my answer, Terminal Services... I proposed it to the IT department and the
whole thing is turning into a nightmare (new server, licensing,... - forget
about a timeline). So now I am back here asking for any possible
alternatives that would bypass involving the IT department. Something that
as a dba I can do.

Please treat me like I am ignorant when you explain!

If I were to upsize to SQL Server for my back-end, would this resolve my
issue? By upsizing do I get away from jet or for that do I need to redo my
front-end? Could someone please explain a little more about this or any
other true alternatives.

Thank you so very much,

QB

 

[Paul Shapiro]

There is no way to successfully (if reliable is part of success) use Access
as the back-end datastore over a WAN. Performance will usually be terrible
and corruption is always much more likely than in a LAN. Access is ok for
the frontend, but not the backend. So just creating a SQL Server backend at
least gets your reliability back, and might well improve performance even
without any other optimizations.

If you take the extra step of reviewing your existing forms and replacing
any existing record source like 'Select * From MyTable...' with an approach
that only retrieves a single row at a time, performance should be excellent
and reliability excellent. You can give the user a textbox to enter
something to retrieve a single row, like a client code, or whatever might be
unique and well-known by the user. Or you can have a dropdown list in the
form header, similar to finding a row in a table when the record source is
'Select * From', but use it to update the row source to get that one row.

 

[Tony Toews [MVP]]

If you take the extra step of reviewing your existing forms and replacing
any existing record source like 'Select * From MyTable...' with an approach
that only retrieves a single row at a time, performance should be excellent
and reliability excellent. You can give the user a textbox to enter
something to retrieve a single row, like a client code, or whatever might be
unique and well-known by the user. Or you can have a dropdown list in the
form header, similar to finding a row in a table when the record source is
'Select * From', but use it to update the row source to get that one row.

That's reasonable but you don't have to be a fanatic about it. Via an
ADSL connection and a VPN I just pulled in a transactions form with
the first 25 or so transactions displayed on the Access form in under
a second. There were at least five joins to other tables in that
query.

I then hit the filter form, increased the date range and viewed the
form. It displayed the first 25 transactions in under a second. When
I hit the last record navigation control on bottom of the form Access
displayed the 4701st transaction in under 4 seconds.

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 free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Armen Stein]

You will need to spend some time working with the FE and making it run
smoother as far as SQL Server goes. I would estimate the effort
involved to be about 5% to 10% of the initial system development time.

We don't really add much of a premium on development time using SQL
Server instead of Access for the back-end. Maybe 5%. But that's
because we've done it so many times, and we have techniques and
standards already established.

If you haul a lot of data over the wire (for example by opening
updatable forms to large recordsets), then using SQL Server over a WAN
might not be everything you wished for. But done correctly, it can
perform quite well.

I've 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 www.JStreetTech.com/Downloads. 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

 

[david]

There is no way to successfully (if reliable is part of success) use
Access as the back-end datastore over a WAN. Performance will usually be
terrible and corruption is always much more likely than in a LAN. Access
is ok for

That's sad to here. Using A97 we never had a WAN corruption
problem. 20 minute start up times and form loads, yes, but corruption no.

(david)

 

[Tony Toews [MVP]]

In my case, the forms deal with one record at a time. So SQL Server sound
like it might be a good option.

Could you explain, what needs to be changed in the front-end if I would
choose to make the switch.

Follow the links and read up on the material.

I understand I will migrate to an odbc connection, which is simple enough to
setup. What else?

Again follow the links.

You mention ADO, I use DAO, does this need to be changed...?

No, DAO works. You will need to add dbSeeChanges IIRC to the DAO
recordset lines. (Not sure why MS requires this but oh well.)

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 free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

No, DAO works.

DAO is the obvious choice because you're getting your data from Jet.
Yes, it's Jet managing an ODBC connection to another database
engine, but it's Jet that's doing all the management. Thus, DAO is
the correct choice.

You will need to add dbSeeChanges IIRC to the DAO
recordset lines. (Not sure why MS requires this but oh well.)

The nice thing is that VBA's error handler tells you this when you
try to open a recordset, so it's pretty easy to figure out that you
need it!

 

[Tony Toews [MVP]]

The nice thing is that VBA's error handler tells you this when you
try to open a recordset, so it's pretty easy to figure out that you
need it!

True enough.

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 free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/