Set table 'Read' property using VB6?

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi everyone

I have an application in VB6 that links to a local Access database. I need to change the 'Read Data' property for one of the tables within the database and cannot work out how to do this using VB6.

I can do this manually like so : within Access, click Tools > Security > User and Group Permissions > select the table > tick or untick the 'Read Data' property. However, I need to be able to do this from within the VB6 application.

Does anyone have any suggestions how to do this using VB?

I'm currently using DAO to connect to the database but if I have to change that to ADO to achieve this then that would be ok.

TIA, Andrew

P.S. I also posted this question on another forum here : http://vbaexpress.com/forum/showthread.php?t=9595
 

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.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Andrew,

This may get you started -- sets the current DB between read-only and read/write.

Code:
' sets the current db to readonly
Public Sub SetReadOnly()
  SetAttr CurrentProject.Path & "\" & CurrentProject.Name, _
   vbReadOnly + vbArchive
End Sub

'sets the current db to read write
Public Sub SetReadWrite()
  SetAttr CurrentProject.Path & "\" & CurrentProject.Name, vbArchive
End Sub
...from vba123.com

Denis
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Denis
Thanks for that but I suspect it won't work. I have full read/write access to the database - but not for one of the tables. The table is one of the hidden tables in Access called 'MSysObjects'. I'd like to be able to set the 'Read Data' property for that table to True, if possible. I've been trawling the 'net most of this afternoon trying various snippets of code without success.
Andrew
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Andrew,

So you need to pull data from MSysObjects? What are you after? It may be necessary to use ADOX if DAO won't do it -- will see what I can find.

Denis
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Denis

I thought I might need to use ADO/ADOX. That code looks like it will give me what I want. However (there is usually a however!) the code crashes and I get the following error message :

Error -2147217887 : You do not have the necessary permissions to use the 'MSysObjects' object......

The error is occuring on the very last line (ie cat.Users....)

This is the code I tried to use:
Code:
Sub GrantPermissions()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"

cnn.Properties("Jet OLEDB:Database Password") = strPass

cnn.Open "data source=" & dbFileName & _
    "; jet oledb:system database=C:\Documents and Settings\Andrew\Application Data\Microsoft\Access\system.mdw", _
    UserID:="Admin"

Set cat.ActiveConnection = cnn

'Give the Admin user Read rights on the MSysObjects object

'Just to help find the groups
Dim x As Integer
Dim xUserCount As Integer
xUserCount = cat.Users.Count
Debug.Print xUserCount
For x = 0 To xUserCount - 1
  Debug.Print cat.Users(x)
Next

cat.Users("Admin").SetPermissions "MSysObjects", adPermObjTable, adAccessGrant, adRightRead

End Sub

I appreciate your help Denis but it now looks like a 'no go', not just for the permissions error but the fact I have to hard key the location of the system.mdw file. I won't be the only user of this programme and hard-keying the location is unacceptable / unworkable. If I leave the mdw part off the code, then I get a different error message (error 3251, ...not capable of performing requested operation - this occurs when I try to access the users in either within the loop or again at last line).

Cheers, Andrew
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Andrew,

Ah well -- worth a try. I can see where hard-coding the mdw location could be a pain, as well as the need to be logged in as teh Admin user.

Wouldn't surprise me if there's a security reason behind it; you wouldn't want just anyone to be able to point an app at your database and play with the system tables.

Now to get another workaround! :(

Denis
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Well I'll be!! It works on a number of databases except the one I have been testing today - there is no obvious reason why it doesn't work on that one database.....other than the fact it has a password on it. I pass the validation test for the password but I might have to see about removing the password via code too......

Cheers
Andrew
 

Forum statistics

Threads
1,141,012
Messages
5,703,729
Members
421,312
Latest member
Mooncake1

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