Timer Event and multiple instance forms

[ukaquanaut]

Hi Guys

I've been having a hunt around the web and not been able to put together a solution for this maybe you can help me please?

I have an Access db which is stored on a server on a network and used on multiple computers, mostly with few issues. in the UK.

But I have been recently been fetching some data from an SQL Server db in the USA, I get the whole new table of data in one go not individual requests. I fetch this data by use of a command button and that has worked flawlessly

However, users are not doing updates with any regularity and have been complaining its out of sync.

We thought about using a timer event to trigger a fetch, in my prototype this worked well and regularly fetched the data table from the USA.

However, if I try and use the db in the production environment where a number of these forms would be open on different PCs at the same time. I get table conflicts as each form tries to do the updates at different times when the period event triggers.

I need to limit the instances of the db form that the event timer is used on. Event/Instance management is a new aspect of Access and one I'm not familiar with, I wonder if anyone has any ideas please?

regards
Jerry Barrett

 

[The_Doc_Man]

The way you described this is confusing with respect to your structure.

I have an Access db which is stored on a server on a network and used on multiple computers, mostly with few issues. in the UK.

OK, multi-user. But is that a split database with single back-end and multiple front-end or is it a unified database in a single file?

I get the whole new table of data in one go not individual requests

Replacing a whole table in one operation is a guarantee to run afoul of SOME locks when you have multiple users, particularly if it is a big and/or frequently used table. Which, from your comments, I infer to be the case.

I question the design because it seems to be asking for trouble. The question I have is this: If this is a shared database, then ONE upload ("fetch" as you described) reloads the table then why does ANYONE ELSE need to update it? They can see whatever was loaded from the last person's use. I just don't see WHY you need to fetch for everyone if a recent fetch has occurred.

Therefore, I see a structural question here and need you to clarify your architecture with regard to split database/unified database and also with respect to what each user sees that can affect other users wholesale.

 

[ukaquanaut]

Hi The_Doc_Man, thank you for your response. Yes, I'm sorry I didn't explain it very well forgive me

The Access db is a unified single file not split.

A bit of background; The company has a SAP ERP product with the SQL Server backend in the USA. This is used by order processing, accounts, picking, packing and goods-in to manage the widgets with barcode scanners.

Locally here in the UK we have an Access db used to process and print a variety of barcode labels through bartender. We used to maintain and upload an excel spreadsheet with product details for our Goods in 2D barcode labels and bin end labels.

However, we recently discarded the Excel spreadsheet and download the basic product details we want from SAP/SQL this works great as I said. but it was a manually triggered download from SAP/SQL.

We were given an SQL view of the data we need, as fetching the data via ODBC is quite quick about 5-6 seconds, it was suggested that we re-fetch this data every 15 minutes or so, so that the locations provided by the SAP warehouse management system are more real time. No data is written back to the SQL Server from the Access db, its a read only activity.

So here in Romsey, the Goods inwards guys on a couple of PCs printout the 2D product labels from the Access db which holds all the necessary latest data from SAP/SQL fetch. this means two instances of the goods-in label printing form is open simultaneously.

This is the Access form that has the timer trigger event in and of course the timer kicks off twice and seems to then have table access conflicts.

I suppose what I need to do, is in the timer event code try and detect if this Access form is being opened for a second time and if it is, then bypass the SAP/SQL server fetch code.

Viola as they say

Regards
Jerry

 

[Minty]

Add a field to a table somewhere or create a table with this field in it - call it ProcessLock Yes/no Boolean field.

In the update on timer event check the status of the field - it it's no then set it to Yes and run the update, once the update is complete set it back to no.

If it's no then just exit - don't run the update as something is already doing it.

Make a override somewhere that lets you reset this field in case something bombs the database out while it's running.

 

[ukaquanaut]

Add a field to a table somewhere or create a table with this field in it - call it ProcessLock Yes/no Boolean field.

In the update on timer event check the status of the field - it it's no then set it to Yes and run the update, once the update is complete set it back to no.

If it's no then just exit - don't run the update as something is already doing it.

