protecting a form in datasheet view

[Bruce927]

I have created a form in the datasheet view, but the problem I am having is that when other people use the form they reset size of the columns or hide them. How can I control the properties of the form so that it will reset back to all of the original column widths or un-hide columns when the form is closed.

[zmbd]

I feel your frustration.

I ran across a small bit of code (may have been in one of the many books I've read) that forces users to change to design mode to make certain changes to the forms.

Make a copy of your frontend/database and in a standard vba module (create a new one) cut and past the following code:

>Note: I usually only run this code either after I'm done with the database design or on a copy of the database that I push out to the users for design feedback.

Sub FixAllowDesign()
Dim objFrm As AccessObject, frm As Form
' Go through every form in the database
For Each objFrm In CurrentProject.AllForms
   ' Open the form in Design view
   DoCmd.OpenForm FormName:=objFrm.Name, _
   View:=acDesign
   ' Set the form object for efficiency
   Set frm = Forms(objFrm.Name)
   ' Check and reset the AllowDesignChanges property
   If frm.AllowDesignChanges = True Then
      frm.AllowDesignChanges = False
      ' Save the change
      DoCmd.RunCommand acCmdSave
   End If
   'release the form
   Set frm = Nothing
   ' Close the form
   DoCmd.Close acForm, objFrm.Name
' Loop to the next form
Next objFrm
End Sub

Now, play with the forms in your copy. If you're OK with how this effects the forms then you can either push the copy out to the users or run the code in the production database.

This won't fix all of the issues that you're after; however, it'll go a long ways to lowering the bloodpressure.

Splitting the database into a front and backend then making an "mde" or "accde" from a COPY of the front end as the user can no longer open the forms in design mode either.

[zmbd]

opps... hit enter too soon...
you can also design a form so that when default view is set to "Continuous Forms" it looks very much like a datasheet view and then disallow datasheet view.

I still run the code I posted even when I do the above.

You might also read this http://allenbrowne.com/ser-69.html

[Bruce927]

Thank you for the suggested code. I already have the data base split for front end and backend. Our company limits the number of people who have developer status and most users have read only capacity. But this has not prevented the data sheet view from being altered. The only other solution I can come up with is to a router which copies the front end from the network drive and puts it into the temp folder on the user’s C drive. This way it allows the user to manipulate the view while they have the database open. But once it is closed these changes do not cascade back to the frontend. By the way I do not make it common knowledge to everyone that they are making a copy on their hard drive, they must always use the router to open the database from the network drive.

[TheSmileyCoder]

I have a developer frontend, which I create a MDE (or accde) and place that on the network. At the same time I update a version number in a central database. Next time the user opens their local frontend, the first thing it checks is the version number. If their local version number is different from the server it opens up a update db, which copies the MDE to their computer. That way each user always has their own frontend.

[Seth Schrock]

Smiley, could you send me a PM with a copy of that system? I have several databases at work that I would love to have a system like that.

[TheSmileyCoder]

I have been meaning to make a article out of it for some time. But currently I am working on creating a article series on the treeview based on the webinar I hosted last Thursday. For now, I think we have derailed this topic enough. :)

[twinnyfo]

All,

Won't this also be alleviated by setting the options for the current database? There is a check box for "Enable design changes for tables in Datasheet view." I thought unchecking this toggle would prevent users from being able to alter tables design in cases like this once the db has been published....

[zmbd]

@Twinney: That is part of what the code does that I posted in #2

@Bruce: As TheSmileyCoder pointed out in #5 and I pointed out (but not as clearly) at the bottom of #2, The distribution of the front-end, preferably as either MDE or ACCDE will take care of this in that your end-user will have their own copy of the front-end.

The only issue with the front-end is if there are changes made. As Smiley does, so I also have a way of pushing the front-ends out that ensure that the most recent upgrade/version is in-use by my end-users. The nice thing about the front-end... if the user bungs the one they have, simple fix to have a new one pushed down... I have just such code built in that will force that upon an admin flag in the user profiles - but that's a whole other thread!

[Bruce927]

Thank you I will give this a try.