Solved

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

Posted on 2008-10-18
16
650 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
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 84
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
 

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 84
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 84
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 84
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 84
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 84

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 33
DBF to ... Converter 5 43
How autopopulate number field with 2 different criteria 9 51
VB.NET Repostiory Pattern 7 16
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

11 Experts available now in Live!

Get 1:1 Help Now