MS Access and Internet - Cannot Open Access over VPN: very strange behavior

Author

Cannot Open Access over VPN: very strange behavior

opensource71@yahoo.com

2005-12-03, 9:24 am

Hi all,

I face a very strange problem, when opening a MS Access database over a
VPN tunnel.
The database reacts weirdly to users' opening requests:

1)-Users who are on the Internal LAN can open the database.
2)-VPN users cannot access the database, if the database has already
been opened on the LAN
3)-VPN users can open instead the database, if the database has not
been opened by any client on the LAN

VPN characteristics:
-a Ms 2000 VPN server allows access to remote users to LAN resources: I
use a simple PPTP VPN connection.
(I have not tested a L2TP/IPSec VPN yet, for it would entail the
establishment of a CA and the distribution of Certificates to all
clients (what I would avoid if possible) ).

I need to solve this problem somehow: any help to this regard will be
appreciated.

Thank you very much in advance
Opensource71

Sylvain Lafontaine

2005-12-03, 1:24 pm

Did you have split the database into a FrontEnd (FE) and a BackEnd and made
a personal copy of the FE for each user?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

<opensource71@yahoo.com> wrote in message
news:1133619456.435438.21700@g49g2000cwa.googlegroups.com...
> Hi all,
>
> I face a very strange problem, when opening a MS Access database over a
> VPN tunnel.
> The database reacts weirdly to users' opening requests:
>
> 1)-Users who are on the Internal LAN can open the database.
> 2)-VPN users cannot access the database, if the database has already
> been opened on the LAN
> 3)-VPN users can open instead the database, if the database has not
> been opened by any client on the LAN
>
> VPN characteristics:
> -a Ms 2000 VPN server allows access to remote users to LAN resources: I
> use a simple PPTP VPN connection.
> (I have not tested a L2TP/IPSec VPN yet, for it would entail the
> establishment of a CA and the distribution of Certificates to all
> clients (what I would avoid if possible) ).
>
> I need to solve this problem somehow: any help to this regard will be
> appreciated.
>
> Thank you very much in advance
> Opensource71
>

Albert D.Kallal

2005-12-03, 1:24 pm

My bets are that the VPN users don't have FULL rights to the directory.

You need full rights (if ms-access can't delete, and create the locking
file..it opens it in single user mode). So, again, full rights means create,
and delete rights.

Further, I don't see how you are going to get any kind of useable
performance here. Read the following about using ms-access on a VPN

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

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

opensource71@yahoo.com

2005-12-04, 7:23 am

Hi Sylvain.

I know that is a possible solution, but I would avoid it if I can.
In fact, if I do that I will then have to cosider a way for
distributing the FE to each user and make sure that each user gets an
updated copy, if changes are made to the back end.

Do you think there is any solution at the FE distribution problem?
Can it be somehow ''centralised'' ?
Opensource71

Sylvain Lafontaine (fill the blanks, no spam please) schreef:
[color=darkred]
> Did you have split the database into a FrontEnd (FE) and a BackEnd and made
> a personal copy of the FE for each user?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> <opensource71@yahoo.com> wrote in message
> news:1133619456.435438.21700@g49g2000cwa.googlegroups.com...
[color=darkred]

Douglas J. Steele

2005-12-04, 7:23 am

Don't treat splitting as a last ditch attempt. Multiple users sharing the
same application increases the risk of corruption significantly, and using
Access over VPN isn't the safest setup in the first place, as Access is very
succeptible to network problems.

You can probably use Tony Toews' Auto FE Updater
http://www.granite.ab.ca/access/autofe.htm to ensure that each user has the
most recent copy of the FE.

However, as I believe was said elsewhere in this thread, the problems you
outline sound permissions-related.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<opensource71@yahoo.com> wrote in message
news:1133693503.448069.224650@z14g2000cwz.googlegroups.com...
> Hi Sylvain.
>
> I know that is a possible solution, but I would avoid it if I can.
> In fact, if I do that I will then have to cosider a way for
> distributing the FE to each user and make sure that each user gets an
> updated copy, if changes are made to the back end.
>
> Do you think there is any solution at the FE distribution problem?
> Can it be somehow ''centralised'' ?
> Opensource71
>
> Sylvain Lafontaine (fill the blanks, no spam please) schreef:
>
>
>

opensource71@yahoo.com

2005-12-04, 7:23 am

Hi Albert,

Thanks for your answer my post.
The VPN users have FULL rights to the directory. When connected by VPN
they can open any other type of files stored in the shared folder,
including the Access database if nobody on the LAN has opened it
before.

