Locking a table with code

[MikeAngelastro]

Hi,

Is there a way to lock a few tables with code so that when a user starts an important operation, nobody else changes any data in the tables until that operation has completed?

Thanks,

Mike

 

[wizcow2]

Mike

I don't know if this would work in your application, but ...

You could remove the import function from the menus in your users applications.

Of course this wouldn't work if the users need to use the import function.

Tom

 

[MikeAngelastro]

Tom,

Thanks for the suggestion. However, the users of my application do not do any importing. They fill out sales orders and, as they are doing so, they are depleting the inventory in the inventory tables.

Because we also receive orders via EDI, I am creaing a program that automates the transfer of sales order data from the EDI file to the sales order files. In the process it first creates a string that says what locations it is getting the inventory from and then actually reduces the inventory. I would like to lock the tables for that period to ensure that the inventory is still where it was found when the string is created so that the depletion will actually occur as the string specifies.

Thanks again,

Mike

 

[llkhoutx]

Welcome to multi-user conflicts.

I wrote and application once where the customer required that inventory not be reduced until an order was shipped. THe idea being some customers are better than others and that sometimes shipments are held up.

Is your question theoretical, or are your users presently having problems? If theoretical, you may be worrying about a problem which will seldom occur.

Take a look at the form "RecordLocks" property. It's probably what you need. That value can also be set for all tables on to Tools|Options menu, Advanced tab.

 

[Mile-O]

To avoid table locks, how about unbound forms?

 

[MikeAngelastro]

Hi llkhoutx and Mile-O-Phile,

Thanks for your suggestions. The situation I am working with is this: We have folks using the regular Accounting System, which is an Access frontend linked to an Access backend. Some of the users are filling in sales orders from people who are calling in. They are using the accounting systems sales order form, which reduces inventory as line items are added to it.

We have another frontend called our EDI Processor and it is linked to the same backend as the Accounting System. This app imports EDI POs to itself and then stores the sales order information in the sales order tables in the Accounting System. It first stores the sales order and then depletes the inventory appropriately. This may take about 10 seconds depending on how many line items are on the sales order. While this part is going on, I want to lock the inventory tables so that only the EDI Processor can deplete inventory. The form in the EDI Processor is not bound; everything is taking place with code. The sales order form in the Accounting System is bound to the sales order tables but not to the inventory tables; so as far as the inventory depletions are concerned the operation from this form is not bound.

Is there a DAO command to lock and then unlock a table? If not is there another way to do it given a bound form is not involved.

Thanks again,

Mike