Performance of MSDE over VPN

[Morning DJ]

I have read many posts which say putting a back end mdb on a wan will
make for some sorry performance for those not local to the machine
with the mdb. Is the same true if the back end is an MSDE database?

If I could find an ISP that would be willing to run a VPN through to a
computer that had MSDE installed and would let me put a database on
that computer, would a local version of Access be able to access the
database on that computer without too much suffering?

DJ

[Albert D. Kallal]

The answer is most certainly yes, but with a BIG IF.

That big if is that you been running, and designing and developing your
application to work with sql server. So, you have already developed many
ms-access applications with sql server, have a good working knowledge of sql
server. Thus, you learned over time what works well with ms-access + sql
server, and what does not work well. So, this "if" also assumes you have a
first rate design, and don't do brain dead things like opening up a form to
a large table without any kinds of restrictions (of course, opening up a
form attached to a large table without a "where" clause is a dumb thing even
when NOT using sql server!).

There is a number of other solutions you can use and I talk about using a
wan with ms-access here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal

[Morning DJ]

On Thu, 03 Feb 2005 00:17:49 GMT, "Albert D. Kallal" <ka****@msn.com >
wrote:

The answer is most certainly yes, but with a BIG IF.

That big if is that you been running, and designing and developing your
application to work with sql server. So, you have already developed many
ms-access applications with sql server, have a good working knowledge of sql
server. Thus, you learned over time what works well with ms-access + sql
server, and what does not work well. So, this "if" also assumes you have a
first rate design, and don't do brain dead things like opening up a form to
a large table without any kinds of restrictions (of course, opening up a
form attached to a large table without a "where" clause is a dumb thing even
when NOT using sql server!).

There is a number of other solutions you can use and I talk about using a
wan with ms-access here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Thanks, Albert. I appreciate the education.

Is it a fair statement that an access front-end that is primarily
based on unbound forms (although not exclusively), with bound queries
that populate low row-count datasources and rowsources and with all
other queries being similarly limited to low row-counts will function
"ok"?

I am only thinking of having 2 or 3 sites have remote access to the
backend, so I'm hopeful that the throttling built in to MSDE won't
cause a problem.

Thanks again.

DJ

[Albert D. Kallal]

"Morning DJ" <ac*********@ra dio.com.invalid > wrote in message
news:42******** ********@news.I NDIVIDUAL.NET.. .

Is it a fair statement that an access front-end that is primarily
based on unbound forms (although not exclusively), with bound queries
that populate low row-count datasources and rowsources and with all
other queries being similarly limited to low row-counts will function
"ok"?

I am only thinking of having 2 or 3 sites have remote access to the
backend, so I'm hopeful that the throttling built in to MSDE won't
cause a problem.

Yes. I agree with the above. In fact, if the forms are simple, then even
bound forms just fine....just use the "where" clause to open a form to the
one record works quite well. On the other hand, if the forms are
simple...you might consider a web based solution.

Also, in the case of the MSDE, I would assume that you are hosting your own
server. If not, then likely, the provider will supply/allow you to use sql
server.

if you are using your own machine with a web server, and don't want any
throttle, then using the new free version of sql server with no throttle
would be the ticket. It can be found here:

http://lab.msdn.microsoft.com/express/sql/default.aspx

about this product can be found here:
http://www.microsoft.com/sql/express/

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal

[David W. Fenton]

ac*********@rad io.com.invalid (Morning DJ) wrote in
news:42******** ********@news.I NDIVIDUAL.NET:

If I could find an ISP that would be willing to run a VPN through
to a computer

Your ISP won't know anything about the VPN.

Nor should they -- that's why it's a Virtual PRIVATE Network.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Morning DJ]

On Thu, 03 Feb 2005 03:49:05 GMT, "Albert D. Kallal" <ka****@msn.com >
wrote:

"Morning DJ" <ac*********@ra dio.com.invalid > wrote in message
news:42******* *********@news. INDIVIDUAL.NET. ..

Is it a fair statement that an access front-end that is primarily
based on unbound forms (although not exclusively), with bound queries
that populate low row-count datasources and rowsources and with all
other queries being similarly limited to low row-counts will function
"ok"?

I am only thinking of having 2 or 3 sites have remote access to the
backend, so I'm hopeful that the throttling built in to MSDE won't
cause a problem.

Yes. I agree with the above. In fact, if the forms are simple, then even
bound forms just fine....just use the "where" clause to open a form to the
one record works quite well. On the other hand, if the forms are
simple...you might consider a web based solution.