I read the article you linked and it was very interesting.
Unfortunately, I still need to let VPN users access the database.
Can this be done in a safe way (i.e. without corrupting the database),
by using the Microsoft Data Engine instead of the JET?
Thanks a lot for your follow-up
Opensource71

opensource71@yahoo.com

2005-12-04, 7:23 am

opensource71@yahoo.com

2005-12-04, 7:23 am

Douglas,

I can confirm you that there is not permission related problem.
You can check my reply to Albert.
Thank you for the Auto FE Updater link. I will seriously look into it.

I understand that Access is susceptible to VPN & network problem.
But is it still possible to open an Access database by VPN when someone
else is already using it on the LAN?

Thank you for your follow-up
Opensource71

Douglas J. Steele

2005-12-04, 7:23 am

I can't think of any other reason why you shouldn't be able to open an
Access database by VPN when someone else is already using it on the LAN?

To be honest, my experience with setting up VPN is almost nonexistant. Is
there somewhere that you can set permissions for VPN, the equivalent of how
you can set permissions separately for a Share and for the folders/files on
the drive?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<opensource71@yahoo.com> wrote in message
news:1133699856.284825.247490@o13g2000cwo.googlegroups.com...
> Douglas,
>
> I can confirm you that there is not permission related problem.
> You can check my reply to Albert.
> Thank you for the Auto FE Updater link. I will seriously look into it.
>
> I understand that Access is susceptible to VPN & network problem.
> But is it still possible to open an Access database by VPN when someone
> else is already using it on the LAN?
>
> Thank you for your follow-up
> Opensource71
>

opensource71@yahoo.com

2005-12-04, 1:24 pm

Douglas,

I am sure the VPN and the NTFS permissions are set correctly.

This strange behavior ONLY appears with Access (other files respond
normally to VPN users), when someone on the LAN is already working on
the file.

It must be an Access problem, not a VPN or a file permision one.
Do you know any resource, which could help me in finding out why Access
reacts this way on VPN?
Opensource71

Sylvain Lafontaine

2005-12-04, 8:24 pm

The only sure method of knowing that this is not a permission problem is to
make the VPN group a member (temporarily, of course) of the Administrators
group.

I recall having some kind of permission problems with IIS, a VPN and the
Temp or Tmp folder as set in the configuration settings but this was three
years ago and I don't remember the exact details.

