Set table 'Read' property using VB6?

[Andrew Fergus]

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

 

[SydneyGeek]

Hi Andrew,

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

' 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]

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]

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

 

[SydneyGeek]

Andrew,

I found this ... http://www.zen51342.zen.co.uk/vbadv/vbadv_008.htm

Denis

 

[Andrew Fergus]

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:

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]

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]

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