Lock Table

Dear All,

what I need to do is the following:-

1) Open a table to insert a new record
2) Lock this table so there is no new entries in it
3) get the maximum call number and add 1 to it
4) insert the new record
5) Unlock the table again.

How can i do it? I know how to insert the record and get the maximum call number. What i do not know is how to lock and unlock the table.

I am using ASP to do this.  I tried several VBScript commands but they did not work since the connection is a ConnX connection to make Access talk with Dataflex.

Thanks for your help and time
monfuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1WilliamCommented:
You will want to add a field, type boolean(yes/No)
Open the table, set the boolean, create/insert the callID
Other users that will want to access this record, make sure thier queries include the boolean to exclude the record.
Insert the call where the callid =callid and the boolean is locked
Remove the boolean
monfuAuthor Commented:
The problem is that the system does not only allow calls to be logged through the web system but also through telephone calls and also an existing application that is already in place!  I think there must be some kind of command/system to lock the database safely
PoppekopCommented:
You want to lock the whole table don't you?
Gotta look it up. Just a sec
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

1WilliamCommented:
Ok, I was thinking of another method.  Open a recordset.  AddNew, Get the max ID + 1, Retain the new value as a variable, update the recordset.  I use this very method to do a call center app with over 35 concurrent users.
If you set table locking on, everyone would be locked out until the lock is removed.  This would mean a record has to be added on at a time.  Everyone elese would have to wait.

1WilliamCommented:
The other thing you will want to do is store the Call ID in its own table.  One record.  You lock that record by opening a recordset against it.  get the value, add 1, then update.  
monfuAuthor Commented:
Hello William,

Can you please give me a code example cause i did not really understand.  

I tried this:-

Set rsInsertCall=Server.CreateObject("ADODB.Recordset")
rsInsertCall.ActiveConnection = cnnDFlex
rsInsertCall.Open xersercl, cnnDFlex, adOpenKeySet, adLockOptimistic, adCmdTable
rsInsertCall.AddNew
rsInsertCall("Call_no") = NextCall
rsInsertCall.Update

but I got an error:-

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Mind you I am inlcluding adovbs.asp
PoppekopCommented:
just got this out of the help file to lock records, but why don't you just use an autonumber for this field? This will automatically pick a new number. If you choose increment at new values it will pick the next number.

The thing is that if you already have values in the field you cannot make it an autonumber:(

---------

RecordLocks Property
               

You can use the RecordLocks property to determine how records are locked and what happens when two users try to edit the same record at the same time. When you edit a record, Microsoft Access can automatically lock that record to prevent other users from changing it before you are finished.

Forms. Specifies how records in the underlying table or query are locked when data in a multiuser database is updated.


Reports. Specifies whether records in the underlying table or query are locked while a report is previewed or printed.


Queries. Specifies whether records in a query (typically an action query in a multiuser database) are locked while the query is run.

Note   The RecordLocks property only applies to forms, reports, or queries in a Microsoft Access database (.mdb).

Setting

The RecordLocks property uses the following settings.

Setting Visual Basic Description
No Locks 0 (Default) In forms, two or more users can edit the same record simultaneously. This is also called "optimistic" locking. If two users attempt to save changes to the same record, Microsoft Access displays a message to the user who tries to save the record second. This user can then discard the record, copy the record to the Clipboard, or replace the changes made by the other user. This setting is typically used on read-only forms or in single-user databases. It is also used in multiuser databases to permit more than one user to be able to make changes to the same record at the same time.
In reports, records aren't locked while the report is previewed or printed.

In queries, records aren't locked while the query is run.
 
All Records 1 All records in the underlying table or query are locked while the form is open in Form view or Datasheet view, while the report is previewed or printed, or while the query is run. Although users can read the records, no one can edit, add, or delete any records until the form is closed, the report has finished printing, or the query has finished running.
Edited Record 2 (Forms and queries only) A page of records is locked as soon as any user starts editing any field in the record and stays locked until the user moves to another record. Consequently, a record can be edited by only one user at a time. This is also called "pessimistic" locking.


You can set this property by using a form's property sheet, a macro, or Visual Basic.

Note   Changing the RecordLocks property of an open form or report causes an automatic recreation of the recordset.

Remarks

You can use the No Locks setting for forms if only one person uses the underlying tables or queries or makes all the changes to the data.

In a multiuser database, you can use the No Locks setting if you want to use optimistic locking and warn users attempting to edit the same record on a form. You can use the Edited Record setting if you want to prevent two or more users editing data at the same time.

You can use the All Records setting when you need to ensure that no changes are made to data after you start to preview or print a report or run an append, delete, make-table, or update query.

In Form view or Datasheet view, each locked record has a locked indicator  in its record selector.

Tip   To change the default RecordLocks property setting for forms, click Options on the Tools menu, click the Advanced tab on the Options dialog box, and then select the desired option under Default Record Locking.

Data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.
monfuAuthor Commented:
I have already seen this on the Microsoft site.  The problem is that the database is DATAFLEX and not MSACCESS!  so this command does not work!  

Also I cannot do autoincrement since the Dataflex database is not designed like that!
PoppekopCommented:
OK so can you tell me how you get the NextCall in rsInsertCall("Call_no") = NextCall
monfuAuthor Commented:
Oh I see, that was just for testing purposes, I gave NextCall a hardcoded number.  I basically bring nextcall like this:-

rsInsert = cnnDFlex.Execute("SELECT max(call_no) + 1 NextCall FROM xersercl")

Sorry if I misleaded you
1WilliamCommented:
Create a table, name the table 'tblLastCallID'
One field, LastCallID

In a recordset, you open the table, get the current value.  Add 1, Save the value in a variable.  Update this table, close the recordset.
now you have the 'next Call ID' as a varaible.  Other users may now get the next Call ID
Using this variable, assign it to the field in your table that contains the call info
monfuAuthor Commented:
I cannot manipulate the Dataflex table!!!  It is used by another application.  I have to lock it in some way
1WilliamCommented:
I am not thinking you need to do anything to any table.  Create a new table as I described Date: 08/28/2003 07:54AM PDT
PoppekopCommented:
can you put the whole code here? That might give us an idea if maybe you loose some time getting the max value.

I think williams solution will get you more speed in getting the next value and that might solve your probs
monfuAuthor Commented:
Wel the code is at work now cause I am at home.  I will continue tomorrow

thanks for all your help
1WilliamCommented:
Thanks, Poppekop.  sometimes simpler is better.  :)
PoppekopCommented:
william:

