Clay,
Here are some suggestions that may help you troubleshoot the permissions
problem.
(Note: 3033 is not necessarily a security error. By definition, it is a MISC
error, which DAO's online help describes as "Microsoft Jet errors that don't
fit into another category" where "another category" refers to the several
classes of errors given in the online error reference.)
When programmatically working with Jet security, it's usually best to use
DAO instead of ADO. OLEDB providers for Jet do not implement the engine's
security features nearly as well as DAO does.
First, make sure that you have the correct MDW file in use. If linked tables
with data sources from other Jet files are involved, each source database
should have been created with the same mdw file in use at the time the
databases were created; otherwise, results can be unpredictable.
To execute code, the user logged on must have sufficient permissions for the
code to execute its processes.
Your code sample indicates that a single table is involved: "SELECT * FROM
CartonLabel"
Is the table a linked table? If so, and if security proves to be the issue,
it will be necessary to look at the permissions set on the linked table's
source database in addition to permissions set on LABEL_DB (and relavent
objects within them). Jet permissions always use the least restrictive
permissions when permissions are combined from different sources, such as
when User and Group permissions are combined as well as when permissions are
combined on objects from different databases.
It is possible that the permission problem arises not because of a
permission setting through security but because an object that is involved
(such as a field, a table, or a query) has a system attribute; a field that
stores replication information will have a dbSystemField value in its
attribute. These fields cannot be deleted. Auto Increment fields have an
attribute of dbAutoIncrField. These fields cannot be changed.
If (SomeField.Attributes And DAO.dbSystemField) > 0 then
debug.print "Stores Replication Information"
end if
If (SomeField.Attributes And DAO.dbAutoIncrField) > 0 then
debug.print "Is an Auto Increment Field"
end if
To add, edit or delete records, the recordset must have at least one unique
index. Also, consistent updates (DAO.dbConsistent) are updates that
essentially enforce referential integrity (see dbConsistent as an option in
the OpenRecordset Method). Often the one side of a one-to-many relationship
is not updateable.
Also, make sure that each field that you are editing is actually updateable
(particularly when adding new records).
If RS_Label.Updatable then
'The recordset has one or more
'fields that can be changed.
For each fld in RS_Label.Fields
with fld
debug.print .name, "Is Updatable = " _
& cbool((.Attributes And DAO.dbUpdatableField) > 0)
end with
next fld
Else
debug.print "The recordset is not updateable."
end if
Below is a small sample of code that will give you some idea of how to check
security settings. For more detail about coding security, refer to DAO's
online help topics for the Containers Collection, Container Object,
Documents Collection and Document Object. Also, see Users, User, Groups,
Groups, Permissions, and AllPermissions.
An alternative to writing security code is to view objects from within
Microsoft(R) Access. For example, open a query in design view to get the
tables involved. Then use the users and group permissions interface to view
permissions. Usually though, an application needs code to handle at least
known possible errors. Access's interface is of little help to someone
confronted with a runtime error generated in your application.
The small sample of code given further below indicates that Permissions and
AllPermissions values are printed during the sample code's execution, but
these values are not useful in themselves; to determine specific
permissions, you need to compare specific permission constants to a user or
groups permissions using logical And. Then check the comparison for a value
greater than zero. Here's an example.
If (doc.permissions And DAO.dbSecInsertData) > 0 then
'The doc.UserName has permission to
'add records.
end if
'For combined permissions use AllPermissions
If (doc.Allpermissions And DAO.dbSecInsertData) > 0 then
'The doc.UserName has permission to
'add records.
end if
Set username on the document object (or a container object) to either a
UserName or a GroupName depending on the permissions that you are checking.
See the Permissions and AllPermissions properties in online help for
specific permission constants.
Your security coding for troubleshooting can be simplified by substituting
your select statement with a recordset opened directly on the table,
OpenRecordset("CartonLabel"). That way, you don't have to deal with
sourcetable properties on fields. If you were using a querydef with many
tables, then you would have to open the recordset from the querydef and
iterate through each field checking permissions on the source table via each
field's sourcetable property. The sourcetable property is what Jet uses to
determine permissions on queries, and its most apparent use by Jet is seen
in its WITH OWNERACCESS OPTION that is used for controlling query execution
and modifications to a query definition in a secured database.
(Note: I hand typed the following code without checking typo's. See DAO's
online help for samples that you can copy, paste and execute.)
Dim DB_Label as DAO.Database
Dim RS_Label as DAO.Recordset
Dim fld as DAO.Field
Dim doc as DAO.Document
Dim other variables as needed.
'Since your sample opens the database without first
'opening a workspace, Jet uses the default
'workspace with user Admin, pwd = ""
'Thus, later when you're troubleshooting, check
'Admin permissions and permissions on the Users
'Group and the Admins Group. By default, the
'Admin user is a member of both Admins and Users.
Set DB_Label = OpenDatabase(LABEL_DB)
'Tables and Queries are in a database's TABLES container.
Set doc = DB_Label.Containers("Tables").Documents("CartonLabel")
doc.username = dbengine.workspaces(0).username
debug.print "Object Name = " & doc.name
debug.print "Owner = " & doc.Owner
debug.print "User = " & doc.username
debug.print doc.username & " Permissions = " & doc.permissions
debug.print doc.username & " All Permissions = " & doc.AllPermissions
debug.print string(20, "=")
'Check field updateabiltiy.
'Use dbOpenDynaset because your query is a dynaset.
Set RS_Label = DB_Lable.OpenRecordset("CartonLabel", DAO.dbOpenDynaset)
For each fld in RS_Label.Fields
debug.print "Recordset Field Name: " & fld.name
'Check updateability of fld in the recordset
debug.print "Updatable: " & cbool(fld.Attributes And
DAO.dbUpdatableField) > 0)
debug.print
next fld
set fld = nothing
set doc = nothing
RS_Label.close
set RS_Label = nothing
DB_Label.close
set DB_Label = nothing
Glenn Tarpley
"Clay" <anonymous DeleteThis @discussions.microsoft.com> wrote in message
news:01d101c39e59$abd81950$a501280a@phx.gbl...
I am having a permissions problem with an access
database. I'm opening a recordset and everything looks
fine. I can navigate all records etc etc.
However, whenever I call the edit or addnew method of the
recordset, I get runtime error "3033"
- "You do not have the necessary permissions to use the
<tablenamehere> object. Have your system administrator or
the person who created the object establish the
appropriate permissions for you." -
As far as I know or anyone else can tell me, there are no
permissions on this database. I can go into the tables
manually and make a mess of anything I want. I've also
been told that ADO can solve this problem, although it
isn't an option because ADO cannot deal with replicated
access 97 databases, which this will be (or hopefully) in
the next few days.
Just so everyone knows everything, this is how I open the
recordset:
Dim strSQLString As String
strSQLString = "SELECT * FROM CartonLabel"
Set DB_Label = OpenDatabase(LABEL_DB)
Set RS_Label = DB_Label.OpenRecordset(strSQLString)
If anyone can help I thank you in advance!!
- Clay
FAQ
Profile
Private Messages
Log in
