Raymond_India
Board Regular
- Joined
- Nov 7, 2008
- Messages
- 68
Hi all,
I have 4 client sheets used by Users A, B, C and D which has 5 fields namely - Yr, Mnth, Time_Capture, Date_Receipt and Amt_Rcvd
User A,B,C & D would fill in the information in their respective sheets and click an UPDATE button which would then open a common database file (Chq_Log.xls) on a shared file location (S:\2017\Monthly\January\) and push the respective data captured by the users into the DATABASE sheet using ADODB method.
This works fine if the users click the button at different time intervals, however if they click the button at almost the same time (chances are high because in reality there are around 45 users doing this activity) then there is a high chance that data might not been written into the sheet because of the delay in the sheet being closed.
What i want to achieve is to be able to find out is that If the destination sheet is open, how can we identify the same and display a message box to try updating after some time.
Please assist.
I have 4 client sheets used by Users A, B, C and D which has 5 fields namely - Yr, Mnth, Time_Capture, Date_Receipt and Amt_Rcvd
User A,B,C & D would fill in the information in their respective sheets and click an UPDATE button which would then open a common database file (Chq_Log.xls) on a shared file location (S:\2017\Monthly\January\) and push the respective data captured by the users into the DATABASE sheet using ADODB method.
This works fine if the users click the button at different time intervals, however if they click the button at almost the same time (chances are high because in reality there are around 45 users doing this activity) then there is a high chance that data might not been written into the sheet because of the delay in the sheet being closed.
What i want to achieve is to be able to find out is that If the destination sheet is open, how can we identify the same and display a message box to try updating after some time.
Code:
Sub PIA()
'****************************************
'PUSH INTO DESTINATION SHEET USING ADODB
'****************************************
Dim stRs As ADODB.Recordset
Dim stCon As ADODB.Connection
Dim workbookvar, monthVar As String
Dim stsql As String
Dim strMonthFolder As String
Dim str_This_Year, str_This_Month, str_Time_Now, str_Date_Rcvd, str_Amt_Received As String
workbookvar = "S:\2017\Monthly\January\Chq_Log.xls"
monthVar = "DATABASE"
str_This_Year = Sheets("Source_Data").Range("A1").Value
str_This_Month = Sheets("Source_Data").Range("A2").Value
str_Time_Now = Sheets("Source_Data").Range("A3").Value
str_Date_Rcvd = Sheets("Source_Data").Range("A4").Value
str_Amt_Received = Sheets("Source_Data").Range("A5").Value
'Create the connection string
Set stCon = New ADODB.Connection
With stCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open workbookvar ' OPENS THE DATABASE SHEET IN THE PATH "S:\2017\Monthly\January\Chq_Log.xls"
'****** THIS IS WHERE I WANT THE CHECK TO BE PUT IN PLACE AS TO WHETHER THE DATABASE SHEET IS
' ALREADY OPEN OR NOT AND THEN DISPLAY A MSG AND EXIT SUB
' IF THE WORKBOOK IS ALREADY OPEN THEN
'MsgBox "THE DATABASE SHEET IS CURRENTLY IN USE, PLEASE TRY LATER !"
'exit sub
'Else
stsql = "INSERT INTO [" + monthVar + "$]"
stsql = stsql + " (Yr,Mnth,Time_Capture,Date_Receipt,Amt_Rcvd) "
stsql = stsql + "VALUES ('" + str_This_Year + "','" + str_This_Month + "','" + str_Time_Now + "','" + str_Date_Rcvd + "','" + str_Amt_Received + "')"
Set stRs = Nothing
Set stRs = New ADODB.Recordset
stRs.Open stsql, stCon
Set stRs = Nothing
.Close
'end if
End With
End Sub
Please assist.