Solved

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

Posted on 2008-10-18
16
668 Views
Last Modified: 2013-11-27
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
Comment
Question by:awolarczuk
[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
  • 9
  • 6
16 Comments
 
LVL 1

Expert Comment

by:hakkekushou
ID: 22751171
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
 

Author Comment

by:awolarczuk
ID: 22751307
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
 
LVL 85
ID: 22751584
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:awolarczuk
ID: 22751593
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
 

Author Comment

by:awolarczuk
ID: 22751595
here is the image of the permissions i need to add read data i dont want to add to it
access-per.JPG
0
 
LVL 85
ID: 22751631
This must be a 2003 database that you're opening in 2007 ...
0
 

Author Comment

by:awolarczuk
ID: 22751638
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
 
LVL 85
ID: 22751654
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
 

Author Comment

by:awolarczuk
ID: 22751660
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
 
LVL 85
ID: 22752183
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
 

Author Comment

by:awolarczuk
ID: 22753173
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
 
LVL 85
ID: 22753727
A simple connection string would be something like this:

"Driver={Microsoft Access Driver (*.mdb)};dbq=Full Path To Your Database;"
0
 

Author Comment

by:awolarczuk
ID: 22755352
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22755918
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
 

Author Comment

by:awolarczuk
ID: 22765990
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
 

Author Comment

by:awolarczuk
ID: 22766004
alsi it seems to error evdn when thats table is there
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

689 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