Problem linking tables.

[Guest]

I've enabled user-level security on a split db using the steps as described
in the "Security FAQ". When a user logs on they are receiving the following
error message: #3111 "Could not create; no modify design permission for
table or query 'TMP%#MAU@'."

I'm attempting to refresh the table links in a procedure in the front end's
opening form. A snippet follows:

For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = ";DATABASE=" & strConnect
tdf.RefreshLink
End If
Next

I've granted my groups "Read Design" as well as "Read Data" authority on all
tables on the backend , full access to all tables on the front end, and
"Open/Run" authority to both db objects themselves, all to no avail....same
error. When I as an admin open it up, all works fine. Where have I gone
wrong? Any thoughts? I even tried granting a user full access to the
backend (as a test)...same results. Help!!!

 

[Allen Browne]

Compact the front end to get rid of the deleted tables.

Modify the code so it avoids these TMP tables, and those starting with ~.

Relinking can occur only with admin priviliges.

 

[Guest]

Thanks for the reply. I've compacted the the front end, added conditions for
avoiding TMP tables and those containing "~" and still get the same results.
I ran through the TableDef collection and printed the names of all table defs
to the immediate window and in addition to my tables I get the following:

MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

Could any or all of these be my problem? What if I were to temporarily add
the user to the admins group (via code) before attempting the RefreshLink?

Thanks again for your help.

 

[Allen Browne]

Yes, these hidden system tables are part of every Access database. You can
code around them if you also exclude the tables where:
Not tdf.Name Like "MSys*"
or, if you prefer, where:
(tdf.Attributes And dbSystemObjects) = 0
While that's a good idea, it's not crucial, as they will not be attached
tables anyway.

I've never tried dynamically adding the current user to the Admins group
before relinking. Even if you do get it to work, it sounds like a security
risk. Surely there can be some user or group who could have full access to
the TableDefs.

 

[Guest]

Thanks again for the help Allen. I went back and reviewed the security faq
and it seems there was a small "blurb" about when using the .connect method
an error will be thrown but the link will be refreshed anyway. I tried the
inline error handler approach ("on error resume next") and, sure enough, it
appears to work. Very confusing! Let me throw another quick one your way.
Is there any way to suppress the warning you get about the program may be
harmful to your computer when you first start up Access. I would as thought
you would have the option to not have that show anymore after the first time
around.

 

[Allen Browne]

I think you are referring to the Macro Security warning you get when you
open an Access 2003 database?

If so, this should solve it for you:
http://home.bendbroadband.com/conradsystems/accessjunkie/macrosecurity.html