Connect to Access Data from various regional locations

[Paul Proefrock]

We are a non-profit organization that keeps all our membership info in an
Access Database. We would like to be able to put the back-end(data) in a
centralized location and have various users connect, real-time, to update
their portion. Users are located in Florida, New York, Missouri and Nevada.

We do not have an organization owned server and we buy space for our hosted
website and email.

We don't want to go to Data Access Pages (certain access functions not
supported) or ASP, (Non of us are proficient in ASP), at least not yet. I
understand that Access only likes to connect to data when on a true network.

What various solutions may be available to us? VPN, hosted server, ? ? ?
Mind you, we are a non-profit so money/cost is an issue.

I would appreciate any ideas on how to accomplish this

Thanks

Paul P

 

[Sylvain Lafontaine]

Split your Access database into a Frontend (FE) with tables linked to a
Backend (BE) and use MSDE as the BE instead of Access. When using MSDE
instead of Access as the Backend, all you will have to will be to add the
parameter "dbSeeChanges" here and there. For example, instead of writing:

Dim rf As DAO.Recordset
Set rf = qf.OpenRecordset(DAO.dbOpenDynaset)

you will have to write:

Dim rf As DAO.Recordset
Set rf = qf.OpenRecordset(DAO.dbOpenDynaset, dbSeeChanges)

Finding where to put these changes will be easy because Access will generate
an error and tell you where the first time you will try to execute a piece
of code without this parameter.

There is a wizard to split your database into a FE and a BE; so except for
the small change of code above; there is nearly no work to do at all.

Finally, if you don't have a static IP address for the machine where the
MSDE database will be located, you may use a service like www.no-ip.com .
This will enable you to use nearly any machine as the database server;
without the need to buy space. The only required thing to do will be to
make that the port number 1433 isn't blocked by a firewall and correctly
forwarded if the machine is connected to the internet via a router instead
of having a direct connection. (Many providers will offer MSDE or
SQL-Server as part of their offering; however, many of them will also block
the port 1433 for a « better » security; so you cannot use them for this
kind of service.)

Total cost: 0$.

S. L.

 

[John Nurick]

Hi Paul,

It's not practical to use the ordinary front end mdb/back end mdb setup
over a wide area network, so one way or another you're in for
significant work and/or expense. This article by Albert Kallal explains
the issues and outlines the options:
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

 

[Jon Furman]

Sylvian's solution will work (I've done the same thing myself it worked
marvelously but we were all on broadband I suspect that your mileage may
vary if you have dialup users), if you're up to changing the data storage
from Jet to SQL server. One thing that I don't think that has been mentioned
yet is terminal services. Let your remote users log into the network
containing the Access database application, probably through VPN (which
isn't too hard to get working) and them allow them terminal access to the
server..then they should be able to use the Access application as they are
used to through the TS window. That's probably the least hassle from the not
having to reprogram or change platforms perspective but it's a big hassle
from a networking perspective. I guess you get to choose your evil.

 

[Paul Proefrock]

Sylvain,
Thanks for your solution, looks like this is the answer we were looking for.
Do you mind a couple questions:

1) Our plan would be to put the data files in a ftp folder on a web host.
That keeps them off the internet yet still accessible or would this not
work?

2) I will query about the port 1433.

3) is the dbSeeChanges associated with any other command other than
OpenRecordset? Can I shorten the broken links by searching for the spots
that will require the additional setting?

4) All users will be connecting via broadband. What can we expect for
performance issues from those who do a lot of data input who are used to
having the data actually on their machine? Will the page refresh or data
input slow down appreciably?

Are there any issues or items I should be aware of or search out as I work
on this transition?

Thanks for your help

Paul P

 

[Paul Proefrock]

Jon,
Our host doesn't like VPN - says it is too much of a security nightmare. I
don't agree but I don't run a big web hosting company either.

Those who have to use dial-up will be minimal and will be making only minor
changes so it should not be an issue.

Thanks for your input

Paul

 

[Jon Furman]

Do what Sylvain said then and go SQL server with the back end. Of course, in
a way, you're just trading the network security issue for a SQL server
security issue, but what ever gets around the overly protective sysadmins is
what you need.

As a side note..I really wish there was a way of making things secure that
didn't involve shutting things down or turning them off. In both of the
corporate jobs I've had in the past 5 years the security was so tight that
the desktop and network computers were about as useful as paperweights. I
always thought that the best way to achieve security was to educate/train
users and not shut everything down, that approach is kind of like an army
were all of the gun barrels have been filled with cement to reduce friendly
fire accidents. Just thought I would mention that to the world here in the
Access newsgroup were all the worlds CIO's get all their actionable
information! Haha!

