Having Multiple users query a linked excel spreadsheet in access.

[Guest]

At work, i have created a linked Access database with an excel spreadsheet,
simply because i am the only one in our department with full rights to
access, and the rest only have the runtime, meaning that they can run my
switchboard (which has my queries, etc). This is being used to search for
insurance quotes, as our old system was very manual (walk up to the filing
cabinet). I wanted to allow everyone to be able to input data into the new
system, and seeing that we all have excel, i decided to make excel the place
to input the data, but i wanted the power of Access to run the multiple
queries that i have. I run into problems when i have multiple users trying
to query at the same time. It seems that there can only be one person
querying the sheet at a time. Is there any way around this with my current
situation?

 

[Graham Mandeno]

You are also going to have problems if two users want to update the
spreadsheet at the same time. You say the users have Access Runtime. Why
not move the data into a linked table(s) in a shared back-end database, and
add a form to your application to allow users to edit the data?

 

[Guest]

I had tried doing something of that nature, but the problem is that 2 people
cannot query the spreadsheet at the same time. While one person is viewing
results, the other person trying to get in gets an error, causing the app. to
halt. Thanks for the reply though.

You are also going to have problems if two users want to update the
spreadsheet at the same time. You say the users have Access Runtime. Why
not move the data into a linked table(s) in a shared back-end database, and
add a form to your application to allow users to edit the data?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

At work, i have created a linked Access database with an excel
spreadsheet,
simply because i am the only one in our department with full rights to
access, and the rest only have the runtime, meaning that they can run my
switchboard (which has my queries, etc). This is being used to search for
insurance quotes, as our old system was very manual (walk up to the filing
cabinet). I wanted to allow everyone to be able to input data into the
new
system, and seeing that we all have excel, i decided to make excel the
place
to input the data, but i wanted the power of Access to run the multiple
queries that i have. I run into problems when i have multiple users
trying
to query at the same time. It seems that there can only be one person
querying the sheet at a time. Is there any way around this with my
current
situation?

 

[Graham Mandeno]

I think you misunderstand me. I am suggesting you store the data in an
Access table, not in an Excel spreadsheet. That way, any number of users
can perform queries on the data at the same time, and you can even have
multiple users adding and modifying data at the same time (so long as they
are now editing the same record).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I had tried doing something of that nature, but the problem is that 2
people
cannot query the spreadsheet at the same time. While one person is
viewing
results, the other person trying to get in gets an error, causing the app.
to
halt. Thanks for the reply though.

You are also going to have problems if two users want to update the
spreadsheet at the same time. You say the users have Access Runtime.
Why
not move the data into a linked table(s) in a shared back-end database,
and
add a form to your application to allow users to edit the data?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

At work, i have created a linked Access database with an excel
spreadsheet,
simply because i am the only one in our department with full rights to
access, and the rest only have the runtime, meaning that they can run
my
switchboard (which has my queries, etc). This is being used to search
for
insurance quotes, as our old system was very manual (walk up to the
filing
cabinet). I wanted to allow everyone to be able to input data into the
new
system, and seeing that we all have excel, i decided to make excel the
place
to input the data, but i wanted the power of Access to run the multiple
queries that i have. I run into problems when i have multiple users
trying
to query at the same time. It seems that there can only be one person
querying the sheet at a time. Is there any way around this with my
current
situation?

 

[Graham Mandeno]

are now editing the same record).

Sorry - I meant "NOT editing the same record"

 

[Guest]

oooooooooooooooooooooooooo

you ever just have one of those days where you can't see past the problem to
the solution? yea, this was one of them. I did exactly as you said, i
created a back end database that contains the information, and i linked it to
2 front end databases, one for just viewing, the other for viewing and
inputting (my boss wanted it this way). I can't believe i didn't think of
that earlier. o well. Thanks a million!