Accessing .mdb file from Shared Drive by multiple Users

  • Thread starter Thread starter FA
  • Start date Start date
F

FA

Hi Freinds,
I have my database on the shared drive, but the problem is only one
user at a time can open the .mdb file. The .mdb file has tables linked
to SQL Server and every user has a ODBC Connection set up to access the
database. I went in Option from the tool bar and in the Advance tab
there are two options to open the file, shared and exlcusive, i checked
the shared option. but still i am not able to use it when other user is
using it.

Is this possible to access same .mdb file on the shared drive by
multiple users?
Please let me know.

Thanks
 
Yes, it's possible for multiple users to access the same .mdb file on a
shared drive. However, you really need to split your database and give each
user his or her own copy of the frontend. The problem you are experiencing
right now may be due to the fact that the users don't have full permissions
to the folder where the database resides.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
How can you split the database with the Linked Table. I tried that and
it didnt work. I believe that the MS Access Database is already been
split when i am using sql server linked table on the back and and on
the front end its MS Access forms and reports. Every user has
permission for that shared drive and i did make sure by accessing that
shared drive from other user's machines.
I can not make an mde file with the linked tables in the database.

Any other solution?

Thanks
Moe
 
I did comple the whole project and it didnt give me any error message.
So you mean to say, multiple users can not access my front end (.mdb)
at the same time on the shared drive. It has to be .mde file ? When i
tried to make .mde file it gives me this error "Microsoft Access was
unable to create and MDE database" when i click show help it give me
this

"This error is usually associated with compiling a large database into
an MDE file. Due to the method used to compile the database, a
considerable number of TableID references are created for each table.
The Microsoft Jet database engine version 4.0 can only create a maximum
of 2048 open TableIDs at one time. Exporting a database as an MDE
potentially can exceed this limit if the database has a large number of
objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet
database engine uses during the process of compiling a database as an
MDE. However, each VBA module and each form uses one TableID, as a
result, if the database has 500 forms, and each form's HasModule
property is set to Yes, as many as 1,000 TableIDs are used.
More information about this error message online. "

I dont know what to do. I dont think .mde is a solution for my problem
, but there has to be a way to access the front end .mdb by
multipleusers.

Moe
 
It has to be .mde file ?

No, but it should be. Any competent developer will always distribute mde's
to their users.
When i
tried to make .mde file it gives me this error "Microsoft Access was
unable to create and MDE database"
Click to expand...
99% of the time, the above means you have some errors in your database. jump
to the code editor (ctrl-g), and then do a debug-compile.

The above means that you code likely has some compile errors, and you just
found out why most developers with any competence use a mde, since it FORCES
you to check your code. It REALLY REALLY is dumb to have users running code
that has compile errors!!!

So, the normal reason why you can't make a mde is that your code has errors
in it..and it will not compile. Get your code to compile first (the
debug->compile). Once you have this working..then you should be able to make
the mde.

As others mentioned, you need to place this mde on each machine
(so,...yes...you are split already in a sense). Read the following as to why
you place the mde on each machine (it is much better to know WHY you place a
mde on each machine...not being just told to. The same goes for
distribution a mde....worse then not distributing the mde is that you have
code in your application that is broken and does not compile!!!).

Here is why you place the mde on each computer:

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

I dont know what to do. I dont think .mde is a solution for my problem
Click to expand...

Sure, auto mechanics have a hard time telling people that they should change
the oil in their cars. If you don't change your oil tomorrow..it does not
mean your car will break..but any mechanic will think you are fool not
change your oil.

The same really that of taking some time to understand how software is
deployed and installed on each of your computers. If basic computer setup
and knowledge can't be followed here..then you will not achieve any degree
of reliability..... in fact, you will start to look incompetent at your
job...

You don't have to put a mde on each machine..but why risk reliability and
throw away good support money that could be used to feed the poor?
 
Thanks Millions Kallal, I did the compile thing and was able to make an
..mde file. Now that i have .mde file, your recommendations are to
installed it on each machine, and i will follow advise. What i
initially wanted to have everybody access it from share drive but now
that i know the better way is to install it on each machine. Also what
about .exe file? Would that be a posibility? If so, would you recommend
it ? If so, how would i achieve it?

Thanks again Kallal

Moe
 
Also Is there any way i can disable the Startup in the Tools. I dont
want any user to go in startup and see the database window at all.

Thanks
Moe
 
You are most welcome.
What i
initially wanted to have everybody access it from share drive but now
that i know the better way is to install it on each machine.
Click to expand...

To be honest, some people have said they had no problems doing this.
However, we OFTEN see all kinds of problems when this is done.
It is one of those risk factors. For some reason, acces97 seemed to
tolerate more then one user in the mdb/mde file. For some reason, a2000 and
later does not take well to this at all. I don't want to sound harsh...or
say you "must" do this. However, it is just a good idea...and if you came
back and said:

But..it is more work to distribute those stupid files to each machine.
You mean I have to walk around and put the mde on each machine?

I would 100% agree with you!!.

This is one pain issue, but a tradeoff for much more reliability!!! So, the
solution is then to have some code to "copy" the new Front end to each pc. I
rolled my own code in the ms-access application (it would check a version
number in the back end). However, Tony has a nice free updater here that
will solve this problem for you here:

http://www.granite.ab.ca/access/autofe.htm
Also what
about .exe file? Would that be a posibility? If so, would you recommend
it ? If so, how would i achieve it?
Click to expand...

There is no ability to create a small single .exe file from ms-access. You
can however purchase a royalty free runtime system for ms-access. This will
allow you to deploy a mde on a pc that does NOT have ms-access installed. So
there is no such thing as a .exe file here. The access runtime is rather
large (34 megs in size -- so, it is essentially ms-access without the
ability to modify forms). I suppose you could re-write the application in
something like VB, as that then would get you a .exe file. (but, VB does
also requite a runtime system also) . So, other develpuemnt tools like VB6
will create .exe files, but I don't think this is an advantage here. And, VB
is missing a lot of things like a nice report writer we have in ms-access.

And, if your question was can you share .exe file on the server? This again
is something that companies did do on occasion many years ago..but
today...we simply put the .exe files on each computer for all of the reasons
mentioned (reliability...less bandwidth etc.).
 
FA said:
Also Is there any way i can disable the Startup in the Tools. I dont
want any user to go in startup and see the database window at all.
Click to expand...

You most certainly can, and should hide all of the ms-access interface. The
options to complete hide and keep people out of the ms-access interface can
easily be done using the tools->start-up options. Using those options allows
you to complete hide the ms-access interface (tool bars, database window
etc). Also, using these options means you do not have to bother setting up
security.

Try downloading and running the 3rd example at my following web site that
shows a hidden ms-access interface, and NO CODE is required to do
this....but just some settings in the start-up.

Check out:

http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

After you try the application, you can exit, and then re-load the
application, but hold down the shift key to by-pass the start-up options. If
want, you can even disable the shift key by pass. I have a sample mdb file
that will let you "set" the shift key bypass on any application you want.
You can get this at:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
 
Back
Top