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.
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