User permissions in access 2007 Results

I understand these features are being discontinued, however, my database started in Access 2003 and is now opened with Access 2007. I would still like to have user and/or group permissions. I tried using the User Level Security wizard, but after setting myself up in the Admins group (also as owner of the object).....when I try to re-open the database, I get error 3033 saying I do not have the correct permissions to open the database????

Can anyone help?????

I have upgraded my access database from Access 2003 to Access 2007. I have a form that allows you to set permissions on the individual database objects based upon user group. It allows you to select a user group and then a specific table or form or query or macro and then select/deselect permissions(read design, modify design, administer, delete data, etc.). It was just fine in Access 2003. However in Access 2007 it will allow me to make permission changes but when I click 'Ok' and then go back into the form it has not set the permissions. I am going through the code(which should not be different since all i did was import the tables) and i have come across the following code where the new permissions are actually set.

If intAction = adhcPermissionAdd Then
doc.Permissions = doc.Permissions Or lngPerm
ElseIf intAction = adhcPermissionSubtract Then
doc.Permissions = doc.Permissions And Not lngPerm
Else
doc.Permissions = lngPerm
End If

I know Microsoft has got rid of Jet User Level Security in Access 2007. So my question is will doc.Permissions still work in Access 2007? Any advice would be greatly appreciated.

Thanks



Doug

OK, I have spent the best part of a week trying to sort this out, so if anyone can shed any light on it, I would be very very grateful.

I have created a database in Access 2007, which I wish to share with my collegues. We all are running Vista, and access 2007, over a peer to peer network.

I have installed a Front End on each Computer and linking to one copy of the backend on the host computer in the public drive.

The system runs great on each PC, however, I am having a problem sharing the database. All users can log onto the database at the same time.

However, when one user enters a form, it locks access to the form for other users, it just hangs until the other user exits the form, then the user that was hanging goes directly into the form. I get no error message.

I have tried linking the form to a query rather than directly to a table, but no joy with that either, the form still allows only one user.

I am not sure if this is an exclusive permissions problem and I need to change the Windows security settings or something else.

Like I say, if anyone can help, it would be really appreciated.

I have a small Access 2007 .accdb file for which I would like to manage permissions. I'm less worried about security as I am about data-integrity. The database is located on a shared network, the access to which is managed by the campus IT. But some staff need more access to the data than others (and some need more restrictions than others). Also, I would like to protect from honest attempts to modify/change forms, queries, code and tables. Most users are not Access-proficient, but a few are more independent than others.

