BE dbase asking to be repaired 1

[Epsilon101]

HI,

I have a front end- back end dbase setup.

The back end is locking everyone out and asking to be repaired, on average of 1 time every 2 days.

Having to get everyone to come out before i can get exclusive access to the BE to repair it is also very tedious.

I have no idea what is causing the BE to want repairing, i compact it after repair as well, just incase.

I was looking around the TT forum at some posts, i noticed settings in Tools>Options>Advanced for record locking.

Mine was defaulted to 'no record locking', and 'use default record locking' is ticked, just wondering if changing the setting to 'edited record locking' would stop this happening???

Things i think might be causing record locking issues..
just a guess..

I have a vba function on a form command button
this function takes the info on the form, and saves it to the table TblLeadInfo, it then takes the customerID autonumber shown on the form that has been created for the new record going into TblLeadInfo, and copies it into 3 other tables, so that those 3 tables are linked to TblLeadInfo.

The reason i did that, is because i needed records alrdy created for the customer, but with no data in it.

I have another vba function that opens a recordset checks to see if a customername already exists and then closes it.

And also 3 forms that open queries, via recordset to do a recordcount to check if there isnt any there that it sends an error message to say none where found.

All not too complicated.

I can explain further if i havent given enough detail, just ask thanks for any help on this

---------------------------------------

Neil

 

[willir]

Hi Neil

Okay, sounds serious. You obviously have a shared network solution...
- How many users on the system?
- Is there one area they focus update activity? For example, you have a sales department with a high volume of sales.
- Do you use autonumber for high transaction acitivty?
- Do you implement any strategies for preventing record locking? For example, using unbound forms and populate the form for each record as the end user proceeds to the next record.
- Do users report other problems? Lost records, error messages, long times to advance to the next record or loading forms, combo boxes / list boxes that are empty?

Richard

 

[Epsilon101]

Hi Willir,

sorry to be a pain but im not sure of some of the terminology.
Ill explain what ive got and we can go from there:
i have 15-20 users for the dbase.

They are split into groups for updating the dbase.
Admin does most of the dbase work.
The other two groups are sales and UW(this one i cant name).

Admin creates the customer, this is a new record created in TblLeadInfo, and a new record created in 3 other tables that are linked to TblLeadInfo via a foreign key, which is the customerID <autonumber>

Sales and UW add further details to the customers information at a later date.

Not sure about record locking strategies, my forms are bound,

the main customer details form, is bound to TblLeadInfo, and has 3 subforms in a tab control for the other tables.
This form is only used to view nothing can be edited there.

I have 4 buttons which can only be used depending on the persons access group, admin,sales,uw.

The buttons link to forms that can be used to edit the information in the customers record.

The only error i have seen so far and its happened about 3 times.

I have gone into the back end and seen that a customer has been created in TblLeadInfo, but not in the other tables.

So when you go to the customer details form, no text boxes are showing in the subforms.

I have asked about this to the users, but none of them has said anything.

Each time i have found this error, the customer has been there twice, i think its just cos the user noticed an error and created the customer again, but no one has brought it to my attention.

As for the back end error..

I checked this thread thread181-855255

and it was a similar problem,

the dbase was asking to be repaired but saying someone was alrdy in it, I looked at the .ldb file in notepad and it was myself.

So i made a copy of the dbase with different name, repositioned the dbase in the server to different location and then opened it, it let me repair it then, and then i deleted the existing dbase back end and the ldb and moved the newly repaired one back to the correct location, it now works again.

Thats sorted it for now.



---------------------------------------

Neil

 

[Epsilon101]

Here is the code im using for creating the new customer.

I havent used an access .mdw file to create workgroups for my users, i tried it but the network is setup in such a way that i couldnt create shortcuts for everyone, to reference to MSACCESS.Exe, the mdb, and the workgroup file.

So instead, i made a user details table, and gave each person a accessgroup eg. sales, admin. Then used a global variable when not allowing them into certain forms.

Its tedious but only way i could think of doing it.


This one is first to check the customer exists or not and ask if they want to create them or view existing.

From Form - FrmCreateNewCst

Private Sub CmdCreateNew_Click()
On Error GoTo Err_CmdCreateNew_Click

Dim rcd As Recordset
Dim MyMsg
Dim MyMsg1
Dim StrSQL As String

