Create table or query

  • Thread starter Thread starter George
  • Start date Start date
G

George

Hi all,

I have a secured XP database. I have created a custom
group CustomGroup. Everything seems to be fine except one
thing: any member of this group is unable to create either
tables or queries.

According to Litwin, Getz, and Gunderloy's book Access
2002 Enterprise Developer's Handbook, any user (no matter
what group he belongs to) is able to create a new table
ore query and XP has no way to stop that. When choosing
<New Tables/Quries> in the User and Group Permissions
dialog box, you can control only the permissions the
account will receive for new objects, but not the ability
to create new objects.

However, this is *not* what I have experienced. Users of
this CustomGroup can create new form, reports, macros, and
modules. But they can not create new tables or quries,
either thru code or manually. For the life of me, I cannot
figure out why.

Can anyone shed some light on this?
 
What happens when they try to create a new table or query? (ie. give us the
message & code, or whatever)

TC
 
When attemtping to manually create a table, after clicking
the Save button from the manual bar and giving the new
table a new in the Save As dialog box, the following
message is received:

Could not create; no modify design permission for table or
query 'Table1'.

The below code is used when attempting to create a table
thru code:

Set tdfNew = CurrentDb().CreateTableDef("Table1")

With tdfNew
.Fields.Append .CreateField("Vendor Number", dbInteger)
.Fields.Append .CreateField("Vendor Name", dbText)
.Fields.Append .CreateField("Available_PV", dbBoolean)
End With

CurrentDb().TableDefs.Append tdfNew

At the last statmene, the following error occurs:

3111: could not create; no modify design permission for
table or query "Table1"
 
A couple of things.

(1) Log on as one of the users in question & check their Allpermissions
property:

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

The online help says how to interpret Allpermissions (I don't have Access
here to check). The value reflects the permissions applying to that user
specifically, >plus<, permissions acquired indirectly by virtue of group
membership. See if there is anything strange there.

(2) You really should not use Currentdb() "in line" as you are doing below.
Currentdb refersehes all collections on every call. That is an expensive
action. Also, certain objects, created like that, do not survive to
subsequent lines of code. Change your code as shown below. This is doubtless
not the problem, but, you should do it anyway.

dim db as database
set db = currentdb()

(then use db instead of currentdb)

set db = nothing

HTH,
TC
 
Back
Top