Checking whether the records are locked or not using vba

[radek225]

I have a code which cut the data from tables and insert to others. The problem is I don't know when I can do this process (when I will cut data and the same time some users lock editing records then I will get an an error). So my question is, there is some procedure which can check that some records in table are locked or not?

 

[Galaxiom]

I have a code which cut the data from tables and insert to others.

A process like that strongly suggests a structural mistake in the database.

 

[radek225]

Why You think that?

 

[Galaxiom]

Why You think that?

Aside from the problem you described that it causes?

Explain why you are doing it and I will tell you.

 

[radek225]

Ok, so I have an aplication split to front and backend(61 tables). Everything working in LAN. It's about printing process (Orders, and related technological cards). We can have 1) Active orders and 2) Orders completed. When orders get last active status, then user push "close order" button and every data from "Active" tables are cut and insert to "Closed" tables. We have 1600 orders per year and 11 users(front). I split for active and completed because:
1) 99% operation are doing on active orders (10-40 orders all the time), so we have small data to count (filter) in every process so it could be faster then count from all
2) Active orders have more fields in tables than closed orders.

 

[Galaxiom]

We can have 1) Active orders and 2) Orders completed. When orders get last active status, then user push "close order" button and every data from "Active" tables are cut and insert to "Closed" tables.

As I suspected. The movements between tables are being used to indicate the status of a record. You are using structure to record data instead of as values in fields.

The records should all remain in the same table. The status should be recorded in a field. If you already have a field that records variation in the status while its active just add another value for closed.

BTW Don't use a string to record the status. Use a number because it is considerably faster to index numbers. Be sure to index the field for best performance. An OrderStatus of something like -1 is a good choice if the normal processing uses positive numbers.

The queries that currently use the Active Orders table need to be altered to use the combined Orders table with the addition of a criteria on the OrderStatus to return only the active orders.

Any other movements between tables should have a similar treatment.

This not only avoids having to move records but greatly simplifies queries that need to return orders regardless of their status. With your current structure you would have to use a union query.

 

[spikepl]

Further to Galaxiom's advice:

so we have small data to count (filter) in every process so it could be faster then count from all

This is not a valid argument to maintain separate tables. Put index on the fields you use in selection criteria.

If you have a telephone book, then it hardly matters how thick it is - it takes a small amount of time to find something because it is indexed! (=ordered in alphabetical order, and you can quickly find the name of interest). If it wasn't indexed, so the records were ordered randomly, it would take ages to find anything.