Detecting multiple users

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

I've created a form where users make selections and press
a button. This in turn runs a series of queries, moves
the output to a table, which is then sent to Excel.
Everything works perfectly unless two or more users try to
run the thing at the same time... which causes the table
to disappear! Is there a way to detect that the table is
already in use, and make the second user wait until it's
finished before the second user's queries run? Or maybe
detect that the button has just been pressed, and make the
second user's request wait?

Thanks,
Cathy
 
Cathy said:
I've created a form where users make selections and press
a button. This in turn runs a series of queries, moves
the output to a table, which is then sent to Excel.
Everything works perfectly unless two or more users try to
run the thing at the same time... which causes the table
to disappear! Is there a way to detect that the table is
already in use, and make the second user wait until it's
finished before the second user's queries run? Or maybe
detect that the button has just been pressed, and make the
second user's request wait?
Click to expand...

That's the problem with using a table as a temporary place
to hold information. Why don't you just send the results of
the query directly to Excel??

If you must use a table, then the best approach is to create
a temporary database to hold the temporary table, but this
is rarely necessary.
 
Marshall,

How can you send directly to Excel? Everywhere I've
looked it says you have to make a table first...

Thanks,
Cathy
 
Cathy said:
How can you send directly to Excel? Everywhere I've
looked it says you have to make a table first...
Click to expand...

There are lots or ways to send data to Excel, the
integration between Excell and Access is amazing.

You never said how you're trying to do it with a table
(TransferDatabase?), but generally you can just use a named
query where ever you're currently using a table.
--
Marsh
MVP [MS Access]


 
Hi,

Surely if you are doing this in a recordset with the correct locking it
should not be an issue? Just open the value in the recordset, check the
value and update it if necessary.

However, I do concede that if it is for a business critical system, I like
your way - it adds a extra layer of security.

Jamie.
 
Jamie:

Comments interspersed.
Surely if you are doing this in a recordset with the correct locking it
should not be an issue? Just open the value in the recordset, check the
value and update it if necessary.
Click to expand...
If there's a way to gain exclusive *read* access to the table, then,
yes, this seems like it would work. But I'm not aware a way to get
that exclusive access. If you know of one and have the time to share,
I could use the education. :-)
However, I do concede that if it is for a business critical system, I like
your way - it adds a extra layer of security.
Click to expand...
Thanks. :-)

-Matt
 
Matt
If there's a way to gain exclusive *read* access to the table
Click to expand...

Have a look at the OpenRecordset method. There's a parameter for options
and one of the options is dbDenyRead. Its valid for Jet Table-type
recordsets only

Set rs = db.OpenRecordset("myTable", , dbDenyRead)

HTH

Andy
 
Back
Top