Sharing a Access Database over a network

[lucyh]

I have created a database and it is used by two people. They rarely need to access the database at the same time.

I have created the Database as "Shared" but they are unable to open it at the same time - although I can open the database if one of them are in it.

Is this something to do with the network permissions? I have heard about splitting databases into front end and back end but have never actually done this before (I am self taught novice...learning as I go!!!).

Is there a simple solution to this? If I need to split the database can someone provide step by step instructions that are so simple even I can understand them!! LOL

I operate on Office 2000...not very up to date in our office!!!

many thanks

Lucy

[munkee]

Yes you need to split the database. I use office 2003 but I am sure it wont be far from office 2000.

To split the database:

- Open the database to split
- Locate the tools menu at the top
- Select Database Utilities
- Select Database Splitter
- Click Split Database
- Select a location for your "back end" file.

The back end (BE) is where all of your tables will be held.

Your front end (FE) is the user interface which houses your forms, queries and VBA. It is in essence your application.

The back end is purely for storing all of the data that your front end manipulates/creates/deletes etc.

- Click Split when you have selected an adequate location for your back end to be held. This is usually a shared drive on a server where all users who use front ends have access to.

The wizard will then split the database.

You will notice in your application the tables held within the database window are now all "linked" tables. That is they are pointing to your back end.

You can copy and distribute out the front end file to whoever needs access. Each person who requires access to the database needs to have their own seperate copy of the front end. If you had everyone using the same front end you would be in the same problem as you are now.

You can just copy and email the front end to users or you can use a distribution program such as AUTO FE Updater ( google it ;) ) which will be a bit more managable.

Any other issues let me know.

[Stewart Ross]

Hi. Although it is indeed a very good idea to split the database as Munkee suggests, your single shared database should still open and be updateable by both parties using the database.

Both persons must have read and write permissions to whatever network area is being shared. Without this Access cannot create its lock file which you should see appear in the directory in which your database is stored when you open the Access database. The lock file has the same name as the database but with the extension .ldb. If there is a lock file present when you open the database then you'll need to investigate further why it is not being shared. If there is no lockfile as a result of permissions problems then the database cannot be shared.

-Stewart

[lucyh]

Thanks for the responses. When someone is in the database you can see this as a lockfile is created, but still only I can access the database if someone else is in it..confusing me somewhat.

I went to follow the steps given by Munkee and joy of joys the Wizard for splitting databases isn't installed...I will see if I can install and then get the database split :o)

Can anyone recommend a good part time evening course I could take to actually learn about access databases...nothing too basic but enough that I can create and manage really good databases?

[hype261]

Lucyh,

Have you looked at there setting to ensure that they are opening the database in shared mode? I can't remember if this property is for the db or it is for the user.

Also are you using any user level security? You can set this up so somebody will always open the db exclusively.

[MrDeej]

You should be albe to use the same .mdb or .accdb by different users. Access is deafult shared database, but when you enter the database and start making design changes access locks down the file.

Make sure the users dont have to make design changes. At my firm we was at the most 15 users on the same .mdb at the same time. No problem, with using it. But we had to convert away from relational tables and bound forms to non-relational (before query) and are now just using unbound forms with VB ADODB to make all table updates.

Splitting the front-end and back-end dosent do anything unless you are serving an individual front-end file to each user.

[lucyh]

I am still scratching my head over this. I can't install the wizard for splitting databases as we can't find the disk...joy!!

I don't really know enough about networks and all that jargon to figure out if the problem lies in there somewhere.

We are all operating different versions of windows - I was wondering if that would have any bearing. I am on
Windows XP Home Edition 2003 and the other two are on:
Person 1 - Windows Vista - Microsoft 2007
Person 2 - Windows 7 - Microsoft 2007

The two people using it will only be adding and editing data - they don't deal with any design changes. There are querys though that they work on in form view as means of updating records (I don't even know if I have explained that right..Im sure its showing how much of a novice I am!!).

