Using AutoNumber on a Split Access database

[murphy123]

Hi,
I created a split database that has a ContactID# set up as autonumber, however, I have about 20+ users (leaders) taking calls from agents logging in calls. I sent the users a copy of the front end database and it worked for about 1 hour. Then error message started to pop up stating "Unrecognized database format" then database path...
Some users were unable to log in. I had to get everybody out.

I have a split database working great with the same amount of users connected. This database has the same format as the one I just created but the AutoNumber.

I refreshed the table link manager in the Front/Backend databases, and the database with the autonumber is working fine now.

My question is, will I have problems having an autonumber field on a split database sent to several users? Are there other options I can use in oder to avoid this issue?

I am not too savy with Visual Basic or SQL

Sorry for writing so much. Any help is appreciated it.

 

[Remou]

I think you need to read:
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184

 

[murphy123]

Hi Remou,

Thanks for the information.

I have another question though, can I have more than 10,000 records set up monthly? Is there a limit?

Last month alone I had almost 30,000 records (calls) logged in.

Let me know,
Thanks.

 

[Remou]

The answer is 'that depends'. I have worked with databases with more that 75k records and others here speak of higher numbers. Microsoft says '2 gigabytes minus the space needed for system objects', but you can link tables (

http://office.microsoft.com/en-us/access/HP051868081033.aspx).

Unless the records are very long, 10,000 does not sound impossible. You may wish to look at MS SQL at some stage, this forum:
Microsoft SQL Server: Setup and Administration Forum, forum962
Would also be a good place to post any questions about upsizing.

 

[murphy123]

Remou,

Thanks again for all of your help.

 

[Remou]

Tut. That should be 750k, not 75k.

 

[murphy123]

Hi again,
I am sorry for bringing this question up again... I was trying to use the code from the FAQ700-184 and I believe I am going to run into a problem. By checking the code on the FAQ it seems to me that a user won't be able to start a record if another one is open at the same time until the first one is finished. My issue is that my users are taking call after call and they can't wait for a user to release the next ID to be able to log their calls.

I also tried to follow the code from the O'Reilly Access cookbook, but I am getting compiling errors. I believe maybe by using that code I could stop a record from being locked by another user because the agent's last name in addition to the autoflex number would be unique.


I am getting a compile error. Is DAO available with Access 2000? Can someone help me out?

My main table has:

CONTACTID, LEADER, AGENT ....

This is what I have on the VB form

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Try to get unique counter and write it
'to the CONTACT ID field

Dim lngCOunter As Long

If IsNull(Me.CONTACTID) Then
lngCOunter = acbGetCounter()
'If not counter is available...
If lngCOunter < 1 Then
'cancel the Update event
Cancel = True
Else
'Write the key field
Me.CONTACTID = Left(Me.AGENT, 5) & lngCOunter
End If
End If
End Sub

Public Function acbGetCounter()

Dim lngCOunter As Long
lngC0unter = acbGetCounter()
If lngC0unter > 0 Then
Me.KeyField = Left$(Me.AGENT, 5) & lngCOunter
End If
'Get the value from the counters table
'uncrement it

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim blnLocked As Boolean
Dim lngTime As Long
Dim lngCnt As Long
Dim lngCOunter As Long

' Set numbers of retries
Const conMaxRetries = 5
Const conMinDelay = 1
Const conMaxDelay = 10

On Error GoTo HandleErr

Set db = CurrentDb()
blnLocked = False


Do While True
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", -dbOpenTable, adDenyWrite + DenyRead)
If Err.Number = 0 Then
blnLocked = True
Exit For

Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries
On Error GoTo HandleErr

If Not blnLocked Then
If MsgBox("Could not get a ContactID: Try Again?", _
vbQuestion + vbYesNo) = vbYes Then
intRetries = 0
Else
Exit Do
End If
Else
Exit Do
End If
Loop

If blnLocked Then
lngCOunter = rst("CounterValue")
acbGetCounter = lngC0unter
rst.Edit
rst("CounterValue") = lngC0unter + 1
rst.Update
rst.Close
Else
acbGetCounter = -1
End If
Set rst = Nothing
Set db = Nothing

ExitHere:
Exit Function

HandleErr:
MsgBox Err.Number & ": " & Err.Description, , "acbGetCounter"
Resume ExitHere
End Function

For intTries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = True
Exit For
Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries

 

[Remou]

As far as I recall, with FAQ700-184 a small table is locked for a moment while a new key is created. You need to add a reference to DAO. You will find it in the code window under Tools->References: Microsoft DAO 3.x Object Library.

 

[Remou]

PS I believe MichaelRed responds to comments regarding the FAQ, you will find a link on the FAQ page, if you wish to comment.

 

[MichaelRed]

thanks to Remou. his comments are correct. The smaple code has been used in 'production' for a small call center application with no noticable delay in data entry. I do not believe it ever got to 75 simultaneous users, but It was commonly at about twenty and occassionally a bit higher than that.

I never tried to actually time the module execution time, so cannot analyze potential impacts due to collisions/conflicts. In the small call center application it was originally developed for, there were NO collision/conflict issues in generatiing the values for the period I was involved in the specific application (sllightly more than a year) I believe that 75 active data entry users would experience other issues before the unique number procedure (FAQ700-184) would be noticable.




MichaelRed

 

[murphy123]

Thank you both for the information. I have about 35 people max using the database at the time. I guess I won't have a problem implementing it then.

Thanks.