Finally, even if you solve your (permission) problem, you still have to
split your database into a FE and a BE; otherwise you may end up with
endless corruption problems. (In fact, even with a split database, you may
still end up with corruption problems if you go over a VPN over the WAN.
The (lack of) speed might also become a problem in its own. The only
realistic solution in your case would be to use Citrix/TS or to go with
SQL-Server/MSDE, linked tables and a lot, lot, lot of Views. See
http://support.microsoft.com/kb/q209123/ for updatable views.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

<opensource71@yahoo.com> wrote in message
news:1133723451.709092.86360@o13g2000cwo.googlegroups.com...
> Douglas,
>
> I am sure the VPN and the NTFS permissions are set correctly.
>
> This strange behavior ONLY appears with Access (other files respond
> normally to VPN users), when someone on the LAN is already working on
> the file.
>
> It must be an Access problem, not a VPN or a file permision one.
> Do you know any resource, which could help me in finding out why Access
> reacts this way on VPN?
> Opensource71
>

Albert D.Kallal

2005-12-04, 8:24 pm

> The VPN users have FULL rights to the directory. When connected by VPN
> they can open any other type of files stored in the shared folder,
> including the Access database if nobody on the LAN has opened it
> before.

That still does not tell me that you have full rights. Remember, ms-access
CREATES a locking file (ldb) in that directory, and this file is used for
multi-users collisions. If those locking files cannot be created, then
ms-access opens in single user mode. Your above statement says that files
can
be opened, but says NOTHING ABOUT PERMISSIONS, AND PROVES NOTHING about
PERMISSIONS. The fact that people can open these files does NOT means that
they have full rights to those directory,a nd that include file create, and
file delete rights WHICH THEY NEED....else ms-access opens in single user
mode. So the fact that users can open those files does NOT mean they have
full create and delete rights to the directory where that file resides. I
going
to say this again, and in plain English with no "ifs" , "buts" , or "ors".

Users need FULL permissions to the direclty where those files reside, and
this includes file create, and file delete rights. If they do not have these
rights (in addition to full read/write permissions on the files), then
ms-access opens in single user mode.

>
> I read the article you linked and it was very interesting.
> Unfortunately, I still need to let VPN users access the database.

Well, you still have not solved the issue of haveing 100 times less
performance here..have you?

> Can this be done in a safe way (i.e. without corrupting the database),
> by using the Microsoft Data Engine instead of the JET?

Yes, using sql server is good solution. The MSDE, or the so called free
desktop edition of sql server will solve this problem of corruptions.

However, you STILL need to install the mdb file on each computer (in fact,
this should be a mde file). In other words, you still can't allow multiple
users into the same front end here. So, we assume you have split this
database, and if you choose to use sql server for the back end, then you do
eliminate the issue of corruption. And, you can use any server based
application
for the server side...Oracle...MSDE....sql-server etc....

Please read the following article as to why you split *just* in case there
is some confusing here as to what a file is, and what a application is.

http://www.members.shaw.ca/AlbertKa...split/index.htm

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

Albert D.Kallal

2005-12-04, 8:24 pm

<opensource71@yahoo.com> wrote in message
news:1133699856.284825.247490@o13g2000cwo.googlegroups.com...
> Douglas,
>
> I can confirm you that there is not permission related problem.
> You can check my reply to Albert.

No, you have confirmed that users have full permissions to the files, but
you have NOT confirmed that users have full rights to the directory where
the mdb file resides..and the users need full create, and full delete
rights...

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

Joan Wild

2005-12-04, 8:24 pm

Ensure that *all* users (not just the VPN users) have full permissions, too.

--
Joan Wild
Microsoft Access MVP

Albert D.Kallal wrote:
> <opensource71@yahoo.com> wrote in message
> news:1133699856.284825.247490@o13g2000cwo.googlegroups.com...
>
> No, you have confirmed that users have full permissions to the files,
> but you have NOT confirmed that users have full rights to the
> directory where the mdb file resides..and the users need full create,
> and full delete rights...
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@
msn.com
> http://www.members.shaw.ca/AlbertKallal

John Mishefske

2005-12-06, 3:24 am

opensource71@yahoo.com wrote:
> Hi Albert,
>
> Thanks for your answer my post.
> The VPN users have FULL rights to the directory. When connected by VPN
> they can open any other type of files stored in the shared folder,
> including the Access database if nobody on the LAN has opened it
> before.
>
> I read the article you linked and it was very interesting.
> Unfortunately, I still need to let VPN users access the database.
> Can this be done in a safe way (i.e. without corrupting the database),
> by using the Microsoft Data Engine instead of the JET?
> Thanks a lot for your follow-up
> Opensource71
>

What version of Access? Access 2000? If so read on....

Are you doing a compact on close perhaps or through some other method that requests Access
to do the compact?

Access 2000 is the only version that does not copy the database to another name in the
application directory prior to compacting - it copies to the defined TEMP directory.

This behavior may cause the compacted .mdb to inherit the permissions of that TEMP
directory and once copied back to the original name maintains those updated permissions
from the TEMP directory.

A long shot; I've seen this only in A2K on Windows XP boxes that host the FE .mdb
and use the Access compact feature.

--
'---------------
'John Mishefske
'---------------

opensource71@yahoo.com

2005-12-09, 11:24 am

Thanks everyone for your posting!

The version of Access I have been using is the 2000.
I will follow the split database suggestion in order to avoid further
complications.
I really do not feel like starting from scratch again and resetting all
my Servers/Users/Folder Permissions, in order to see if there is a
conflict somewhere I have missed, which may prevent VPN users to access
the mdb database.

I want to really thank everyone for the invaluable contribution and
help, you have given to me and anyone else reading this posting in the
future.
I really appreciated your time and effort: this is newsgroups' power at
his best!

Opensource71

david epsom dot com dot au

2005-12-15, 8:26 pm

> It must be an Access problem, not a VPN or a file permision

Access uses the native database primitives of the file
system. An Access problem IS a file permission problem.
A file permission problem IS an Access problem.

Access and the file system are the same thing. The
file system is part of Access. Access is part of the
file system.

The minimum permissions required are also required by
other file based systems (like Word), but Access is
integrated into the file system more fully than other
applications, and also requires more and different
permissions than other applications.

The problem you describe occurs when a user cannot
share a copy of an .LDB file created by a different
user.

It is definitely an Access problem. That means that it
is a VPN or file permission problem.

PS, I have trimmed the references to Replication and
ModulesDAOVBA. I'm sure they aren't interested.

(david)

<opensource71@yahoo.com> wrote in message
news:1133723451.709092.86360@o13g2000cwo.googlegroups.com...
> Douglas,
>
> I am sure the VPN and the NTFS permissions are set correctly.
>
> This strange behavior ONLY appears with Access (other files respond
> normally to VPN users), when someone on the LAN is already working on
> the file.
>
> It must be an Access problem, not a VPN or a file permision one.
> Do you know any resource, which could help me in finding out why Access
> reacts this way on VPN?
> Opensource71
>

Sponsored Links