Macro - one at a time, please!

[GD]

Is there a way to prevent a macro from being triggered by one user if another
user already has it running, in a multiple user environment?

My situation is this: I have a form that the user can enter between 1 and 5
parameter criteria. A query was then set up to search for these criteria
anywhere within 3 separate fields in a 1,000,000+ record table. Suffice it
to say, it was desperately slow, even when indexing the search fields!!

I've come up with a much quicker search by narrowing the focus to just the
particular vendor the user is concerned with + the various alternate names
our branches around the country may have for that vendor. In order to make
it as speedy as possible, I'm using several holding tables to temporarily
store the data, thereby utilizing the indexing.

The crux of my problem is that there are several delete queries at the
beginning of the macro to clear the holding tables for a new search.
Therefore, if one user is running the macro and another triggers it, won't
the tables be cleared before the first user's search be disrupted? The macro
takes 5-30 seconds to run, depending on the size of the vendor's data, but
that's plenty of time for 6 users to choose the same macro.

So again, can further use of the macro be disabled while running? Or
better, can it be set up to run only when it is finished running its current
course??

Thanks!!!!!

 

[John Spencer MVP]

In order to keep the macro from running you will need to set a flag somewhere
and have that accessible. Since this is a shared database the best place to
store information that everyone needs to see is in a table.

Table: tblBusy
Field: ExecuteTime: DateTime
Field: UserID: String (Optional field to identify which user called the macro)

Next, I would not use a macro for this but I would use VBA code.

Your macro would need to check the value of the field ExecuteTime and compare
that to the current date and time. If sufficient time had elapsed then allow
the macro to continue and to reset the ExecuteTime field value. If not,
message to user to wait.

This is probably NOT the best solution since the tables you are using may need
to remain unchanged until the user has finished printing the report or whatever.

A better solution would probably be to create a temporary database and tables
on each user's computer and use that. Once the user has finished (or they
quit the application or some other event occurs) you can delete the temporary
database.

See Tony Toews' website
http://www.granite.ab.ca/access/temptables.htm
for an example of how to create a temporary table in a temporary database

Also if you are using a wildcard search and looking for a string contained in
somewhere in a field, then indexes cannot be used and you are scanning the
table instead of using the index. If you are looking for a string that is at
the beginning of the field, then the index will probably be used.

Sometimes you can speed up the query significantly by nesting queries so that
the first query returns a limited set of records that is than filtered by the
next query up the line. Something like the following:

QueryOne:
SELECT SomeTable.*
FROM SomeTable INNER JOIN Vendors
ON SomeTable.VendorId = Vendors.VendorID
WHERE Vendors.VendorID In (1,3,52,201)

QueryTwo:
SELECT *
FROM QueryOne
WHERE SomeTable.FieldA Like "*abs*"
Or SomeTable.FieldB Like Like "*abs*"
Or SomeTable.FieldC Like Like "*abs*"

In practice the above is so simple that the SQL engine will probably use the
same plan whether you used two steps (like above) or used one simple query to
accomplish the same thing. HOWEVER, with much more complex queries, you may
see a significant increase in speed. As always, your mileage may vary.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Arvin Meyer MVP]

If you are using temporary tables, they should be in the front-end of split
databases. You won't have that problem any longer. In a multi-user
environment, all databases should be split with a front-end on each user's
workstation, linked to the back-end on the server. Any other configuration
MUST be avoided or you will eventually experience corruption which will
bring production to a halt until it's fixed.

 

[GD]

Thanks, John!! Very thorough!

I tried nesting queries, but using holding tables made for a MUCH faster
search. I assume it's because of the indexing option.

I'll explore your advice ASAP. Thanks again!!

 

[Klatuu]

Arvin's suggestion is very good, but I would do the same thing a bit
differently. Rather than putting the tables in my FE, I would create a
separate mdb (accdb) that would be on the user'rs computer and the temporary
tables would be in that mdb. I would like the tables to my FE.

The reasons for that are that it would be better to maintain table is an mdb
and you may want to deploy an mde to the user. The other is that the sort of
activity you are doing will create bloat and the mdb should be compacted
occassionally.