Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mark Multiple Databases as Read Only at one time with VB

Posted on 2013-11-22
15
Medium Priority
?
383 Views
Last Modified: 2013-11-26
Hello - What would code look like for me to set properties of multiple db's to Read Only at one time?  

For example, if I have a "Set Read Only" db that stored code I could run...and have it mark DB1, DB2, and DB3 as read only...?
0
Comment
Question by:Cynthia Hill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 5
15 Comments
 
LVL 13

Expert Comment

by:Surone1
ID: 39671288
just to be clear you want to set the read-only filesystem property of multiple .mdb files?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39672933
You can do this with one Attrib command, since it allows you to use wildcards.

You can easily invoke this with the Shell function or using the Wscript.shell object's Run method.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39673467
i would have suggested Scripting.FileSystemObject but work got in the way. if you need details on how to make either of those work let us know.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Cynthia Hill
ID: 39675339
Okay...that helps to get me going.  I've never modified file properties in VB before, so this function is new to me.  

Would the code look something like this (see below)?  Feel free to simplify if I've added in things that are not necessary.
Function ToggleReadOnly()
   Dim fso, f
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.GetFile(c:\DB1)
   If f.attributes and 1 Then
      f.attributes = f.attributes - 1
      ToggleReadOnly = "Databases are Read/Write."
   Else
      f.attributes = f.attributes + 1
      ToggleReadOnly = "Databases are Read Only."
   End If
End Function

Open in new window

0
 
LVL 13

Accepted Solution

by:
Surone1 earned 800 total points
ID: 39675391
that code will make it read write if it is read only and read only if it is read write.
the filename should be between quotes as it is a string.

to just make it read only without checking:

 Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("c:\DB1.mdb")
    f.Attributes = f.Attributes + 1
    Set f = Nothing
  Set f = fso.GetFile("c:\DB2.mdb")
    f.Attributes = f.Attributes + 1
    Set f = Nothing
   Set fso = Nothing
0
 

Author Comment

by:Cynthia Hill
ID: 39675438
Great - Thanks!

I will play with that today and to verify it works for me.

Question: If I wanted to make a second function to make teh databases read/write without checking (so basically the opposite of the other function)...would I simply change the line of code listed as "f.Attributes = f.Attributes + 1" (READ ONLY method) to "f.Attributes = f.Attributes -1" (READ WRITE method???)  Changing the "+ 1" to a "- 1"?  Would that do the trick?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39675445
yes it should
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39675729
Beware that if you run this against the same set of files twice, you will remove the read-only attribute.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39675765
aikimark is right. it also seems to affect the hidden property so we put the check back in:



 Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("c:\DB1.mdb")
 If f.Attributes And 1 Then
       Else
    f.Attributes = f.Attributes + 1
end if
    Set f = Nothing
  Set f = fso.GetFile("c:\DB2.mdb")
 If f.Attributes And 1 Then
       Else
    f.Attributes = f.Attributes + 1
end if
    Set f = Nothing
   Set fso = Nothing


and



 Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("c:\DB1.mdb")
 If f.Attributes And 1 Then
  f.Attributes = f.Attributes - 1
       Else
 
end if
    Set f = Nothing
  Set f = fso.GetFile("c:\DB2.mdb")
 If f.Attributes And 1 Then
  f.Attributes = f.Attributes - 1
       Else
   
end if
    Set f = Nothing
   Set fso = Nothing
0
 

Author Comment

by:Cynthia Hill
ID: 39675791
Yeah...I noticed that it wanted to mark the DBs hidden if I ran it more than once.  

Below is what I came up with...instead of adding or subtracting a number to the exisitng Attritbute property...just set it to the settign I want (e.g. from  f.Attributes = f.Attributes + 1 to  f.Attributes = 1)

Do you see any reason why this will not work?  Seems to work when testing.


Function DBS_ReadOnly()
Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("H:\TEST\db1.accdb")
    f.Attributes = 1
    Set f = Nothing
  Set f = fso.GetFile("H:\TEST\db2.accdb ")
    f.Attributes = 1
   Set fso = Nothing
End Function

Function DBS_ReadWrite()
Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("H:\TEST\db1.accdb")
    f.Attributes = 0
    Set f = Nothing
  Set f = fso.GetFile("H:\TEST\db2.accdb ")
    f.Attributes = 0
    Set f = Nothing
   Set fso = Nothing
End Function

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 39675846
Instead of using addition, use the AND boolean operator to flip the one bit.

Have you considered my suggestion of the use of the Attrib command?
0
 

Author Comment

by:Cynthia Hill
ID: 39675896
aikimark - is there a reason why this wouldn't work...or is not a "correct" option to go with?  

settign the attribute to the exact setting I want (e.g. Attributes = 1 or Attributes = 0)?  


Function DBS_ReadOnly()
Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("H:\TEST\db1.accdb")
    f.Attributes = 1
    Set f = Nothing
  Set f = fso.GetFile("H:\TEST\db2.accdb ")
    f.Attributes = 1
   Set fso = Nothing
End Function

Function DBS_ReadWrite()
Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("H:\TEST\db1.accdb")
    f.Attributes = 0
    Set f = Nothing
  Set f = fso.GetFile("H:\TEST\db2.accdb ")
    f.Attributes = 0
    Set f = Nothing
   Set fso = Nothing
End Function
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39675955
As written, you are setting the Attributes of these files without any regard to other attributes, such as archive-needed.

Earlier comment correction:  The OR boolean operator should be used, not the AND operator.
0
 

Author Comment

by:Cynthia Hill
ID: 39679507
aikimark - can you show me an example of what the code would look like if using the Attrib command?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39679762
attrib +r H:\TEST\db*.accdb

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question