Cannot Open Access over VPN: very strange behavior

  • Thread starter Thread starter opensource71
  • Start date Start date
O

opensource71

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
 
Did you have split the database into a FrontEnd (FE) and a BackEnd and made
a personal copy of the FE for each user?
 
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
 
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:
 
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.
 
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
 
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
 
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
 
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?
 
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
 
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.)
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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/AlbertKallal/Articles/split/index.htm
 
Douglas,

I can confirm you that there is not permission related problem.
You can check my reply to Albert.
Click to expand...

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...
 
Hi all and thanks to everyone for the help.
I can confirm you that for testing purposes I have:

1) given an full access to the Everyone group on the folder where
Access and other Words files reside (I repeat that Word files can be
opened normally by VPN users)
2) I have tried to log in by VPN with a user to whom I had assigned
full Administrator Domain rights: he has the same problem opening the
Access file as any other VPN user

Thanks again for your follow-up on this thread
Opensource71
 
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
Click to expand...

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.
 
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
 
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)
 
Back
Top