The Old Joel on Software Forum: Part 4 (of 5)

Should I go with Vpn?

Hi Guys,
I have a vb program. And this will be stored on 21 pcs around the country, and all will connect simulatenous (at times) to an access db on a central server. All will be connected via a dialup internet connection. One of the programmers that I know suggested VPN or Lease line to solve the problem of concurrent connections.
Now I've done my google research, but I'm so confused as to how to go about it, and what are the typical rates for it?.
Can anyone help me out here, and point me in the right direction.
Thankyou in advance

Drake
Monday, February 9, 2004

VPN is just a Virtual Private Network. Has nothing to do with concurrency. It allows computers on various random networks (e.g. the internet) to act as if they're on a single private network.

mb
Monday, February 9, 2004

A VPN provides security on the wire for connections - which isn't your issue at all.

Lou
Monday, February 9, 2004

Unless your MDB is trivially small, Access should never be used over anything as slow as a dialup account, and you're likely to encounter problems with 21 simultaneous users as well. If it's centralized anyway, think MSDE at the very least, and SQL Server if you can afford it.

Sorry, no suggestions on the network side.

Sam Livingston-Gray
Monday, February 9, 2004

My opinion is you're probably going to run into insurmountable problems unless you developed your program specifically to be used over a dial-up connection.

And based on the suggestion that you could use Access -- a fileserver database -- over a dialup connection (whether vpn or otherwise), I'm assuming that the vb program wasn't developed that way. Even converting it to use SQL Server or some other true database server as a back end is almost certain to have poor performance unless the app was constructed to work well with limited bandwidth.

You might want to check out some of the n-tier technology at http://www.astatech.com . Their primary product is an n-tier component library for the Delphi programming language, but they do sell (I believe) an ODBC adapter that will let you connect to an Access database on an internet server, and which will sort of "transform" Access into a hybrid database server.

The Asta ODBC adapter does this by using a middleware "application server" which is actually where your client's Access query requests are routed, and the middleware gets the Access query results, accessing the .mdb locally on the server, and then sends the results back to the client's through the ODBC connection. It all works transparently and is pretty slick; I did a small demo myself using an Access front end to get at Access data on an internet server and it worked okay.

Your mileage may vary. In any case it's something to investigate now, or when you go further down the road and run into stumbling blocks trying to do what you want some other way. Using the Asta ODBC method would potentially require virtually no revisions to your vb program, I think.

You will likely have questions about it. Steve Garland is the head honcho and Asta and you can send him email with questions, or go to their newsgroups and post questions or review old posts.

Herbert Sitz
Monday, February 9, 2004

To echo what others have said, using VPN vs. other technologies to provide simultaneous access (npi) to your database is going to be the least of your problems if you use Access as your back end.

My suggestion is that you find a database professional who has done widely distributed applications, and follow his/her recommendations about program design, database to use, and connection technology.

Your current path - there lies madness.

Karl Perry
Monday, February 9, 2004

Drake -- I just looked at the Asta site and it looks like the ODBC driver could be tailor made for what you want. Here's what Asta has to say about its intended use:

"The ASTA ODBC Driver allows any Windows program capable of accessing ODBC to efficently run over the internet. Legacy applications built with VB or Access can just point to an AstaODBC Driver and then run over the internet. With the ASTA ODBC driver, you can use excel and get data from across the world very easily!"

You can download the evaluation version, I think, from here:
http://www.astatech.com/odbc/

You would also have to download the generic ADO or ODBC evaluation middleware server to install and run on the server against your .mdb, I believe.

This thing does really work, how well it works with your .mdb database would depend on things like whether your application is designed to minimize the size of recordsets being transferred (e.g., avoids things like a recordsource of 'select * from xxxxx'), whether it's designed to have low "chatter" between application and database (by, for example, being careful how you deal with combo box lookups, etc.).

I'd be curious to hear whether it turns out to be a solution for you. I first saw it a couple years ago and thought that it was a clever solution to a problem I didn't have. You've got the problem, so it may be just what you need.

Herbert Sitz
Monday, February 9, 2004

The last 3 versions of the office cd with ms-access have included a free 100% compatible desktop edition of sql.

As others have mentioned, you CAN NOT use the JET engine as a file share over the Internet. You are about 100 times TOO SLOW in terms of HIGH speed net, let alone dial up!

In other words, an increase of 2x, or even 3 times type of speed increase is not even close. You need many orders of magnitude more speed to use JET as a file share. You can
most certainly use JET with odbc...but not a file share.

I would suggest you consider Terminal Services and not even deploying any code to each workstation.

Anyway, you an read my article on using JET with a VPN, and some solutions are provided at the end of the article:

Using a wan with ms-access? How fast, how far?
By Albert D. Kallal
Saturday, August 09, 2003
Using a wan with ms-access. - a instant network primer.
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Monday, February 9, 2004

I seem to recall someone posting a very similar problem to this board many months ago, in some African or island nation (I can't remember which), and getting pretty much the same advice about Jet. Was that you, Drake?

Sam Livingston-Gray
Monday, February 9, 2004

It could have been me Sam, the problem is the project going on for this long.
The programmer we hired is giving us different opinions then the one expressed in these posts. I'm rattling my brain at what to do now.

Drake
Monday, February 9, 2004

Damn, Albert is probably right. Terminal Services (or Citrix) may be the best solution.

"You can
most certainly use JET with odbc...but not a file share."

Not sure what you mean, Albert. Any regular ODBC driver for Jet is going to be file-sharing. Even though it's ODBC you still have to have the Jet engine installed on the client (or else Jet engine is built into the ODBC driver, I'm not sure) and the data access is just to a file on a fileserver that doesn't need to have MDAC installed. Regular ODBC can't magically transform a Jet back end solution into a client-server relationship. ODBC drivers for Jet still access Jet as a filesharing database.

The Asta ODBC driver is different, though, since it mediates the connection and actually makes the connection from client to an Asta middleware application server, which then makes the ODBC connection to Jet locally. So the fact that it's filesharing doesn't hurt performance because middleware server doing the actual data access and the Jet backend .mdb are on the same machine.

Herbert Sitz
Tuesday, February 10, 2004

You'll rarely see me say this, as I like to give people the benefit of the doubt, but: perhaps you should consider changing programmers, or at least hiring another one as a translator.

Seriously, if you've been working with a programmer for this long and you still don't understand it, that programmer is not doing (his? her? I'll go with "their") job, part of which is to make sure you know exactly why you're giving them your money -- and it doesn't sound like they know what they're doing on the coding side, either. Access' performance -- or rather, its lack of performance -- over the network is... well, maybe it's not legendary, but it's certainly common knowledge in the MS developer community.

Sam Livingston-Gray
Tuesday, February 10, 2004

Oh, and Terminal Services would definitely be a good solution given the tools you have now.

Sam Livingston-Gray
Tuesday, February 10, 2004