Lock Type and Cursor Type - To update and to extract data from Access to Excel

[mks123]

Hi All,

I have developed one Tracker ( in MS Excel) which will be using by around 40 people at same time basically only for to add records in Table (MS Access Database). so which is the best Cursor Type and Lock Type.



I'm using ADO.


I think C.T. "adopenkeyset" and L.T "adLockPessimistic" would be right type, but still not sure ?


Subject - Lock Type and Cursor Type - To update and to extract data from Access to Excel

Thanks!
Regards,
Mks

[CJ_London]

same time basically only for to add records in Table

if they are only adding data, I wouldn't think locking matters since it only applies if you are changing an existing record

[mks123]

I'm using below code to add record to table named "tblSystemData", at same time around 30 people would be using it.

this works when single user use it.

trying to hard to figure it out, what is wrong with this code.

Option Explicit
Public Lpdata As ADODB.Recordset
Public Lpcon As ADODB.Connection
Public opath, ofilename, oextension As String
Public Accesstring As String
Public Const ReadOnlyDB = "PROD_Tracker Database"
Public Const DefultDB = "PROD_Tracker Database_be"




Sub Connection_data()




Dim opath As String
opath = Sheet2.Cells(1, "G").value


On Error GoTo ExitPnt
ofilename = opath & DefultDB
oextension = ofilename & ".accdb"


Set Lpcon = New ADODB.Connection




Accesstring = "Provider=Microsoft.ACE.OLEDB.12.0;" _
              & "Data Source=" & oextension & ";" _
              & "Persist Security Info = false"
Lpcon.ConnectionString = Accesstring
Lpcon.Open


Exit Sub


ExitPnt:


MsgBox Err.Number & "---" & Err.Description


MsgBox "Please Contact Admin"
'Call JustStopTimer
ThisWorkbook.Close False


End Sub






Sub RetriveData(oSysStatus As String)
Set Lpdata = New ADODB.Recordset


Call Connection_data
On Error GoTo Exitpoint
With Lpdata
    .ActiveConnection = Lpcon
    .Source = "tblSystemData"
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
    .CursorLocation = adUseClient
    .Open
End With


With Lpdata
  .AddNew
      .Fields("User") = Environ("UserName")
      .Fields("USerName") = Application.UserName
      .Fields("Date") = Date
      .Fields("Time") = Time
      .Fields("FullTimeInfo") = Now()
      .Fields("Remark") = oSysStatus                                    '"Mukesh"
      .Fields("TimeID") = TimeID
      .Fields("ComputerName") = VBA.Environ("ComputerName")
     ' .Fields("ClientName") = VBA.Environ("ClientName")
  .Update


 End With


Lpdata.Close
Lpcon.Close
Set Lpdata = Nothing
Set Lpcon = Nothing


Exit Sub
Exitpoint:


MsgBox Err.Number & "---" & Err.Description
'Call JustStopTimer
MsgBox "Please Contact Admin"
ThisWorkbook.Close False


End Sub

[CJ_London]

Not sure,

date and time are reserved words in access that might be causing some confusion.

You say it works with one user, what does that mean? many connected to the db and one can update and the others can't? in which case it is a permissions issue

[mks123]

let me explain in detail, excel macro(with userform) and access database saved on Shared Drive.currently around 30 people could use that excel macro same time to add new record in Access database in same table.

what happen when I use alone everything works fine and smooth, but the moment other user opens the excel macro and try to add new record to database system through the error "2147467259 Operation must use an updateable query".

what I' m try to say at same time only one user can update Database, If other use try to access both get error.


Regards,
Mukesh Y