Sharable Database

[Shirley]

I have developed a database that I would like multiusers to used on a network server. But I can't figure out how to make the database accessible to more than one person at a time. If you have any suggestions I would greatly appreciate it.

 

[dreew]

I've done that with a few applications by just putting the mdb file on a shared network drive. From there I've created shortcuts from the individual's desktops and most have been running fine for the last 2 years.<br>
<br>
Instructions for this recommend using the database splitter in the tools/add-ins menu to set up front-ends and back-ends if network traffic is an issue. <br>
<br>
The only issue i've run across is with file-locking.<br>
<br>
These aren't huge databases, the largest of which got to about 500 meg, but still behaves resonably well.

 

[culshaja]

Splitting the database into two parts is always a good idea even if you are not sharing as it allows update of the user interface simply an without running the risk of loosing data.<br>
<br>
James

 

[Shirley]

dreew and culshaja<br>
<br>
The database I've developed is hughs. There is something that I just don't get, after splitting the database, when copying the database to the sharable network file, do I put the back end and front end copy or just put the front end copy. And once I've copied the database over then this will allow multipleuser to view and edit my program. Is this all that I will have to do to allow multipleuser to use the program. And when it comes down to updating the data, how is that done? Sorry guys, I'm just a poor confused programmer.

 

[culshaja]

You need to put the back end with all the tables on the shared drive. Then put the front end on the users machines.<br>
Use link tables from file to link tables from he back end into the front end. The queries and forms in the front end will then access the tables on the server via the links. An advantage of using this method is that users can be given different front ends accessing the same data. Also users can see the data on differently mapped drives if needs be as the linking to the back end is distinct to the individual user.<br>
If you want more in-depth answer then mail me at <A HREF="mailto:culshaja@hotmail.com">culshaja@hotmail.com</A><br>
<br>
James

 

[Shirley]

I still a little confused, so I would put the back end on the shared network file and <br>
put a copy of the front end on each user computer. And I would manually relink all the table, because most of the computer do not have the master link wizard capability. Then how do you give each user different acces? Help Please! <br>
<br>
My e-mail is <A HREF="mailto:shirleysanders@sbcusd.k12.ca.us">shirleysanders@sbcusd.k12.ca.us</A>. <br>
<br>
Thanks Culshaja I really appreciate this.<br>
<br>
<br>
I tried to e-mail you culshaja but the Network is unable to find you mail server.

 

[WP]

Shirley <br>
<br>
All the users should have the same drive mapping pointing at your shared network folder. e.g. X:\ repesents //share path<br>
<br>
So, when setting up the table links you link to X:\data.mdb where data.mdb is you data file ( the back end db )<br>
<br>
All the users will point at X:\data.mdb too .<br>
<br>
Hope this helps <br>
<br>
WP<br>
<br>
<p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[Shirley]

I tried that, I put all the data tables on the server shared files and put a copy of the program on each user computer. The program on each user computer, I linked to the daat file. But it's still not working, even the network person checked the rights for the user and they all have rigths, and the data file is sharable and transactional. So I don't know what I'm doing wrong.

 

[WP]

Ok,<br>
<br>
You've done all the right things. So what is the error you are getting ?<br>
<br>
<p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[Shirley]

The error that I'm getting is when one user have the program opened, If someone else tries to use the program it stated that &quot;Couldn't lock table, currently in use by User on computer#&quot;. Maybe it is because this particular program only have 2 tables. Or maybe it is because the memory they have on thier server is low. I don't know. I am tempted to try to set the program up in VB and see what happens. I have tried the process on another program for another department and it works find. But that program had over 12 tables, maybe the program with 2 tables should be imported. But if it is imported than what happens when new entries are add or edited. I am really stumped, I don't know what to do. But I have to figure it out by the end of the month.

 

[DougP]

One thing you may do for safety is create a .MDE file from your original database and let the users run it not the .MDB. I have lots of Access databases on 4 severs and occasionally one might crash and corrupt the VBA code.<br>
The database when launched again will give all sorts of errors from &quot;Invalid database&quot; and others.<br>
The only way to fix it is restore from backup.<br>
I've done it many times.<br>
The .MDE's will never corrupt when and if they crash. Of course when you make a change to the original you have to recreate the .MDE Look for it under the Tools menu, Database Utilities, Make MDE file... when you are looking at the main database window.<br>
<br>

 

[WP]

Ok<br>
<br>
how are you accessing the database? Are you using shortcuts pointing at a security file or just via loaded access.<br>
<br>
If the latter check that the db is not being opened exclusively.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[Shirley]

I've made sure that the db was sharable. The table is set up under a shared file on the server and I copied the program on the user C:\Program File\... I also set up the user a shotcut icon on their desktop for easy access. But it still not working.

 

[dreew]

Hiya,<br>
<br>
Might have another 2 cents to add. Try changing the locking in the source object to snapshot and see if that changes your situation. It might be that the record that you're opening when starting the database is already being accessed by the first user, and cannot be seen.. It sounds wacky to me, since locked records can still be viewed by third parties on my networked databases, but at least we'll be able to remove the possibility of the problem being caused by access's locking routines. <br>
<br>
Making sure the database is not being opened exclusively is also key.<br>
<br>
Good luck, and happy holidays

 

[Shirley]

Thanks for you help, I'll try any thing now.

 

[Shirley]

I went back and checked to make sure that all the tables and program were sharable and they were, so I don't know what is wrong.

 

[culshaja]

To try and resolve the locking problem get everybody out of the database and then delete the ldb file. This will get rid of any unreleased locks. Another thing to do is open the data and application databasaes and look under the tools menu at options and make sure the default opening mode isn't set to locked on the general tab ( well I think it is the general tab). I had a similar problem with shared use and locking.<br>
<br>
James

 

[DougP]

If you can't delete the .ldb try rebooting the machine where the to file are located.<br>
The locking problem may be the database is partly corrupted.<br>
<br>
Note if Database is corrupted all is not lost the data in the Tables and the Queries is still retrievable.<br>
Try to import each item i.e. Tables, Queries Reports, Macros, etc. one section at a time.<br>
You can import multiple items at once.<br>
To import the data create a new .MDB then Import the tables from the old .MDB<br>
Click 'File', Get External data , Import<br>
<br>
This is a problem with Access'97 and multiple people using the.MDB<br>
Make a .MDE when you are all done and all of your problems will go away.<br>
I haven't used 2000 yet to know if it has the same problem.<br>
Access '95 did not seem to have this problem.<br>

 

[Shirley]

Thanks for the suggestions, I'll try that.

 

[Shirley]

Thanks for the suggestions, I'll try that.