Access/SQL DB and record locking. (1 Viewer)

[rothjm]

rookie needs advice

I have recently created an access front end and sql backend help desk app (multi-user environment) for my company. It seems to be working great, except:

When you open the app it opens to a new record (requested feature). The app doesn't save until they have completed the data entry and click on a button (like new rec, print, etc). My problem is when two people open the app they may be on the same record and then the second person will get an error when he submits the entry. Like if one person opens the app and the next record is record 200. Then the next person opens the app, meanwhile the first person is entering data on record 200 but have not saved to the database, the second person also opens to record 200 because the database thinks that is still the next available record. What do you advise to solve this problem? Should I save the record as soon as something is entered into the first field? I am uncertain on how to go about correcting this problem. I don't want a bunch of blank records because the user opened the database but did not enter any data.

I'll take any advice. TIA.

Jeff

 

[jaydwest]

Access & SQL

You might try using unbound forms in MS Access. Create a form in Access but make all fields unbound. Load the Data on the form from SQL Server using a Stored Procedure. Then on the Save button update SQL Server. This will only lock the SQL Server record for a fraction of Second.

USing this approach takes a little longer to create the form, but Stored Procedures are blazing.

Good Luck

 

[Pat Hartman]

You are obviously assigning your own sequence number rather than using an autonumber. With an autonumber, Access takes care of this problem.

Your code needs to handle the duplicate problem because no matter what technique you settle on, including the unbound form suggestion, there is always the possibility that there is a user a split second ahead of you. Certain techniques can minimize the problem. One is to not assign the sequence number until the BeforeUpdate event of the form. This is the last possible moment and it is less likely (although not impossible, so you still need to code for it) to run into a duplicate. Your current method of assigning the number when the form is opened is the most likely to encounter duplicates.

Add an error trap for the duplicate key error, get the next available sequence number and attempt the save again. You can limit the loop to some arbitrary number of tries or make it infinite.

 

[jaydwest]

Access & SQL Server

When using unbound forms with Stored Procedures you do not have to worry about the Sequence Number (PrimaryKey or Identity Field in SQL Server). Unlike Access which assigns the Autonumber the instant you START editing the record, SQL Server automatically assigns the Identity field only at the instant the record is stored. If needed, you can return value to Access using the @@Identity Parameter in your Insert Stored Procedure.

Because SQL Server keeps track of all this for you, you will not need to worry about the possibility of duplicate primary Keys. SQL Locks the record being added while it is being Inserted. The next insertion will get the next Primary Key (Identity field in SQL Server).

 

[rothjm]

I am using autonumber in SQL.
I have a PK on an identity field that increments by one.
That is not the problem.
That increments when two people are on at the same time, as soon as they hit a key I see it increment.
My problem is in access.
The record number (different than my pk) the form is sitting on, if they can't save...it says the save action has been cancelled if more than one person is sitting on the same record (row).

I believe what you are saying, Jaydwest, will work. I would just have to make a lot of changes and my coding isn't that strong.

 

[jaydwest]

Access & SQL Server

What is the exact error message you are getting. There is a problem when working with SQL Server that sounds somewhat similar to the problem you are having. SQL will not let you insert a record and gives a message similar to the one you describe if you have a Bit field (Yes/no) in your recordset that is not initialized. You must initialize all values in all existing records and set the default in SQL Server. SQL Server will not let you add a record with a null bit field.

If you use an unbound form, you eliminate the contention issues. Yes this is a lot of coding, but the forms are blazing after you do it. If you plan to work with SQL Server, I suggest you bite the bullet. Once you have mastered it, it's not too bad.

If you want, I can send you some sample stored procedures and the VBA code to use them.

 

[rothjm]

It just says the save action was cancelled.

I do have a bit field.
It is on my form as a check box and it is not enabled.
I flag it when the "receipt" is printed.

So you are thinking I don't have it initialized?
Do you think I need to set the variable to false just after I declare it?

Here is a piece of that code.


Dim varReceiptFlag As Boolean

varSite = [Site] 'Set variable equal to the current records site

stDocName = "qryReceipt" 'Set object
DBID = [ID] 'Set DBID with the database id to use to append to the filename

'MsgBox "Site: " & varSite
varReceiptFlag = True
[ReceiptFlag] = varReceiptFlag 'Set Receipt Flag on the main form to true
varReceiptFlag = False 'Set variable back to false


I would like to see your sample stored proc etc.

Thanks again for the help.

 

[jaydwest]

Access & SQL Server

Try writing a query to update the bit field to either true or false in all existing records in SQL Server. Then goto SQL Server Enterprise Manager and open the Table and set the default property to 1 or 0. SQL Server uses 1 or 0 instead of true (-1) or False (0) in Access.

See if this doesn't solve your problem.

 

[Kevin_S]

jaydwest,

I know this is bad forum ettiquette to jump in on this thread for personal reasons but I also develop SQL Server 2000 be/ MS Access XP fe applications and have had similar problems as to those descibed in this thread. Would it be possible for you to post some of your stored procedure / VBA examples as you stated above - or - possibly e-mail an example? My coding skills are adequate but my familiarity with stored procedures is limited (especially in regards to using them in conjunction with forms).

Thanks in advance...
Kev