Could not create table

[George]

Hi all,

I created a custom user group, CustomGroup, in XP and
assigned the following permission to this group:

Tables: New Tables/Queries: Read/modify Design,
Administer, Read/update/delete/insert Data.
Talbes: Table1: Read/modify Design, Administer,
Read/update/delete/insert Data.

For all other tables, this group only has Read Design and
Read/update/delete/insert Data permission. This is because
I have code that automatically create a new table Table1

Set tdfNew = CurrentDb().CreateTableDef(Table1)

' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("Vendor Number",
dbInteger)
.Fields.Append .CreateField("Vendor Name", dbText)
.Fields.Append .CreateField("Available_PV",
dbBoolean)
End With

CurrentDb().TableDefs.Append tdfNew

However, if I log in as a user in this CustomGroup group
and run the above code, I always receive error 3111 "Could
not create; no modify design permission for table or query
Table1." on the last statement. If I attempt to create a
table manually, I receive the same error when saving the
new table.

Can anyone shed some light on why this is happening and
how to fix it?

TIA.

 

[TC]

Let's look at it logically. If you are not allowed to create a new table via
the user interface, one would have to conclude that you are *not* logged in
as a user who has permission to create new tables!

Run the following (untested) code to display your user *and group*
permissions:

debug.print dbengine(0).users(currentuser).allpermissions

Check the AllPermissions property in online help, to see how to make sense
of it. (I don't have Access here to check.) This will show you the actual
permissions of the current user - direct permissions, *and*, permissions
inherited from group membership.

Also, you really should not reference CurrentDb() "in line", as you are
doing. Everey call to currentdb refreshes all collections, which is an
expensive operation. Also, when you create an object (like a tabledef) from
an "in line" currentdb reference, that object might or might not survive for
following lines of code - it depends on the object type.

So you should do this at the start:

dim db as database
set db = currentdb()

then use db (not currentdb) thereafter.

HTH,
TC