StrSQL = "SELECT TblLeadInfo.DateOfLead, TblLeadInfo.CstFirstName, TblLeadInfo.CstLastName, TblLeadInfo.ProposalNumber, TblLeadInfo.Introducer FROM TblLeadInfo WHERE [CstLastName] = '" & Me.TxtLastName & "' AND [CstFirstName] = '" & Me.TxtFirstName & "';"
    If IsNothing(Me![TxtFirstName]) Then
        StrSQL = "SELECT TblLeadInfo.DateOfLead, TblLeadInfo.CstFirstName, TblLeadInfo.CstLastName, TblLeadInfo.ProposalNumber, TblLeadInfo.Introducer FROM TblLeadInfo WHERE [CstLastName] = '" & Me.TxtLastName & "';"
    End If
    
    If IsNothing(Me![TxtFirstName]) Then
        MyMsg = MyMsg & vbCrLf & " *First Name"
    End If
    
    If IsNothing(Me![TxtLastName]) Then
        MyMsg = MyMsg & vbCrLf & " *Last Name"
    End If
    
    If Len(MyMsg) = 0 Then
        Set rcd = CurrentDb.OpenRecordset(StrSQL)
        'If none found then say
            If rcd.RecordCount < 1 Then
                MyMsg = MsgBox("No Customers meet your criteria, would you like to create one", vbYesNo, "LP Dbase")
                    If MyMsg = vbYes Then
                        rcd.Close
                        DoCmd.OpenForm "FrmEnterNewLeadInfo"
                    Else
                        rcd.Close
                        Exit Sub
                    End If
            Else
                MyMsg = MsgBox("There are customers meeting your criteria, would you like to view them?", vbYesNo, "LP Dbase")
                If MyMsg = vbYes Then
                    rcd.Close
                    DoCmd.OpenForm "FrmNewCstListings"
                Else
                MyMsg1 = MsgBox("Would you like to create a new customer?", vbYesNo, "LP Dbase")
                    If MyMsg1 = vbYes Then
                        rcd.Close
                        DoCmd.OpenForm "FrmEnterNewLeadInfo"
                    Else
                        rcd.Close
                        Exit Sub
                    End If
                End If
            End If
            
        
            
    Else
    MyMsg = "The below fields are missing:" & vbCrLf & "Please complete" & vbCrLf & MyMsg
        MsgBox MyMsg, vbCritical, "LP Dbase"

    End If
Exit_CmdCreateNew_Click:
    Exit Sub

Err_CmdCreateNew_Click:
    MsgBox Err.Description
    Resume Exit_CmdCreateNew_Click

End Sub

This one is to then take the information from the form and create the new records in the 4 tables.

From Form - FrmEnterNewLeadInfo

Private Sub CmdCreateCst_Click()
On Error GoTo Err_CmdCreateCst_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim MyMsg
    Dim MyMsg1
    Dim dbs As Database
    
            If IsNothing(Me![TxtDateOfLead]) Then
                MyMsg = MyMsg & vbCrLf & "  *Date of Lead"
            End If
            If IsNothing(Me![TxtCstFirstName]) Then
                MyMsg = MyMsg & vbCrLf & "  *First Name"
            End If
            If IsNothing(Me![TxtCstLastName]) Then
                MyMsg = MyMsg & vbCrLf & "  *Last Name"
            End If
            If IsNothing(Me![CboIntroducer]) Then
                MyMsg = MyMsg & vbCrLf & "  *Introducer"
            End If
            
            If Len(MyMsg) = 0 Then
                DoCmd.RunCommand acCmdSaveRecord
                Set dbs = CurrentDb
                dbs.Execute "INSERT INTO TblAdminDetails" & "(CustomerID) VALUES (" & Me.CustomerID & ")"
                dbs.Execute "INSERT INTO TblUWDetails" & "(CustomerID) VALUES (" & Me.CustomerID & ")"
                dbs.Execute "INSERT INTO TblSalesDetails" & "(CustomerID) VALUES (" & Me.CustomerID & ")"
                Set dbs = Nothing
            
                MsgBox "Customer has been saved", vbInformation, "LP Dbase"
                stDocName = "FrmCustomerDetails"
                stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
                DoCmd.OpenForm stDocName, , , stLinkCriteria
            Else
                MyMsg = "You can not save while the below fields are missing." & vbCrLf & "Please complete or close without saving:" & vbCrLf & MyMsg
                MsgBox MyMsg, vbCritical, "LP Dbase"
            
            Exit Sub

            End If

Exit_CmdCreateCst_Click:
    Exit Sub

Err_CmdCreateCst_Click:
    MsgBox Err.Description
    Resume Exit_CmdCreateCst_Click
    
End Sub

---------------------------------------

Neil

 

[Epsilon101]

Oh another error thats occured, is that when the Dbase is open, the users cant close MSACCESS.exe from the x button at top right.

They used to be able to, i checked my startup settings with another older simpler dbase and they where the same.

The x button works on the old dbase but not on this one.

Bit strange, not sure what option effects this, i might of changed it and just forgotten.

---------------------------------------

Neil

 

[willir]

Neil

Review...
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number) faq700-184 by MichaelRed

which is the customerID <autonumber> ... Each time i have found this error, the customer has been there twice, i think its just cos the user noticed an error and created the customer again...

Autonumber, although convenient, is problematic for a mutltiuser system, especially if used for transaction systems. (I would consider sales transaction because this is an on-going process; creating a customer is not since the frequency of creating a new customer is considerably less)

