If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro - one at a time, please!
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!!!!! -- GD |
#2
|
|||
|
|||
Macro - one at a time, please!
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 GD wrote: 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!!!!! |
#3
|
|||
|
|||
Macro - one at a time, please!
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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "GD" wrote in message ... 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!!!!! -- GD |
#4
|
|||
|
|||
Macro - one at a time, please!
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!! -- GD "John Spencer MVP" wrote: 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 GD wrote: 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!!!!! |
#5
|
|||
|
|||
Macro - one at a time, please!
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. -- Dave Hargis, Microsoft Access MVP "GD" wrote: 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!! -- GD "John Spencer MVP" wrote: 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 GD wrote: 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!!!!! |
Thread Tools | |
Display Modes | |
|
|