Accessing different databases from a database already open

[hall ted]

I have 9 different databases that track costs for 9
different divisions. I have set each database up as
generic so that when making changes I can change one
database and copy it to all of the others saving only the
tables. This saves me from having to make the changes 9
differnt times. I have one person that needs to access
all 9 databases. I would like to have a database that
they open and when they click on button A A database
opens up. I have tried the run apps, but because my
databases are on the network server it has problems
trying to open them with the command "c:\program
files\msoffice\office\msaccess.exe
\\server\location\folder 2\folder 3\folder 4
\databasename.mdb". I don't want to store all the data in
one table, because I need to limit access to the data, in
other words I don't want Division A to see/input division
B data. Any ideas how to accomplish this? Thanks in
advance.

 

[Van T. Dinh]

It is much better to use ONE database. If you don't want one division to
see the other division data, you build proper interface with filters so that
each user can only see his department's data or his and other departments'
data. Clearly, you will need to implement some algo. to identify the user
and his department.

 

[Hall Ted]

I get very frustrated when I post on this board. I have
noticed when someone asks a question, more time than not
they do not get the question answered, but they get
someone suggesting a different way to do it. Without
getting into all of the details on why a person is
setting up the database the way they are, and trying to
justify why they are doing it that way, why can't people
just answer the question if they know the answer, and if
not do not respond to the question.

 

[Joseph Meehan]

I have 9 different databases that track costs for 9
different divisions. I have set each database up as
generic so that when making changes I can change one
database and copy it to all of the others saving only the
tables. This saves me from having to make the changes 9
differnt times. I have one person that needs to access
all 9 databases. I would like to have a database that
they open and when they click on button A A database
opens up. I have tried the run apps, but because my
databases are on the network server it has problems
trying to open them with the command "c:\program
files\msoffice\office\msaccess.exe
\\server\location\folder 2\folder 3\folder 4
\databasename.mdb". I don't want to store all the data in
one table, because I need to limit access to the data, in
other words I don't want Division A to see/input division
B data. Any ideas how to accomplish this? Thanks in
advance.

I am guessing a few things, but it sounds like you should have one back
end database with ALL the data (well almost all in some cases) and front end
databases on each user's machine linked to the tables they need.

If you need to control access, user level security in Access does a fine
job. You one user that does it all, will have no problem accesses all the
data.

This almost always speeds things up and makes access a lot more
convenient. Your job is usually easier as well.

I am guessing that everything and every user is on a LAN. Your one
super user could link to all the tables in the other databases and be able
to access each of them from his one front end, even if you don't want to
change your general setup.

 

[Van T. Dinh]

If we see you going the wrong direction, what do you think we should do?

Sooner or later, someone will bound to ask you for a report or something
that involves data from all departments and when you get stuck, you wonder
why no one advised you the right way at the beginning.

If you want people to answer strictly as you ask, perhaps you should use
commercial consultancy service rather than the public newsgroups. At least,
if you want to try the public newsgroups, put a note on your message to the
effect that you only want the answer as you ask and no further like you are
the boss and see how many replies you get.

BTW, I had a case where a client had 250+ databases and they wondered why
they had to "move" around so much to get the info.

--
HTH
Van T. Dinh
MVP (Access)

P.S. Clearly, you can see I am not the only one who thinks you should be
advised of the correct use of database.

 

[Joseph Meehan]

I get very frustrated when I post on this board. I have
noticed when someone asks a question, more time than not
they do not get the question answered, but they get
someone suggesting a different way to do it. Without
getting into all of the details on why a person is
setting up the database the way they are, and trying to
justify why they are doing it that way, why can't people
just answer the question if they know the answer, and if
not do not respond to the question.

We see it time and time again that someone is asking such a question,
because MS Access has such a learning curve, they got that far without
knowing they were doing it the hard way.

We try to re-direct to the best path.

When you posted your original question, you indicated nothing that would
tell us any different. You got good advice.

I might add that if you have some special reason, or other oddity about
the application, telling us this information can make a big difference in
the proper advice to give you.

You really did not give is much information about your situation. There
may well be several possible direct answers to your question, but without
that information we are still shooting in the dark.

Frankly I believe your response was rather rude. We are not paid and we
are trying to help you. We gave good advice based on what you presented and
what did you do????

Now say your are sorry and try to give us some more information and
maybe we can give you some better answers.

 

[Hall Ted]

If I offended someone with my comments I am sorry,
however this forum does not allow to go into all of the
details. My major problem with my database is that I
have people access it all over the country, and we limit
bandwidth, so to try and have the databases run faster, I
put them on each individuals local server. When I try
and access a database on a server a a different location
it takes upto 5 minutes just to open a form. Right now
for my "super User" I have a union quesry that pulls all
of the data into one query for them to generate reports
that show all division data. The union query only allow
view rights and my super user cannot change the data in
division A. This is why I want super user to click on a
button that will open each division. I know this is
wordy, but people want to know the reason we do things
the way we do.

 

[Guest]

I know nothing about back end and front end databases.
Where can I get more information?

 

[Van T. Dinh]

Check Access Help on "Splitting a database" and Tony Toews' article:

http://www.granite.ab.ca/access/splitapp.htm

 

[Joseph Meehan]

I know nothing about back end and front end databases.
Where can I get more information?

The built in manual/Help (F1) has information on it. There is a built
in wizard, but as I suspect you know, additional information would be a good
idea before starting.

Most books on MS Access go into this procedure along with normalization.
I do suggest getting one or two as it appears you have been given the job to
handle this. MS is a great program, but that learning curve is a bear.

I also suggest Tony's as suggested by Van T. Dinh. He has some very
easy to understand, yet solid information.