Check if a file opened using ADODB is read only or not ?

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.

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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top