Refreshlink error

[Guest]

I have a FE/BE solution with the FE complied to an mde file. I am adding a
new table through VBA and get the following error when I try to re-link the
tables.

3033 - you do not have the necessary permissions to use the "Glossary" object.
I am using the relink code from
http://www.mvps.org/access/tables/tbl0009.htm

I only get the error on the new table, all the others prior to it relink
fine...

Set tdlocal = dblocal.TableDefs(x)
tdlocal.Connect = ";DATABASE=" & Trim(strFileName)
tdlocal.RefreshLink
The following code is what I use to build the table:

'Build backend table and fields
Set tdfGlossary = dbPrevCon.CreateTableDef("Glossary")
With tdfGlossary
.Fields.Append .CreateField("Organization", dbText, 50)
.Fields.Append .CreateField("Acyn", dbText, 8)
.Fields.Append .CreateField("AcynDesc", dbMemo)
End With
dbPrevCon.TableDefs.Append tdfGlossary
For Each Fld In tdfGlossary.Fields
If Fld.Name = "Organization" Then
Fld.Required = True
End If
If Fld.Name = "Acyn" Then
Fld.Required = True
End If
Next

'Set the permissions for the table
Set con = dbPrevCon.Containers!Tables
Set doc = con("Glossary")
doc.UserName = "Admin"
doc.Permissions = dbSecInsertData + dbSecReplaceData + dbSecRetrieveData
+ dbSecDeleteData
doc.UserName = "Admins"
doc.Permissions = dbSecInsertData + dbSecReplaceData + dbSecRetrieveData
+ dbSecDeleteData
doc.UserName = "Users"
doc.Permissions = dbSecInsertData + dbSecReplaceData + dbSecRetrieveData
+ dbSecDeleteData

'Build Index for Glossary
With tdfGlossary
Set Idx = .CreateIndex("PrimaryKey")
With Idx
.Fields.Append .CreateField("Organization")
.Fields.Append .CreateField("Acyn")
.Primary = True
End With
.Indexes.Append Idx

End With

Any help is appreciated!

Jerry

 

[Guest]

Hi, Jerry.

The default user and groups don't have permission to modify the design of
this table, which is what they need to refresh the links. Change the line of
code granting permissions to this table for each user and group to:

doc.Permissions = dbSecWriteDef Or dbSecInsertData Or _
dbSecReplaceData Or dbSecRetrieveData Or dbSecDeleteData

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Gunny,

Thanks, this seems to work. One question though - if I change the
permissions to allow the default user and groups to change the design could
they possibly alter the tables and screw up the backend? I have a password
on the backend that I use when connecting through code on the frontend and I
have use the security wizard on the backend.

Thanks
Jerry

 

[Guest]

Hi, Jerry.

if I change the
permissions to allow the default user and groups to change the design could
they possibly alter the tables and screw up the backend?
Yes.

I have a password
on the backend that I use when connecting through code on the frontend and I
have use the security wizard on the backend.

<Sigh> Well, the code I gave you won't help if you've implemented
User-level Security. If the Admin user or the Users Group have _any_
permissions (except "open/run DB" for the Users group), then the back end
hasn't been secured properly. And placing Shared-level Security on top of
User-level Security is redundant.

These are the minimum permissions necessary for both the front end and the
back end when refreshing links:

Front end:

DbSecCreate
DbSecReadDef
DbSecRetrieveData

Back end:

DbSecDBOpen
DbSecRetrieveData

Don't give these permissions to the Admin user or to the Users Group. Give
them only to your custom group that the people who use this database belong
to and need to refresh these links. The Admins Group should have administer
permissions on this table and the rest of the database. And there should
only be one member in the Admins Group: the database owner.

For more information on User-level Security, please see the Security FAQ on
the following Web page:

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

If the Admin user or the Users Group have _any_

permissions (except "open/run DB" for the Users group), then the back end
hasn't been secured properly. And placing Shared-level Security on top of
User-level Security is redundant.

I am somewhat confused - I have setup all my tables with Read design;
Read, Update,Insert and Delete data for the Users group and the Admin user
to cover anyone using a different mdw to login to the database. Most of my
queries and vba code use with owners access persmission. I thought that
these settings would keep anyone from modify/deleting the database objects
(maybe I need to re-visit Security?!?- ugh!).

I guess my approach for adding tables and fields via the FE through vba may
not be the right way to go about this? I want to be able to update my user's
BE without them having to do anything and just distributing the FE to
maintain the backend.

The path I that I embarked on was to connect to the BE and then add tables
and fields but the connection via vba does not have the appropriate access
levels to do this that is why I tried to use the code that I sent...

Any suggestions are welcomed - Thanks for your help!

Jerry

 

['69 Camaro]

I have setup all my tables with Read design;

Read, Update,Insert and Delete data for the Users group and the Admin
user
to cover anyone using a different mdw to login to the database.

If people can use different workgroups to log into the database, then it
isn't secured.

I thought that
these settings would keep anyone from modify/deleting the database objects
(maybe I need to re-visit Security?!?- ugh!).

If the database isn't secured, then anyone can change the security settings
for any particular user-owned object -- and you can't stop them. I suggest
studying the Security FAQ some more and practicing on a copy of your
database. (Don't worry. Security is a complex subject, and hardly anyone
gets everything right on the first try -- or even the twentieth.)

I guess my approach for adding tables and fields via the FE through vba
may
not be the right way to go about this?

It's a good approach, but with User-level Security implemented, it becomes
more complex. I'd suggest securing the database properly, then creating a
new "LocalDBA Group" that has the necessary permissions to add the tables
and fields, then creating new users as members of this group and the Users
Group only. People who need to "upgrade" the latest version would have to
log in as a member of the LocalDBA group in order to perform these
functions. After they were finished, they could then close Access and log
in under their normal User ID to perform their daily functions. However,
there's nothing stopping them from logging into the database as a member of
the LocalDBA group to perform their daily functions, which would give them
more permissions than you want them to have.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

..unny - Thanks for the insights and encouragement on security! One follow up
question - If I have a FE/BE do I need to have security on the FE or just the
BE databases - that is where the tables and data reside.

Thanks again for the help!

Jerry

 

['69 Camaro]

Hi, Jerry.

If I have a FE/BE do I need to have security on the FE or just the
BE databases - that is where the tables and data reside.

One doesn't have to secure the front end, but it's easier to work with a
secure backend when one is already joined to the secure workgroup.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

Thanks for the help!

Hi, Jerry.



One doesn't have to secure the front end, but it's easier to work with a
secure backend when one is already joined to the secure workgroup.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.