KISS

oops, no not what you think...

Keep
It
Short &
Simple

:)
is it friday yet???
1WilliamCommented:
Hehehe  Almost!

People use that expression with me all the time, but I think they mesn:
Keep
It
Simple,
Stupid
PoppekopCommented:
so what, I'm polite;-)
monfuAuthor Commented:
Good morning Poppekop,

Yesterday you asked for my code, so here it is:-

Set rsInsert=Server.CreateObject("ADODB.Recordset")
rsInsert.ActiveConnection = cnnDFlex

rsInsert = cnnDFlex.Execute("SELECT max(call_no) + 1 NextCall FROM xersercl")
NextCall = rsInsert("NextCall")

Set rsInsertCall=Server.CreateObject("ADODB.Recordset")
rsInsertCall.ActiveConnection = cnnDFlex

rsInsertCall.Open xersercl, cnnDFlex, adOpenKeySet, adLockOptimistic, adCmdTable
rsInsertCall.AddNew
rsInsertCall("Call_no") = NextCall
rsInsertCall.Update

At the moment I am only trying to insert one field for testing purposes, cause if this works then the others will work.  The problem that I am getting is with this line:-

rsInsertCall.Open xersercl, cnnDFlex, adOpenKeySet, adLockOptimistic, adCmdTable

I am getting this error:-
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


I am assuming that the connection that I am using to connect to the DataFlex database does not support this

Any help is very much appreciated since at the moment I am lost!

Thanks

monfuAuthor Commented:
I also thought about Williams post.  Well if I had to deal only with my Access database, that would not be a problem.  But imagine, I create this table with the CallID, and insert it into my Access database.  No problem.  But what if, when I am inserting this number into the Access table, a customer called on the phone, and requested a service?  then the administrator of the Dataflex system will enter a new value in the Dataflex table, with the new call number, which is the call number + 1 and that might conflict with the call number I have in the access table.  I hope I am making myself understandable.

Thanks for your help
PoppekopCommented:
I'm not sure about the dataflex stuff because I have never worked with that.

