Sharing an Access File on a Small Network

[JDJones]

I've created an Access Database (2003 Version) for our small office - saved
as Version 2000 to accommodate the other two users. It works fine on the
individual PCs (C Drive), but on the network (Pier to Pier - Microsoft),
we're having problems. The way it's set up - one user's PC acts as the
"server" and the file resides on the C Drive. The rest of us try to access
it through My Network Places and click on the file. Sometimes we get in -
sometimes we don't. The server PC is on. Access resides on all three PCs.
Is it possible that using XP Home Edition is causing a problem? Do we all
need to be XP Professional? Once we're all in, it seems to work fine. It
tells us to check the path - that it can't find the file. Does Access need a
mapped drive?

 

[Wayne Morgan]

XP Home should be ok. NO, don't use a mapped drive. To use a shared file,
you should split the file into a front-end and back-end. The back-end will
be just the data tables. The front-end will be the rest of the file and a
copy should be placed on each user's computer. Create linked tables in the
front-end file that point to the back-end file. The users will all need full
permissions on the directory/share that the files are in.

For more information, see these links.
http://www.granite.ab.ca/access/splitapp/index.htm
http://www.granite.ab.ca/access/bloatfe.htm
http://www.granite.ab.ca/access/backendupdate.htm
http://www.mvps.org/access/tables/tbl0009.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;207793
http://support.microsoft.com/default.aspx?scid=kb;en-us;305542
http://support.microsoft.com/default.aspx?scid=kb;en-us;235961

 

[JDJones]

This sorta helps. I thought about splitting the database. But if I click on
the folder it's in on the host computer and see the file - why won't it open
it? I can put other types of files in the same folder (Word for example) and
they open just fine. Is it possible that because we're on a wireless network
that radio transmissions in the neighborhood could interfer with opening a
large file? We are very close to an Office Depot, and one day last week, we
actually saw their network. Evidently not encrypted. I'm just wondering if
something else is going on. But I will split the database to see if this is
part of the problem. Also - is it possible my Norton firewall is preventing
something?

 

[Wayne Morgan]

Could you have outside interference? Possible. Could your firewall be
interfering? Possible, but if you can copy the file to your computer, then
for the firewall to be blocking it, it would have to be blocking the Access
application from accessing the network.

Wireless networks aren't good for Access because of their tendency to
fluctuate and sometimes completely drop out. This can cause file corruption.
Also, make sure each user has their default open mode in Access set to
Shared (Tools|Options|Advanced tab) and that they aren't doing anything else
to force and Exclusive open of the file.

 

[Albert D.Kallal]

I explain without technical terms why you should split here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

However, you can't use ms-access on a wireless network. I explain how to
display ms-access on a Lan, OR WAN here:

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

In the above, I mention why you can't use a wireless network.

 

[JDJones]

I tried to copy the file earlier back to my computer from the network, and it
would not let me copy it - despite the primary user being out of it. We had
to go back to the PC it resides on to copy it out to another folder. Then I
could pick it up to copy.

Last night, we put in cable to network two of the PCs without wireless.
Same problem.

I've checked the Exclusive/Shared thing. It's all set to Shared. But for
some reason, on the PC the file resides on - the file thinks it's in Exlusive
mode. The first error is always - can't find the path. Then it says it's
opened either exclusive or I don't have rights to it. I did not set any
security on this file.

I've copied it to a '97 format - and it opens. (The swithboard isn't too
happy). I copied the Northwind database out to the same shared folder and we
can all open it. I'm going crazy! For some reason, when it was first copied
out, it worked. After a couple of days it did not work anymore. So it will
be interesting to see if the 97 format or Northwind open after a couple of
days. It's like the longer it sits there, it tries to hide.

 

[JDJones]

I will read this in a minute. Thank you so much. See my response above.

 

[Wayne Morgan]

If you can't copy the file either, then you have a network permissions
problem, not an Access problem. It could be the permissions on the network
share or the NTFS permissions on the folder. If the client computer can't
access the file then Access can't either.

 

[JDJones]

That's my thought as well. We had a network guy out here who had us all
communicating for awhile the other day. But about an hour later, it was
gone. My gut says it's the network. However, if I can view the contents of
the folder and open a Word document, doesn't it make sense I can also open an
Access file?

 

[JDJones]

I got a chance to read the technical info you referred me to. You gave the
BEST explanation I've ever heard for splitting a database. I took a course a
few months back and asked that very question, and the instructor said it
wasn't necessary and that he never split databases. I have always split any
database requiring multiple users, but had not done so yet on this one
because of the problem simply accessing it at all! I didn't want to have to
keep moving around a split database if there was a problem with the network.
I will check out your website more often. You have a way of explaining
things in such a common sense way. Thank you.

 

[Wayne Morgan]

Yes and no. I have seen network transfers fail after a few bytes. The larger
the transfer, the more problems that occurred. It still comes back to the
fact that if you can't copy the file, Access can't open the file. You have a
network problem somewhere.

 

[JDJones]

I had our network guy create a "shared documents" folder on the root of the
network and copied the file there. I've been suspecting that users are
denied access at some sub-level. So far, we can get in. However, it's done
this before (allowed access for awhile - then denied). However, this was the
first I insisted on being at the root (or highest level) of the network.
I'll know if I can still get in on Monday or Tuesday. If I can - I'll split
the db.

 

[JDJones]

Problem solved! The server in this case is actually someone's computer. We
set up shared drives - but nothing worked. Then we used the shared folder
that comes on XP and made it a network drive - everything works. Which
brings me to another question - what's different about the Microsoft shared
folder and a new folder that was set up as both a network and shared folder?
It seems they should be the same. Thanks so much for your help.

 

[Wayne Morgan]

Without knowing how you set it up, I don't know. Between share permissions
and NTFS permissions, it could be any one of a number of things. But, it
would appear that one of them was set incorrectly for what you were trying
to do since it is now working.

 

[TraceyH]

There is another way to get rid of the annoying message without having to set
your security to low. You can digitally sign the project. Look for a program
called "selfcert.exe" on your XP machine to create your own digital
signature. Then refer to article 832510 in the microsoft KB. Once you have
completed that process, you can set your macro security back to medium and
that message should go away.

Tracey Hughes
www.traceyhughesconsulting.com

 

[JDJones]

Thank you!! I knew there had to be a way!!

There is another way to get rid of the annoying message without having to set
your security to low. You can digitally sign the project. Look for a program
called "selfcert.exe" on your XP machine to create your own digital
signature. Then refer to article 832510 in the microsoft KB. Once you have
completed that process, you can set your macro security back to medium and
that message should go away.

Tracey Hughes
www.traceyhughesconsulting.com

 

[BillD]

Albert:
Your article on database splitting was excellent. I am just starting to
deploy split databases for small office networking (say 3-5 users). I did not
know that when linking _fe to _be tables that you should use network
neighbourhood.

Thanks for the great tip. This will save me problems in the future.

Bill D

 

[Armen Stein]

I am just starting to
deploy split databases for small office networking (say 3-5 users). I did not
know that when linking _fe to _be tables that you should use network
neighbourhood.

Hi Bill,

You'll need to keep those tables linked. If you want an automated
approach to relinking tables, there are lots of free techniques out
there, including our J Street Access Relinker at:
http://www.jstreettech.com/downloads

You're welcome to use it for your projects.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com