Detecting multiple users

[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

 

[Marshall Barton]

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?

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.

 

[Cathy]

Marshall,

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

Thanks,
Cathy

 

[Marshall Barton]

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

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]

 

[James Brown]

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.

 

[Matthew Sullivan]

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.

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.

Thanks.

-Matt

 

[Andy Cole]

Matt

If there's a way to gain exclusive *read* access to the table

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

 

[Matthew Sullivan]

HTH
It does. Thanks!