However, you can use commands, so you could try to use an Insert SQL string  command to insert your new record (I'm not sure about the SQL though)


Set rsInsertCall=Server.CreateObject("ADODB.Recordset")
rsInsertCall.ActiveConnection = cnnDFlex

Set rsInsert=Server.CreateObject("ADODB.Recordset")
rsInsert.ActiveConnection = cnnDFlex

rsInsert = cnnDFlex.Execute("SELECT max(call_no) + 1 NextCall FROM xersercl")
NextCall = rsInsert("NextCall")

rsInsertCall = cnnDFlex.Execute("SELECT INTO xersercl ( Call_no ) SELECT " & NextCall & " AS Call_no")
monfuAuthor Commented:
I know that you can use and sql instead of a recordset insert.  The problem is not the insert clause but how do I lock the database before doing the insert!
PoppekopCommented:
I thought you said that this line gave you the problem?

rsInsertCall.Open xersercl, cnnDFlex, adOpenKeySet, adLockOptimistic, adCmdTable

anyway, in your code you first determine the maximum value and the set create a recordset and open a recordset and then add that value. If you get the maximum value after you have opened the recordset, you will have a lot less chance that anyone else has gotten "your" value. It is not fullproof, but it will improve a lot.



Set rsInsertCall=Server.CreateObject("ADODB.Recordset")
rsInsertCall.ActiveConnection = cnnDFlex

rsInsertCall.Open xersercl, cnnDFlex, adOpenKeySet, adLockOptimistic, adCmdTable

Set rsInsert=Server.CreateObject("ADODB.Recordset")
rsInsert.ActiveConnection = cnnDFlex

rsInsert = cnnDFlex.Execute("SELECT max(call_no) + 1 NextCall FROM xersercl")
NextCall = rsInsert("NextCall")

rsInsertCall.AddNew
rsInsertCall("Call_no") = NextCall
rsInsertCall.Update

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PoppekopCommented:
PoppekopCommented:
oops, second one seems offline
monfuAuthor Commented:
Ok you are rigth but does it make that much difference?  I mean I need to lock the database to be 100% fullproof
1WilliamCommented:
Maybe I had misunderstood.  No all users entering data will be able to use the Access database?
monfuAuthor Commented:
No all users entering data will get directly into the Dataflex database
1WilliamCommented:
So there is nothing in this Q that really pertains to Access, you need help working on making this happen in dataflex?
monfuAuthor Commented:
Exactly
PoppekopCommented:
"Ok you are rigth but does it make that much difference?  I mean I need to lock the database to be 100% fullproof "

I'm just an access person with some common knowledge, don't know much about dataflex, trying to find you pointers to get this working.

And it may not be full proof, but it may save you the split second that causes the problem.
1WilliamCommented:
I have to ask, then, how can an Access developer help you on the problem?  Although Poppekop has some ideas.....

Whattya think, Poppekop ?
monfuAuthor Commented:
Well Poppekop, I am an Access person too, and that is why I tried to find some help here!  I have no idea on what to do and you know when a person is desperate :)  I have posted in about 10 Forums with no reply whatsover and this is the only forum that tried to help me out.

I understand that you might not have the reply to my answer, since it is quite a difficult thing.
PoppekopCommented:
did you try this link?
http://www.starzen.com/dffaq/DataFlexFAQ.html

I searched this forum for dataflex, but didn't see any experts:(

Hey the access forum here is pretty overactive with replying, I always get answers within ten minutes:)
1WilliamCommented:
Not knowing a single thing about dataflex, I am going to bow out.  Best of luck, guys.
monfuAuthor Commented:
I tried the link Poppekop, no luck but
monfuAuthor Commented:
Yeah I already saw that article.  If I am not mistaken it says that there is no need to lock the table, however the guy at Dataflex told me that there is the need.

I already did a search in Google on database locking in Dataflex, no anwers!
PoppekopCommented:
try this sql statement to search for in Google

SQL_CONCUR_LOCK

if should be a way to lock tables in dataflex, but I'm not sure how to use it
PoppekopCommented:
can't the guy at dataflex tell you how to lock it?
monfuAuthor Commented:
Well he uses a special command for Dataflex, which obviosly does not work on Access.  What I did is I sent an email to the ODBC Driver provider, maybe he has an idea on how to do it.

Thanks for all your help
PoppekopCommented:
you're welcome:) it was a nice challenge to all the creative update queries I write for this datamigration project.
PoppekopCommented:
did you ever get it working?
monfuAuthor Commented:
Well basically what I did was through ASP.  I am doing it through a recordset and its being locked from the dataflex people automatically.  Cannot lock a Dataflex record thorugh ASP though
Karen FalandaysTraining SpecialistCommented:
This question has been abandoned and needs to be finalized.
  You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
kfalandays cleanup volunteer
Karen FalandaysTraining SpecialistCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept answer from Poppekop

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

kfalandays
EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.