Make a override somewhere that lets you reset this field in case something bombs the database out while it's running.

Hi Minty, Thank You. for your thoughts and reply.

After some thought I'm not sure it would work that well as it would still allow both instances to run the request. if they get a chance of the collect a ProcessLock.

Id like to just stop one of the instances running the event completely. maybe.

I wonder if this idea could be changed to provide that. I don't know if a form has a unique ID which is accessible?

Jerry

 

[isladogs]

Minty's solution sounds fine to me.

Each database object does have a unique id stored in the hidden system table MSysObjects.
However I don't think that will help.
My guess is that if you test by opening the same form twice, it will use the same ID????
Might be worth checking though

 

[Minty]

I don't know how it would run twice at the same time ? The logic flow is

Is the process Locked¬ Yes ¬ Exit
¬No
¬Lock anyone else out of running the process
¬run the the process
¬check it's run successfully if you can
¬Unlock it
¬Finish

It's the same database being shared yes ?

We use a similar process for an Invoicing Upload process - it's never failed us, except that it sometimes get locked out if someone has a network crash. This only stops the process being run simultaneously.

 

[ukaquanaut]

Hi Minty

I agree your processlock is great, I seem to remember it being called a rendezvous back in the day

Sorry I wasn't suggesting that they would run at the same time. they cant, but as they are timed events triggered from when the form starts, it could be that each form has the ability to run as it finds a 'No'

this might mean while each timer is 10 minutes, if one form opens 5 minutes after the first, we would end up with a 5 minute fetch cycle to SAP/SQL in its self maybe not an issue. but form runs a search subform which needs to be closed to update the products table when the SAP fetch occurs.

This may mean the subform on one goods in form isn't closed as the SAP/SQL fetch is triggered from the second and if the search subform isn't closed it table locks/conflicts.

This why I want to completely stop the goods in form opened second from running the timed event.

Although I'm still not sure about the search subform on the second goods in screen - arrgghhhh.....

Jerry

 

[Minty]

Well - add another field to your "LocalSystem" table with the last update time. If it's within 9 minutes don't run it, that way only one open version of the database will ever run it.

As for the record locks - this is all read only I believe - so open the recordsets for the forms as snapshots or dynasets with no record locking, or lock the forms for editing, and you shouldn't have any ?

 

[ukaquanaut]

Hi Guys… back again with a similar problem although I have managed to progress since Minty offered his help. Thank you again.
I have the SAP auto update working on a single PC client with the help of a table based semaphore system that was suggested. And I can even shutdown the product search popups prior to the Auto Update of the Product table.
However, I have run across an issue reopening the product search popups from the button after an Auto Update has completed:
On the PC which is running the Auto Update form, I get an error panel telling me ‘you don’t have exclusive access to the database’. When I click on the Product search popup button on the form.
If I click ok or close and the panel goes away the product search panel opens with all the products searchable as normal.
I have since split the db and gone through my code checking I have set things like db and record sets to Nothing at the end of procedures. To no avail. I don’t really understand why it thinks there’s a lock on the data table when it worked perfectly prior to the Auto Update process being written and the other PC opens the search popup form without incident?
I’d appreciate any ideas please.
Regards
Jerry

 

[ukaquanaut]

Hi Guys

I think I have it sorted out now, I had two macros for button events. I changed them to procedural and my error seem to have gone away.

Cheers
Jerry

 

[Mark_]

Another way to avoid this issue it to have two date fields in your "Local System" table; Dt_LockOut and Dt_DataPull.

Have your timer run every 15 seconds.

If NOW() > Dt_LockOut Then
   Do Code to get all users out of lookup
   msgbox "Table being updated.... Please wait"
End If

If NOW() > Dt_DataPull
   Dt_Lockout = Now()+ 870 seconds (14 and a half minutes)
   Dt_DataPull = Now() + 900 seconds (15 minutes)
   Update "Local System" table
   Fetch from other source
End IF

Your timer checks often but you can tell when you want the update to happen. This will avoid most of the issues with multiple users trying to access the same file at the same time.