Also, in the case of the MSDE, I would assume that you are hosting your own
server. If not, then likely, the provider will supply/allow you to use sql
server.

if you are using your own machine with a web server, and don't want any
throttle, then using the new free version of sql server with no throttle
would be the ticket. It can be found here:

http://lab.msdn.microsoft.com/express/sql/default.aspx

about this product can be found here:
http://www.microsoft.com/sql/express/

Thanks, again.

My thought was to take a run-of-the-mill W2k or XP machine and plug it
into a VPN-capable router and then plug that router into my ISP's rack
so it will be visible on the web.

Sounds wacky, I know, but I think I know somebody who knows somebody
who told me they *think* their boss will let me do this.

I am thinking about doing it this way because, frankly, I have no
knowledge of any server software (like Small Business Server). So, no
IIS or similar setup for me.

That doesn't mean I can't use the express version of SQL, though, to
avoid the throttle. That will run on a regular machine, just as well
as a server.

DJ

[Morning DJ]

On Fri, 04 Feb 2005 01:33:29 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

ac*********@ra dio.com.invalid (Morning DJ) wrote in
news:42******* *********@news. INDIVIDUAL.NET:

If I could find an ISP that would be willing to run a VPN through
to a computer

Your ISP won't know anything about the VPN.

Nor should they -- that's why it's a Virtual PRIVATE Network.

I don't think I explained this very well, so I'll try again.

I could put the MSDE (or Express 2005) database on a machine in my
offices and connect it to the internet on its own static IP if I
wanted.

I don't want to for a couple of reasons:

1) I don't want to be responsible for seeing that it is getting power
24/7.

2) The pipe to the internet is significantly less (for the same cost)
than what the ISP is willing to provide. I'm looking at 1.5mbps both
up and down. In my neck of the woods to get that through Covad or
some other business class ISP would cost about two hundred dollars
more per month than what my ISP is willing to charge me if I take my
hardware to them.

But I still need the VPN because I know nothing about servers. I
know that I'll need a router that restricts all ports except those
needed for the VPN and I know that I'll have to use, at the least, a
set of strong passwords to avoid compromising the VPN.

Hope that makes it clearer.

DJ

[Trevor Best]

David W. Fenton wrote:

ac*********@rad io.com.invalid (Morning DJ) wrote in
news:42******** ********@news.I NDIVIDUAL.NET:

If I could find an ISP that would be willing to run a VPN through
to a computer

Your ISP won't know anything about the VPN.

Nor should they -- that's why it's a Virtual PRIVATE Network.

Actually, it's called an IP Tunnel, VPN is a term of endearment.

--
This sig left intentionally blank

[Morning DJ]

On Fri, 04 Feb 2005 06:04:15 GMT, ac*********@rad io.com.invalid
(Morning DJ) wrote:

On Thu, 03 Feb 2005 03:49:05 GMT, "Albert D. Kallal" <ka****@msn.com >
wrote:

"Morning DJ" <ac*********@ra dio.com.invalid > wrote in message
news:42****** **********@news .INDIVIDUAL.NET ...

Is it a fair statement that an access front-end that is primarily
based on unbound forms (although not exclusively), with bound queries
that populate low row-count datasources and rowsources and with all
other queries being similarly limited to low row-counts will function
"ok"?

I am thinking about doing it this way because, frankly, I have no
knowledge of any server software (like Small Business Server). So, no
IIS or similar setup for me.

That doesn't mean I can't use the express version of SQL, though, to
avoid the throttle. That will run on a regular machine, just as well
as a server.

Thanks to all.

It absolutely FLIES. Hundreds of thousands of rows in the table and a
rowsource that results in a few dozen rows, if selecting on a WHERE
clause that can access the defined indices, and it returns the results
INSTANTLY.

I am in heaven.

Think I'll try it on a dial-up just to see how well it really works.
<g,d & r>

DJ

[Tony Toews]

ac*********@rad io.com.invalid (Morning DJ) wrote:

It absolutely FLIES. Hundreds of thousands of rows in the table and a
rowsource that results in a few dozen rows, if selecting on a WHERE
clause that can access the defined indices, and it returns the results
INSTANTLY.

I am in heaven.

Think I'll try it on a dial-up just to see how well it really works.
<g,d & r>

FWIW a fellow MVP found performance over a dial-up acceptable.

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