How check read only status of a file using VBA

I'm just wondering if there is a way in VBA code to check the readonly status of an access.accdb.  I am using Access 2013.  

There are times when I put the database in readonly mode.  That is, I put a check in  the ReadOnly checkbox in the properties of the file, like you might using file explorer.

I am wondering if then in my code I can check to see what the read only status of my database is?
WoodrowAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I believe you want to check the Readonly status of your current database? If so:

If Currentdb.Updated Then
  Msgbox "Not Read Only"
Else
  Msgbox "Read Only"
End If
0
Dany BalianCTOCommented:
you can also check the db properties aswell..

CurrentDb.Properties("updatable").Value
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

WoodrowAAuthor Commented:
@Scott and Danny

I"m sorry to be so long in responding.  I had some other things come up.  With regard to your last two posts:

If you use commands like Current.Updated or CurrentDb.Properties("updatable") what is being checked?

As a result of Danny's first response I wrote code like that show below. Do the commands above do essentially the same thing or are they something different?

sFile = "C:\Users\Woody\Documents\AccessDBs\Testing_Sandbox_01.accdb"
    
    If IsFileReadOnly(sFile) = True Then
        MsgBox "File is Read Only"
    Else
        MsgBox "File is NOT Read Only"
        
    End If

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ...

If Currentdb.Updated Then

should be

If Currentdb.Updatable Then

Also ... the Updatable Property refers to DAO Objects being updatable. ReadOnly of the db itself is not the same per se.

So what you have here

If IsFileReadOnly(sFile) = True Then

.... well, where is IsFileReadOnly() defined?  Is that a FileSystemObject ?

Either way, you can certainly use the File System Object (FSO) to check this.  You can set a VBA Reference to the Microsoft Scripting Runtime which  will give you access to the FSO Object Model, which I use for all sorts of things.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, it should be Currentdb.Updateable - my bad.

ReadOnly of the db itself is not the same

Not quite sure what you mean by that. Currentdb.Updateable will tell you if the database has been opened in ReadOnly mode. You cannot add or change objects or LOCAL data in that case. You can change data in LINKED tables, of course, since you're not opening THAT database as read only. If you want to determine if those linked tables are opened in ReadOnly mode, then you'd have to check the Updateable property of that database, obviously.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, if I use FileSystemObject to check the file after I've opened it ReadOnly, the method I used does NOT report that it's read only. Here's what I did:

Function TestFile() As String

Dim fso As New FileSystemObject
Dim fil As File

Set fil = fso.GetFile("C:\Users\Scott\Documents\database10.accdb")

If fil.Attributes And vbReadOnly Then
 TestFile = "File is read only"
 Else
 TestFile = "File is not read only"
End If

End Function

Open in new window

I opened database10 as read only, using the Access interface - that is, I clicked Open - Computer - Browse, then located the file and selected "Read Only" from the drop down. When the database opened, it reported that it was opened in Read Only mode, and gave the standard warning about data and object changes.

After doing that, using VBA and checking Updateable showed the file was opened Readonly:

?Currentdb.Updateable

Returned "False"

However, the FSO method shown above does not return the attribute of vbReadOnly.

Perhaps I'm using FSO wrong, however. I don't do much with in VBA.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Per OP
"That is, I put a check in  the ReadOnly checkbox in the properties of the file, like you might using file explorer."

I'm assuming he means in Windows Explorer ?
So ... I not sure that setting is the same as explicitly opening the DB in ReadOnly mode ?

1
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"the method I used does NOT report that it's read only"
That is why I am saying they are not (I don't  think) the same.
0
WoodrowAAuthor Commented:
@Joe,

Yes, you're right.  I neglected to include the code for the ISFileReadOnly Function.  

I am thinking about all that's been said,  I need to digest it and will respond soon.

Thank you all for your help

Public Function IsFileReadOnly(sFile) As Boolean
     ' Test to see if the Read-only attribute was assigned to the file.

    If GetAttr(sFile) And vbReadOnly Then
        IsFileReadOnly = True
    Else
       IsFileReadOnly = False
    End If


End Function

Open in new window

0
Dany BalianCTOCommented:
well basically what differs is:
are you trying to open another database file and check if that file is readonly? or does your code need to know if the open database file is read only?

the isfilereadonly function actually checks for other files, however the currentdb.updatable property checks if the database where your code currently resides is readonly (beit because it's open as readonly or the file physically is set as read only in the file properties, or maybe even stored on a write protected disk)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just for reference and clarification:

"or the file physically is set as read only in the file properties, "
They are not really the same thing
Updatable
0
Dany BalianCTOCommented:
@databasemx i remember trying the updatable property on a database file that has a file property of readonly and it returned false. i will check it again when i'm on my pc
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... Scott confirmed that ... but he also found:

"Also, if I use FileSystemObject to check the file after I've opened it ReadOnly, the method I used does NOT report that it's read only."

So this is why I am saying they are not quite the same thing.
Again ... I am assuming the OP wants to know - via VBA - if the ReadOnly attribute of the db (as set in Windows Explorer) is True or False.
0
WoodrowAAuthor Commented:
To Clarify:

This is a split database,  I am interested in finding out if the the back-end is in a read-only condition.   I think using FSO is sufficient for my needs.  This application allows a user to (as it were) check-out the database, like you would a book at a library.  The application needs to  be taken off-site to a clients to gather information.  When this happens, the database is considered checked-out. The application may still be viewed at headquarters, but users are asked (at headquarters) not to edit information because when the database comes back from client information gathering, the headquarters database (specifically the back-end) is going to be over-written.  When the database is checked out the users at headquarters, at present, get a big yellow message telling them of the condition and asking them to refrain from data entry (for the reasons already mentioned).  It would be nice, however, if instead of asking them to refrain, you could prevent them from doing data entry, which is what I'm trying to accomplish, in the easiest way.

I think, thanks to this thread, I have enough information to do that, and it appears doing it with FSO logic will be sufficient.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Interesting for sure.  Similar in a way to a project I did a few years back indirectly for Lloyds of London via a law firm in San Francisco..

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't really need FSO, although you could use it if you like. Your IsFileReadonly function seems to work fine, and that's entirely VBA (no additional references needed, which is always good).

I set my file to Readonly in the File Properties, then opened it. When I run IsFileReadOnly, the result is True when the box is checked, and False when it's not.

So you'd have to supply the BE path to your function, and you would be able to determine if the file is readonly.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
That does seem to work. Simple enough.
Also, fwiw a Reference would not be necessary if you use Late Binding.
mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.