Jon Furman

 

[Sylvain Lafontaine]

An FTP folder? I'm not sure of fully understanding what you would have
wanted to achieve with this; unless each user would his own copy of the mdb
files.

dbSeeChanges is also required for some .Execute command.

The performance can be very good (and in fact practically as fast as local
database on some occasion) but may require using Views for queries using
join. In the later case, you will have to look at the following article to
make them updatable: http://support.microsoft.com/?kbid=209123 .

Using databases over the Internet is a complex thing; you will have to learn
your lessons.

S. L.

 

[Paul Proefrock]

Sylvain,
Learning, slowly but surely.

Is it necessary that the host server the BE resides on support SQL or is it
just necessary that we have a folder that we can put the BE in and that
folder be accessible with Port 1433?

We have a host who supports MySQL but not SQL, says the license is too
expensive. They say port 1433 is only for SQL. It might be a losing
arguement but I don't want to spend a lot of time setting it up to find out
it won't work. They want their hosting money up front and you know what it
is like to get refunds ;-(

Is there a specific knowledgebase article that references this MDSE/Access
procedure or did you sort it out by yourself?

Thanks

Paul P

 

[Sylvain Lafontaine]

SQL-Server is expensive but MSDE is free. You can install the later at
home and play with it some time before you commit yourself to spend money.
In fact, by using a service like www.no-ip.com; you can be your own host.

There is plenty of references/books on the subject but my best advice would
be that you ask someone who know about that to make the installation for
you.

The next version of MSDE will be called SQL-Server 2005 Express (here, the
word Express mean Free) and a Beta-2 version is already available at
msdn.microsoft.com/sqlserver (look for the label 2005; I can't give you the
exact reference right now because my DNS server is presently down). The
current version is the October CTP Edition (for "Community Technology
Preview") but the next version, probably called the December CTP Edition,
should be out in a few days.

S. L.

 

[Guest]

Interesting post. I have the same situation - work in a school through a
non-profit. Have designed an Access 2003 database to track client records.
Have staff at 2 other offices. I understand how to split the database, but
how do I "use MSDE as the BE instead of Access." How do I do that? I'm not
clear how to deploy to other users and where to set table links to. Please
explain next steps after splitting. Thanks!

 

[Sylvain Lafontaine]

Hi,

When you split a database, you have the choice of splitting into two MDB
files or into a MDB file as the frontend and the MSDE (or SQL-Server) as the
backend. The second option is called Upsizing, so you must use the Upsizing
wizard to perform it. In the past, if I remember correctly (but I may be
wrong), the same wizard was used for both of these tasks, hence my poor
choice of words.

The upsizing wizard give also you the possibility of creating an Access Data
Project (ADE file) instead of a MDB with linked tables but you are better
starting with the MDB file than with the ADP (much more easier).

MSDE is the free version of SQL-Server and it come with Access or can be
downloaded free of charge from the Microsoft web site.

The only problem you may have is a performance problem when joining linked
tables; as this involve a lot of network communication. However, a subtle
use of views here and there can alleviate most of these problems. Here an
article about setting a view into an updatable view under Access:
http://support.microsoft.com/kb/q209123/

Finally, before performing these steps, make sure that you can access the
port 1433 from the other offices. There is a trend in blocking about
everything these days from the various administrators, because of "security
concern".

S. L.

 

[alexandre LERMANT]

Le 21/12/04 21:11, dans (e-mail address removed), « Sylvain

Hi,

When you split a database, you have the choice of splitting into two MDB
files or into a MDB file as the frontend and the MSDE (or SQL-Server) as the
backend. The second option is called Upsizing, so you must use the Upsizing
wizard to perform it. In the past, if I remember correctly (but I may be
wrong), the same wizard was used for both of these tasks, hence my poor
choice of words.

The upsizing wizard give also you the possibility of creating an Access Data
Project (ADE file) instead of a MDB with linked tables but you are better
starting with the MDB file than with the ADP (much more easier).

MSDE is the free version of SQL-Server and it come with Access or can be
downloaded free of charge from the Microsoft web site.

The only problem you may have is a performance problem when joining linked
tables; as this involve a lot of network communication. However, a subtle
use of views here and there can alleviate most of these problems. Here an
article about setting a view into an updatable view under Access:
http://support.microsoft.com/kb/q209123/

Finally, before performing these steps, make sure that you can access the
port 1433 from the other offices. There is a trend in blocking about
everything these days from the various administrators, because of "security
concern".

S. L.

Nulos!!!!!!!!!!!!