I've scanned this forum and read some stuff on how Access 2007 no longer provides user-level security for accdb files. But I haven't been able to decide which route to take:Convert to MDB and implement ULS (what will I loose? will it make db management (particularly updating) more difficult?) Simply create an accdbe and forget about ULS. Try to program permission-management (can it be done with VBA?...I'm a VBA novice, but a quick-study). Another option? Please advise.
Thanks
Karen

I'm working wtih a .ACCDB in Access 2010 x64 running on Windows 7 x64

This DB was originally an Access 2000 format whose permissions were controlled by an old MDW.
It contained several links to tables in two other DBs also of Access 2000 format and whose permissions were also controlled by the MDW.

I had admin permission to all the DBs and exported all objects from all DBs to seperate MDBs and confirmed I could open all three without restriction from the MDW.

I then converted all three to ACCDB format and have been working fine with them.

However, I have just now discovered that I cannot delete, cut, or paste any of the objects in the main database.
However, I can delete, cut, and paste any and all objects in the two linked databases when I open them directly.

To be clear, in the main database, my inability to cut, delete, or paste objects is NOT limited to the linked tables.
I cannot even delete any queries I have created AFTER the conversion to ACCDB format.
This limitation applies to all objects (tables, queries, forms, reports, macros, and modules), linked or not, in the main database only.

I have gone back to the original MDB running in Access 2007 on Windows XP (x86) and have confirmed I AM able to delete, cut and paste any and all objects.
However, I also checked the original x86 ACCDB running in Access 2010 on Windows 7 x86 and I am NOT able to delete, cut or paste any objects.

Additionally, I AM able to edit all the objects as much as I care to and in any way I wish.
This limitation apparently only applies to what I can do within the navigation pane.

I have confirmed my windows user account has all the necessary file level permissions.
I have poked around in the Access Options with no help.
I have reduced the setting for ‘Default Open Mode’ to ‘Exclusive’ from ‘Shared’.

Still no joy.

Any help or insights will, as always, be greatly appreciated.

In Access 2007, what is an alternative to the Users and Permissions that was able to be used in 2003 and prior?

Thanks,

Matt

In Access 2003 I can assign permission to every user or group using workgroup feature. How can i do that in Access 2007?

Hi all!

Ok today for the first time I get the chance to finally look at office 2007 and upgrading my access db from 2002 (our entire workplace is upgrading to 2007 and our db is going with it)

The access itself is used mainly as a front end to a back end ms sql 2005 database, with a smattering of local tables in the db too.

So conversion ran well, then it came to testing the code.

Up sprang forth the following error....
odbcdirect is no longer supported rewrite the code to use ado instead of dao

`Oh bugger` was my first thought, knowing the amount of code in our db. I noted that none of the `features no longer available in access 2007` guides id read mentioned this!

So I do some research to see the issues in hand, and from that what it takes to convert. The MS examples were a bit lacking, and other examples dotted around varied in response. So I thought id ask opinions here.

The first port of call when my access opens is to try to create a basic connection to the ms sql server without actually accessing any data, to check the database link itself is ok (we can trap for problems such as user has access denied or doesnt have permissions set up on the ms sql server to access the database) - at this point in time there may be no tables linked to our database.

I normally do this with a code snippet of

	Code:
	 
    On Error Resume Next
    Err.Clear
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("", dbDriverNoPrompt, False, getODBCConnectionString)
 
    If Err.number  0 Then
        ' handle error accordingly
    End If

I can't quite work out the equivalent code using an ADODB.Connection

I presume the default way of linking tables in 2007 is via ODBC link tables so I do need to check if the connection works. ado != ODBC is it, so checking an ado connection wouldnt give me what im after?

Another question, heres an example of my opening a recordset in current code...

	Code:
	 
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim rs As Recordset
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("", , False, getODBCConnectionString)
    Set rs = conPubs.OpenRecordset("SELECT fields FROM mytable", dbOpenSnapshot)

Code im already using using ado connections (to execute stored procedures from access) goes something like

	Code:
	    Dim objConn As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim sDataConnect As String
    Dim lngErrorNum As Long
 
     ' Instantiate a database connection
    Set objConn = CreateObject("ADODB.Connection")
 
    ' Create an ADO connect string
    sDataConnect = getOLEConnectionString
 
    ' Open a database connection
    objConn.Open sDataConnect
 
    Set objCmd = CreateObject("ADODB.Command")
    With objCmd
        .ActiveConnection = objConn
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.mystoredproc"
         etc.etc.
        .Execute , , adExecuteNoRecords
        lngErrorNum = .Parameters.item("@err")
        etc.etc.
    End With
    Set objCmd = nothing
    objConn.close
    Set objConn = nothing

I see lots of examples using currentproject.connection, but I wager this doesnt mean I can share multiple seperate recordsets down the same connection at the same time - should I be creating my own adodb connection each time or only using currentproject.connection. I don't understand how that works - theoretically your access may be connected to several back end db's - what does currentproject.connection actually point to? Im guessing currentproject.connection = linked tables (which is what most of my code based querying accesses)

Hitting the server direct in code based queries or converting lots of things to pass through queries is not an option, given the volume of work, and dynamic nature of much of it.

So with the above I supose im asking - what kind of connection is required on the first example above (currentproject.connection - and can this share multiple live recordsets down it at the same time?), and is the ado stored procedure example still valid, or a little `long winded` compaired to current good working practise examples!

Many thanks in advance for any input,

Martin

Hi,
I have a system based on 25 different mdb databases where most of the data is located on a SQL server.
The main database is a protected database with a login dialog. After the login dialog, the user is asked to select a sub-system.
Each sub system is a stand-alone database, some are protected, and some are not, but they may still require the login name to work.

Due to the strange fact that running a mdb database from a remote computer with Access 2007 is at least 5 times slower than with Access 2003, the mdb files have been stored locally on all clients. Since the total size of the mdb files is ~200MB the files has to remain splitted to minimize waiting times during a database update/repair. (60+ clients trying to download a 200MB file simultanously on a 10Mbit network while working on external files may make some users rather furious...)

Currently we do the switching between databases by using the macro function "SendKeys" with the parameters like ^(o)u:appstartup.mdb%(o), but some users have problems with this command. -The open dialog does not contain the filename, and sometimes the wrong directory is opened.

I have tried %(fo)[databaseName].mdb%(o), but for some users the file menu appers, and the macro stops.

I have also tried %(fo)[databaseName].mdb~, but this causes the macro to loop.

Is there a VBA code that can do the same thing as the Macro function SendKeys ^(o)u:appstartup.mdb%(o) without sending key-strokes?

I have tried Application.FollowHyperlink "U:appstartup.mdb", but this causes a new Access window to open without the necesary permissions.

I have also tried Application.OpenCurrentDatabase "U:appstartup.mdb", but this shows the error message Run-time error '7867': You already have the database open." -The same error occur if I try to open a database that does not exist.

I need to open the new database in the same access-window to prevent the permission error to occur, but at the same time, it must be possible to open multiple instances of the same database on one computer.

-Olav

Hi Everyone;

I created a stored procedure in MSSQL Server 2005 using user sa (sysadmin role) and associated it to a button in an MS Access 2007 form (actually this is an access data project), it executed ok when button was pressed.

Then I created another user in MSSQL Server 2005, this one not sysadmin and granted execute permission on this procedure only, but when I enter the project with this login the procedure does not execute, there is a message saying that Microsoft Access cannot find the object...

Can Anybody help me on this, please ?

Andre

I am attempting to create an Access 2007 front-end to an existing remote SQL Server 2008 database. I created a new Access Project (adp) and connected to an existing SQL database. I entered all information in the Data Link Properties dialog and clicked Test Connection. Access said it was successfull so I clicked OK on the Data Link Properties dialog expecting the database to connect. However, it does not. In the title bar at the top it says DATABASENAME (disconnected):Project (Access 2002-2003 file format). I checked permissions for the user (SQL Authentication) in Management Studio and I don't see any issues there as the user as db_owner permissions for the database. I can connect through Excel and link to all tables in the database using the same login. Is there something I'm missing?

Has anyone ever integrated Access 2007 with Windows login?

What I would like is to do is allow anyone to open the DB and view information (run reports, etc) but only allow users on an ACL to be able to input/modify data. Ideally it would be integrated with the smart cards we use to login, and require you to put in your PIN...

I'm not sure if this better tackled via NTFS permissions or setting up ACLs within the DB.

Anyone have any good links, or books to point me in the right direction? (and just an FYI...I'm in week 3 of dealing with Access 2007...and never done anything with DBs in the past.) :-)

Thanks!

Hi,

Still being a new to access, I am looking to restrict users from design and layout views with in a series of databases.

The databases will be maintained and any changes made by 2 administrators.

My requirement seems to be heading towards using users & permissions.
I have a very limited understanding of how this option works.

Looking at the wizard, it seems to be wanting to set up a workgroup file containing user names for admin & users. This I am happy with. My concern is; will creating and applying this file affect just the database I am working on or will it affect each users use of access in general?

I hope you can understand what I'm trying to ask.

Regards

Steve

I created a MS Access 2007 database and now want to setup user level security. The problem is that when I open my database I don't any of the user level security tools (e.g., the Administer/Users & Permissions tool in the Database Tools tab) on my tool bar.

Thanks

I've built an Access 2007 application that lists our company's suppliers and creates purchase orders. In this application, I want to allow different users access to different things. Some of the things that I want to control access to include:
adding suppliers deleting suppliers, editing suppliers, adding comments about suppliers seeing a list of suppliers with expired certificates, reviewing purchase order log, managing users, receiving alerts to add/modify suppliers through email receiving purchase orders through email, etc. I originally thought of classifying these into groups like 'Admin', 'Manager', etc., but it got messy since some users might need A,B,C, one might need B,C,E,F, another might need A,C,F and it just ends up with too many groups with too few people in each. So instead, I have each of these permissions set as a Boolean value in the Login table.


Managing this table seems pretty simple so far. But the forms and VBA are a mess. Most of my code looks like this:

	Code:
	If Not AllowAdd Then
     cmdAdd.visible = False
     Me.AllowAdditions = False

End If
If Not AllowDelete Then
     cmdDelete.visible = False
     Me.AllowDeletions = False

End If
If Not AllowEdit Then
     For ctl in controls
          ctl.locked = True
     Loop
End If

...

or

	Code:
	If AllowEdit Then
        Me.cboMenu.RowSource = Me.Me.cboMenu.RowSource & _
        "Modify records;"
    End If
    If AllowApprovePurchase Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "Review Purchase Orders;"
    End If
    If AllowUserAdmin Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "User Administration;"
    End If
    If ShowExpired Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "List of suppliers with expired certificates;"
    End If
...

This is generally on the Form_Load() procedure of each form that restricts user access, although sometimes it's in the middle of the code, such as when retrieving email addresses for users that are supposed to receive requests/status updates. It works, but it gets incredibly messy to maintain such as when adding or modifying a permission. I thought of managing this in its own module, but I'm having trouble figuring out how it could work.

I'm wondering if anyone has any suggestions for better organizing these permissions so that adding/modifying them isn't as much of a tedious and error-prone process as it currently is.

Thanks

Just upgraded to Acccess 2007 and have ownership problem extracting queries from a 2002 db.

When trying to import into a new 2007 or export from the 2002, get "you have no permission" message

Have created a new user tried to change owner of the db, but no permission
File security looks correct in that I am owner of the file with full permissions, but Access doesn't recognize me as the owner

Tried logging in as admin, but it isn't the owner either

This is not critical, just very time consuming, this is a test project, but it would be nice not to have to rewrite about 30-40 queries and equivalent forms

Any guidence appreciated

Hi there,

I have an application that I converted from Access 97 to Access 2003, and from which I've created an MDE file. I want to test the MDE out on an environment that does NOT have Access but instead has the Access Runtime (which is what the users' machines will have). I used Access 2007 Developer Extensions' Package Solution Wizard to create the setup for the application and ran the setup on the test environment, and the application installed ok. My problem is that when I run the application, it opens up, but then after a few seconds a message appears saying: "You do not have the necessary privileges to open this database. Closing Access Program."

I included a workgroup information file (an .mdw file) as one of the files to be installed when the setup is run, and I would think this file should handle privilege/permission issues, so I can't see where things are going wrong.

Does anyone have any suggestions on how to fix this problem? Also, is it necessary to convert the .mdw file to Access 2003? It is currently in Access97 format. Could this be causing the problem?

Any help here would be great!

Thanks,
newToAccessDev

Hi - I've got a database in Access 2007 that keeps track of client data for work. It's been working fine for about a month, and suddenly today other users can't change any information because the records are suddenly locked when accessed from their accounts.

I've already checked that they can't update forms, tables, nothing.

I've checked the database properties, it defaults to shared with no locks.

I've checked the properties of my forms - no locks.

I've double checked the permissions to the folder that the database is in - no restrictions.

The database is split.

I'm losing my mind! What is wrong here?!

In Access 2003, I could easily make linked tables (from SQL Server) read-only, as follows:


	Code:
	    Set con = db.Containers("Tables")
    Do Until rs.EOF
            Set doc = con.Documents(rs!Local_Table_Name)
            For Each grp In wrkDefault.Groups
                doc.UserName = grp.Name
                doc.Permissions = dbSecRetrieveData
 
                For Each use In grp.Users
                    doc.UserName = use.Name
                    doc.Permissions = dbSecRetrieveData
                Next
            Next
        rs.MoveNext
    Loop

Is there a way to get this same result from Access 2007?

Thanks!

everytime i open my database i have to activate the VB permission but its very tedious for the end users

is there a way to bypass it

cheers


Not finding an answer? Try a Google search.