Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

If there a way to change the promission of a access table on the fly

Hi all
I am trying to check my database to see if there is a table already there before doing a query, but i have come across a problem, the table i am using is a system table and i dont have read permission even with Admin as the user , the moment i go in to access and chance the permission by hand it works fine

Is there anyway i cant do this in access as thi is a app i need to send to over 200 site so it will be a little hard to do it all by hand or even another way you think will work,

I will attach my code, change it if you like but if you do please tell me what the change will do in comment as i can learn

thanks
Module update
    Public Sub dbu()
        connect()
 
        Dim rs As New ADODB.Recordset
        Dim sql As String
 
        sql = "select * from MSysNavPaneGroups where [name] =  'job_information'"
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 
        If (rs.BOF Or rs.EOF) Then
            GoTo updatet
        End If
        Exit Sub
updatet:
        sql = "CREATE TABLE Job_Information (ID integer PRIMARY KEY ,Joblist char(255))"
        Debug.Print(sql)
        'rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")
        rs.Update()
        MsgBox("Stars Datase Update (New Table,""Update"")")
        cnn1.Close()
    End Sub
 
End Module

Open in new window

0
awolarczuk
Asked:
awolarczuk
  • 9
  • 6
1 Solution
 
hakkekushouCommented:
Sure thing!

GRANT OPERATIONTYPE ON DBOBJECT TO USERORPROFILENAME

DENY OPERATIONTYPE ON DBOBJECT TO USERORPROFILENAME

*OPERATIONTYPE would be SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc...

*DBOBJECT would be a table, procedure, database, schema, etc...

*USERORPROFILENAME would be the name of a DB user or security profile such as public or owner
0
 
awolarczukAuthor Commented:
so to change the read permissions for MSysNavPaneGroups  to read data how would we do this , it already has admin as the user but has not got any permissions selected

grant  update on MSysNavPaneGroups to Admin

some thing like that but how would i add "read data"

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say you "change permissions", exactly what are you doing? Access 2007 doesn't have User Level Security, thus you cannot "grant" permissions.

Also: Updating system tables is generally a bad idea, and can cause your application to become hopelessly corrupted. If you're just adding a table you can do this without working with any of the system tables ...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
awolarczukAuthor Commented:
Thanks mate for the heads up, all i am trying to do is do a search on that table to see if there is a table byt that name, if not add the table if there is the dont do anything, i have put a screen shot of the permissions i need to change

I look forward to hearing from you

and thanks in advice for your help
0
 
awolarczukAuthor Commented:
here is the image of the permissions i need to add read data i dont want to add to it
access-per.JPG
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This must be a 2003 database that you're opening in 2007 ...
0
 
awolarczukAuthor Commented:
thats right mate i upgraded but my clients still have the old database the new client can have the new one but for this one i need a what to change it , any ideas mate
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try searching MSysObjects ... MSysNavPaneGroups is simply the "groups" that show in the NavPane, while MSysObjects actually contains all the objects in the db:

SELECT * FROM MSysObjects WHERE Name='YourTableName' AND Type=1
0
 
awolarczukAuthor Commented:
Record(s) cannot be read; no read permission on 'MSysObjects'.

Still get this mate

sql = "select * from MSysObjects  where [name] =  'job_information' and [type] = 1 "

this is the code i use

Do you have any other way i can use to see if there is a table by that name
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can open a recordset based on the table, and catch the error ... although if you don't have the necessary permissions to read MSysObjects I doubt you'll have the permissions to add a Table. You can use DAO to manipulate permissions on an Access database, but not ADO/ADOX ... can you include a reference to DAO in your program?
0
 
awolarczukAuthor Commented:
that should be a issues for this one area, i have been able to create a table with no issues at all it is just this area i am having issues with, could you please give me a example

        cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & DBFile & ";jet oledb; "


This is the connection string i am using atm
Public Sub connect()
 
        Dim rs As New ADODB.Recordset
        On Error Resume Next
 
 
        ' DBFile = Application.ExecutablePath()
        '  If Right(DBFile, 1) <> "\" Then
        'DBFile = DBFile & "\"
        ' End If
        DBFile = "c:\stars\Stars.mdb"
        cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & DBFile & ";jet oledb; "
 
 
 
        'cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + " & DBFile & " + "User id=admin;" + "Password="
        cnn1.Open()
 
        STARS.connection.Text = "You are now connect to STARS"
 
DBSafe:
        Exit Sub
DBError:
        cnn1.Close()
        MsgBox(Err.Description & vbCrLf & vbCrLf & "Failed to 'connect to database", vbCritical, "Error")
        Resume DBSafe
    End Sub
End Module

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A simple connection string would be something like this:

"Driver={Microsoft Access Driver (*.mdb)};dbq=Full Path To Your Database;"
0
 
awolarczukAuthor Commented:
so we are putting that after the cnn1.connectionstring = "Driver={Microsoft Access Driver (*.mdb)};dbq= DBFile;"

does that look ok

Now how would i do that query and change permissions
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Well ... you really can't change permissions with ADO/ADOX ... with Access, you pretty much have to use DAO to do this.

I'd try this before anything, however:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

<open your connection>

On Error Resume Next
rst.Open "SELECT * FROM SomeTable", cnn1

If err.Number = WhateverNumberYourGet Then
  '/table doesn't exist - add it here
End IF
0
 
awolarczukAuthor Commented:
how does that look i dont think it is making the table as eavh time it goes through it finds the error, what am i doing wrong on create mate
Module tbe
    Public Sub tbee()
 
        Dim rs As ADODB.Recordset
        rs = New ADODB.Recordset
        Dim sql As String
 
 
        connect()
        On Error Resume Next
 
        Sql = "select * from MSysObjects  where [name] =  'job_information' and [type] = 1 "
        rs.Open(Sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 
        If Err.Number = -2147217911 Then
            sql = "CREATE TABLE Job_Information (ID integer PRIMARY KEY ,Joblist char(255))"
            Debug.Print(sql)
            'rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
            SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")
            rs.Update()
            MsgBox("Stars Datase Update (New Table,""Update"")")
        End If
      
    End Sub
End Module

Open in new window

0
 
awolarczukAuthor Commented:
alsi it seems to error evdn when thats table is there
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now