Splitting/restricting a form? (Office XP)

Topic

New Reply

Ok, I have a form that I want to restrict a part of. My main form has many command buttons to open separate forms, and throughout those separate forms, I want the top half to be read only…or if I can restrict certain columns to read only, and others to be editable. Is there a way to do this? Is there a way to have a table opened that the first, say,10 columns are read only and those after it are editable?

Reply | Quote

Replies

Access does not provide security for tables at the level of individual rows or columns. In a form bound to a table, you can lock controls for editing by setting the Locked property to Yes. Usually, you do this in design mode, but if necessary, you can do it in code in the On Open or On Load event of the form.

Users who know how to get into the database directly will be able to circumvent this type of security.

Reply | Quote

What if I have a FE/BE design? What kind of measures would I have to take to ensure they can’t access it directly? I’m going to have a password on the back end…

Reply | Quote

The only really good way of securing a database is to apply user-level security. You’d need it on the backend to ensure that users can’t get at the tables directly, and on the frontend to ensure that users can’t tamper with the design of forms. (Another way is to create an .MDE database, but ashrafk demonstrated recently that it is possible to break into an .MDE database.)

See WendellB‘s tutorial on user-level security The Secrets of Security; it contains many useful links. Another good reference is jacksonmacd‘s security paper available at Database security in Microsoft Access.

Reply | Quote

The only really good way of securing a database is to apply user-level security. You’d need it on the backend to ensure that users can’t get at the tables directly, and on the frontend to ensure that users can’t tamper with the design of forms. (Another way is to create an .MDE database, but ashrafk demonstrated recently that it is possible to break into an .MDE database.)

See WendellB‘s tutorial on user-level security The Secrets of Security; it contains many useful links. Another good reference is jacksonmacd‘s security paper available at Database security in Microsoft Access.

Reply | Quote

What if I have a FE/BE design? What kind of measures would I have to take to ensure they can’t access it directly? I’m going to have a password on the back end…

Reply | Quote

Hans’ comments on security are spot-on, but there is a way to arrange things using Access User Security and two tables that might achieve what you want. If you take your table and split it into two tables, one with the first 10 columns (fields), and a second with the same primary key as the first table, and the remaining columns. You can then create a form and a subform and display the read-only data on the main form and the editable data on the subform. You still need Access User Security so you can set the first table to be read only for all but some sort of administrator login. Hope this gives you some additional ideas.

Reply | Quote

The same result can be accomplished by retaining just one table, but creating two RWOP queries. Apply different permissions to the two RWOP queries, and base the form and subform on the two queries. This has the benefit of keeping all the info in a single table.

Reply | Quote

The same result can be accomplished by retaining just one table, but creating two RWOP queries. Apply different permissions to the two RWOP queries, and base the form and subform on the two queries. This has the benefit of keeping all the info in a single table.

Reply | Quote

Wendell, love your ideas, I will definitely try them. Instead of tables though, could I use queries? I don’t really want to split those tables. I’ll try the query thing….how do you *define* a subform? The only time I’ve seen it is when I create forms from two tables…..could I just edit the autoform? Either way, whatever works/whatever suggestions you have I will definitely try. – Thanks!

What is an RWOP query?

Reply | Quote

RWOP: Read With Owner’s Permission. See the Security FAQ for a full explanation. there is a link on my web site.

In essence, you remove all permissions for your users to the base tables. Then you create new queries, and include only the fields and records that you want the users to see. Fields are included/excluded by dragging them to the query grid. Records are included/excluded by applying various selection criteria. Then you change one of the properties of the queries from “Users” to “Owners”. This allows the users to run the query with the permissions that the owner has on the base tables, rather than the permissions that the user has on the base tables.

Reply | Quote

There isn’t a small *blurb* of code or something I can enter somewhere on either my forms or my queries?

Reply | Quote

Each form has an “AllowEdits” property, and each subform has a “Locked” property. You can manipulate them using VBA, probably in the Form_Open event. You can use the CurrentUser() function to determine who is running the application and make the appropriate changes to the AllowEdits and Locked properties.

Under this scheme, any user who opens the tables or queries directly will bypass your “security”.

Reply | Quote

What if I disable the Shift bypass key, and only allow them to see the forms I create when they open the database? If they don’t know the password, then they can’t bypass my menus and have administrator access to the tables/queries (only Read-Only)…

Reply | Quote

If you haven’t applied user-level security, they can still link to tables in the database from another database, or use ODBC from Excel.

Reply | Quote

There is user-level security, and they’re on a FE database…..maybe redundant, but the guys I’m working with aren’t too bright, and they’re really not looking to TRY to get into the tables, I’m doing the security part for my own knowledge..it isn’t a requirement.

Reply | Quote

There is user-level security, and they’re on a FE database…..maybe redundant, but the guys I’m working with aren’t too bright, and they’re really not looking to TRY to get into the tables, I’m doing the security part for my own knowledge..it isn’t a requirement.

Reply | Quote

If you haven’t applied user-level security, they can still link to tables in the database from another database, or use ODBC from Excel.

Reply | Quote

What if I disable the Shift bypass key, and only allow them to see the forms I create when they open the database? If they don’t know the password, then they can’t bypass my menus and have administrator access to the tables/queries (only Read-Only)…

Reply | Quote

Each form has an “AllowEdits” property, and each subform has a “Locked” property. You can manipulate them using VBA, probably in the Form_Open event. You can use the CurrentUser() function to determine who is running the application and make the appropriate changes to the AllowEdits and Locked properties.

Under this scheme, any user who opens the tables or queries directly will bypass your “security”.

Reply | Quote

There isn’t a small *blurb* of code or something I can enter somewhere on either my forms or my queries?

Reply | Quote

RWOP: Read With Owner’s Permission. See the Security FAQ for a full explanation. there is a link on my web site.

In essence, you remove all permissions for your users to the base tables. Then you create new queries, and include only the fields and records that you want the users to see. Fields are included/excluded by dragging them to the query grid. Records are included/excluded by applying various selection criteria. Then you change one of the properties of the queries from “Users” to “Owners”. This allows the users to run the query with the permissions that the owner has on the base tables, rather than the permissions that the user has on the base tables.

Reply | Quote

Wendell, love your ideas, I will definitely try them. Instead of tables though, could I use queries? I don’t really want to split those tables. I’ll try the query thing….how do you *define* a subform? The only time I’ve seen it is when I create forms from two tables…..could I just edit the autoform? Either way, whatever works/whatever suggestions you have I will definitely try. – Thanks!

What is an RWOP query?

Reply | Quote

Hans’ comments on security are spot-on, but there is a way to arrange things using Access User Security and two tables that might achieve what you want. If you take your table and split it into two tables, one with the first 10 columns (fields), and a second with the same primary key as the first table, and the remaining columns. You can then create a form and a subform and display the read-only data on the main form and the editable data on the subform. You still need Access User Security so you can set the first table to be read only for all but some sort of administrator login. Hope this gives you some additional ideas.

Reply | Quote