How to convert to FE/BE

[Jay]

Hi all,

I need some advice on how to make some changes to my Access 97 DB.

I currently have a database set up to be used by one user. This works fine
as is, for the one user. Now I'd like to set it up to be used by multiple
users at the same time, and I don't really know how to do so. I'll explain
the situation.

Currently, the database is set up to allow users to perform select queries
and view the data. To build the query, the user selects from various options
on a form, and once they have finished, a query is created and another form
uses that query to display data. If they so choose, the data can then be
exported to an Excel template.

The database is to reside on a local server. Users should be able to access
the DB and perform the query they would like. However, since each user would
be creating a query, they conflict.

The users are not editing the data in any way, they are just performing
select routines. An administrator will be performing updates to the data,
but this is limited to specific people and multiple people will not be
updating data.

I had thought to do a FE/BE, but I think I have issues. However, I've never
done something like that so I may be wrong. A FE requires an mdb file with
links to another table. But if I was to do that, then it would still cause
conflicts as the users would still (in the end) be accessing the same DB on
the server.

I don't think an MDE will work as each user will not have a copy on their
drive. The file will be centrally located.

Is there a way to allow multiple users to access the same DB, but have each
instance of the DB behave as a separate entity?

I'm a little lost here. If anyone has some suggestions it'd be greatly
appreciated.

Thanks,
Jay

 

[Rick Brandt]

Comments interspersed:

Jay wrote:
[snip]

I currently have a database set up to be used by one user. This
works fine as is, for the one user. Now I'd like to set it up to be
used by multiple users at the same time, and I don't really know how
to do so. I'll explain the situation.

Currently, the database is set up to allow users to perform select
queries and view the data. To build the query, the user selects from
various options on a form, and once they have finished, a query is
created and another form uses that query to display data. If they so
choose, the data can then be exported to an Excel template.

The database is to reside on a local server. Users should be able to
access the DB and perform the query they would like. However, since
each user would be creating a query, they conflict.

If you split and give each user their own copy of th FE then this conflict
is gone.

The users are not editing the data in any way, they are just
performing select routines. An administrator will be performing
updates to the data, but this is limited to specific people and
multiple people will not be updating data.

I had thought to do a FE/BE, but I think I have issues. However,
I've never done something like that so I may be wrong. A FE requires
an mdb file with links to another table. But if I was to do that,
then it would still cause conflicts as the users would still (in the
end) be accessing the same DB on the server.

Yes. If you split but still share the same FE on the server then you lose
most of the advantages of splitting. Each user should get theri own FE.

I don't think an MDE will work as each user will not have a copy on
their drive. The file will be centrally located.

Bad idea. Besides the BIG reasons not to share a FE doing it this way
doesn't solve your problem.

Is there a way to allow multiple users to access the same DB, but
have each instance of the DB behave as a separate entity?

Well you could create new queries with a name that is specific to each user
so they don't conflict, but that is the hard way around the issue. Just
give them separate copies of the file.

 

[Jay]

Thanks for the quick response Rick! It looks like MDE might be the quickest
way.

Jay

Comments interspersed:

Jay wrote:
[snip]

I currently have a database set up to be used by one user. This
works fine as is, for the one user. Now I'd like to set it up to be
used by multiple users at the same time, and I don't really know how
to do so. I'll explain the situation.

Currently, the database is set up to allow users to perform select
queries and view the data. To build the query, the user selects from
various options on a form, and once they have finished, a query is
created and another form uses that query to display data. If they so
choose, the data can then be exported to an Excel template.

The database is to reside on a local server. Users should be able to
access the DB and perform the query they would like. However, since
each user would be creating a query, they conflict.

If you split and give each user their own copy of th FE then this conflict
is gone.

The users are not editing the data in any way, they are just
performing select routines. An administrator will be performing
updates to the data, but this is limited to specific people and
multiple people will not be updating data.

I had thought to do a FE/BE, but I think I have issues. However,
I've never done something like that so I may be wrong. A FE requires
an mdb file with links to another table. But if I was to do that,
then it would still cause conflicts as the users would still (in the
end) be accessing the same DB on the server.

Yes. If you split but still share the same FE on the server then you lose
most of the advantages of splitting. Each user should get theri own FE.

I don't think an MDE will work as each user will not have a copy on
their drive. The file will be centrally located.

Bad idea. Besides the BIG reasons not to share a FE doing it this way
doesn't solve your problem.

Is there a way to allow multiple users to access the same DB, but
have each instance of the DB behave as a separate entity?

Well you could create new queries with a name that is specific to each user
so they don't conflict, but that is the hard way around the issue. Just
give them separate copies of the file.

I'm a little lost here. If anyone has some suggestions it'd be
greatly appreciated.