Solved

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

Posted on 2008-10-18
16
639 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
  • 9
  • 6
16 Comments
 
LVL 1

Expert Comment

by:hakkekushou
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
 

Author Comment

by:awolarczuk
Comment Utility
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
Comment Utility
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 84
Comment Utility
This must be a 2003 database that you're opening in 2007 ...
0
 

Author Comment

by:awolarczuk
Comment Utility
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 84
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:awolarczuk
Comment Utility
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 84
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
Comment Utility
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
alsi it seems to error evdn when thats table is there
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now