When a "system" creates an autonumber or sequential number, it can create the number either at the start of the insertion, or just before the update. Access creates the autonumber at the start as soon as the first character is typed.

Try this...
On one computer, start creating a customer. Do not update / commit the record. Take note of the autonumber. Now go to a second computer, and start creating a second customer. Take note of the autonumber. Chances are the autonumber for both in-progress customer records will be the same! ...because Access assigned the next autonumber, but since the autonumber has not been updated, the value remains the same.

The first record to be updated will be successful, but the second cusomter record may not be successful. Any related records will then have problems because the foreign key will be messed -- meaning that, depending on the data entegrity (enforced or not), you may create supporting records that will be bound for the first successful update event though they were to be assoicated with the second customer.

Your first action item would be to change the way you assign the primary key for the customer.

Note: Before doing anything, backup your database (back end), and test anything before implementing the change in production.

MichalRed's code has been well accepted. However, you may "get away" with using "random" instead of "sequential" autonumbers since I suspect you do not create customers all that often. ...BUT, random numbers will be large, and can be negative -- may be a shock to people if the autonumber is visible, and there is no gurantees. (You can change sequential to random via the design view by clicking on the "sequential" value and selecting "random" from the pikc list.)

I would be also concerned about your sales records in how the unique primary key is developed here.

...Moving on
Strategies for implementing a client server application with Access can be extensive. You can buy books on this topic that can provide better info than I can, and you may even consider hiring a consultant or programmer.

- Use an unbound form. The form has various text boxes, even combo or list boxes. Then, you use a record set to retrieve or walk through your data. The retrieved data is then displayed to the unbound fields. This tactic requries the use of VBA code. Any updates are used to update the table, again using code. This way, you minimize when records are locked, and minimize conflicts. This approach also can reduce network activity since only the required data is transferred. BUT this approach requires a lot of work.
- Default settings... From the menu, select "Tools" -> "Options" -> "Advanced" tab. Ensure default oepn mode set to "Shared", Default recording locking set to "Edited record".
- Already talked about have a strategy on autonumbers.
- Create an error routine that captures errors in a table / file for later review. Right now, you just know there is a problem because of the repair process having to be frequently run.

Richard

 

[Epsilon101]

Thanks for this Richard

I think this is going to be whats causing my problem,
ill look into doing an error routine to keep a table of errors that occur, to see if anything else is happening.
Ill need to buy a book on Access too.

I need to ask what will happen if i change record locking from 'No records' to 'edited records' what will change.

I remember that it locks out a 2kb page of records that are next to each other when it locks one.

I have it on 'No Locks' at the moment, does Access keep a log of changes to record when more than one person is editing it?? so that when the first person saves, any changes the second person makes and saves overwrites the first persons changes.


---------------------------------------

Neil

 

[willir]

Neil

I need to ask what will happen if i change record locking from 'No records' to 'edited records' what will change.

You can actually set record locking on the form which will apply to the specific form only. Look on the Data tab of the Properties window for the form -- RecordLocks. (You can also set the lock via VBA or macro)

This will allow you to test the change yourself -- it would be hard for me to recommend to specifically use tje EditedRecord option since I have no idea of the volume of activity and design.

And yea, the "EditRecord" = "Locking a Page" thing is a pain. ...But Access 2000 and newer does support RecordLevel and PageLevel record locking. On the same (Options) Advanced tab - Check / enable "Open databases using record-level locking". Per Access Help, they indicate PageLevel locking is the norm, but select the check box and hit F1 -- the popup help states...

Select to make record-level locking the default for the current database . Clear to make page-level locking (which locks all records on the disk page) the default. If you select this check box, record-level locking becomes the becomes the defualt behavior for access to data through a form, a datasheet, and code that uses a recordset object to loop through records, but not through action queries, or through code that performs bulk operations by using SQL statements.

It is important to install Access / Office service pack. There is a know poroblem with record locking in some situations...
ACC2000: Access Database Does Not Use Record-Level Locking When Started from a Windows Shortcut

Richard

 

[Epsilon101]

Thanks again Richard,

Will test that out and change it, great how they have setup record level locking and yet it doesnt even work properly without the service pack. I cant even install anything here so im just hoping they already have it as a MS update, if MS even have online auto updates for Office, i cant remember.

---------------------------------------

Neil

 

[Epsilon101]

Bit of a silly question here Richard but got to ask because i cant see it on Access help.

When you change an autonumber field to 'Random', does it make sure it doesnt create any duplicates.

Logical guess would be Yes, but it doesnt actually confirm it.

---------------------------------------

Neil

 

[willir]

Not a silly question Neil. I am not sure to be honest, but the odds are extremely small. But note that the random numbering only starts at the point from when you make the change -- it does not re-calculate random numbers for existing sequential numbers.

 

[Epsilon101]

Yea noticed it would do that, thats fine just hope it doesnt create the same ID number twice thnx Richard

---------------------------------------

Neil