I was wondering is there a way that I can set the database up so that it thinks one of the other users is it's creator and then they would have access at all times? (they would be happy to come out of it and let me work on it exclusively if any design changes were needed).

[colintis]

Is the 2 people using Microsoft Office 2007 includes Access program in full version as well? or is it individually install with Access 2000?

If they have the full version of Access 2007, you can also suggest your PC to get one as well, the split function is build-in with that version since 2003.

[lucyh]

I hope this makes sense Colintis - I looked at the Access properties on one of the other laptops (Vista) and it states Microsoft Office 2000. Opening the database on this laptop gives me the same response when I am asking for the database splitter - it wants the CD-Rom.

When I am working in design view on the Vista the whole layout for access is completely different so I had assumed this laptop had a newer version of access?

The Laptop running windows 7 gives me more options on the network / sharing permissions - would this be worth looking at or is it possible to change the author of the database (if that makes sense!!)?

Its funny that some people in my office think I am a bag of knowledge when it comes to computers but I am starting to feel that I have matching knowledge of a plank!!! LOL

[lucyh]

I saw your post and had hoped it was as simple as this :o) - I checked and the other two are opening the database in shared mode and still only one of them can be in it at the same time.

[lucyh]

[Make sure the users dont have to make design changes. At my firm we was at the most 15 users on the same .mdb at the same time. No problem, with using it. But we had to convert away from relational tables and bound forms to non-relational (before query) and are now just using unbound forms with VB ADODB to make all table updates.]

I have relationships and subforms in this database - would they have any affect on sharing the database?

[colintis]

It sounds weired that Access 2000 has a different layout in window vista...

Let take look here, below are screen shots of layout for version 2000, 2003, and 2007. Which one is the one you are looking with different layout looking..?

Access 2000

Access 2003

Access 2007

Also to mention, Microsoft Office is a name represent a whole package of software including Word, Excel, Powerpoint, etc. And Microsoft Access is part of this package, also in basic package of Office it does not contain Access

[lucyh]

Thanks Colintis :o) - on my computer its Access 2000 and on the Vista its Access 2007. I went to amend a query in the design view while the Vista User was in the database and it took me a while to find what I was looking for as I have never worked with Vista before (told you I was unskooled lol).

Is it just the way Access is presented in Vista that would be different and how the programme actually operates?

The girl who is on Vista had uploaded Office 2000 from the same CD-rom as me originally but then she got a virus and had to re-load the office package - she then uploaded office 2007.

[colintis]

Then I think you better off convince your boss to get you an office 2007 as well. As some compatible problems do exist with such a generation gap between the versions.

The difference of the layout is actually the version of Access Microsoft published, as you can see from the previous post, the layout of 3 versions are different, nothing to do with the OS either its xp/vista/win7. But far as I remember, win7 can't use office 2003 or older versions.

In Access 2007, you simply click on the "Access Database" button in the "Database Tools" ribbon to perform split database, its the one with a yellow thing pointing to 2 other smaller yellow things.

[lucyh]

Thanks Colintis - this may well be the problem then. I will see about upgrading to Office 2007. Will upgrading affect any of my existing databases? Just want to check first as I have my big baby database that I have spent the last 6 years tweeking and poking on my lunch breaks and I worry about all things upgrading!! Lol - you hear sooo many horror stories about having to convert files after an upgrade :o)and with me being such a novice it doesnt help with the worry :o)

[colintis]

I did heard quite alot of panic stories after upgrades, even to my own cases as well. A good reference to look at is HERE.

If your database don't have much VBA codings, then it won't be much work to follow, because the coding recognition is a bit different in 2007, normally the format or style problems. Notice that the upgrading in 2007 will be in another new extention .accdb in replacing the old .mdb one.

One more thing, before you upgrade your database, you can do the following incase you have data lost during the upgrades.

1. Copy a backup database file, in case you break the database you still have something to recover
2. Use compact and repair database function before you perform upgrade, checking existing error you didn't notice
3. When point 1 & 2 is done, upgrade your database and check its contain if any conversion error message
4. When no errors along the way, you can delete your old version backup if you like.