Open Table as Read Only

[The professor]

Hi,

I want to be able to open a Table as Read Only, so that a user can browse etc., but neither alter nor enter data - I want them to use the Forms for that.
How then, using VBA code, do you open a Table as Read Only?

Many thanks,
Prof.

 

[Galaxiom]

A user should not open a table.

Put the table as the RecordSource of a form and turn off AllowEdits, AllowAdditions and AllowDeletions properties.

Another alternative is to set the RecordsetType to Snapshot.

 

[The professor]

Actually, I believe you're right - giving them access to a Table isn't really a good idea. I think I can probably achieve the same effect by using a Query. Thanks.

Prof.

 

[Mihail]

You think wrong.
Is the same, with other name

 

[Pat Hartman]

If the user wants the "look" of a table, use a DataSheet style form. There is a missing action in the standard Access Switchboard. It doesn't allow you to directly open a form in datasheet view. You have to create a function to open the form and call the function from the switchboard. I avoid this in one of two ways. The simple way is to create an unbound main form and put the datasheet form on it as a subform. The more complicated way is to modify the switchboard to add an open datasheet form option. This does cut you off from the switchboard wizard so you will have to actually open the [Switchboard Items] table and modify the code for the ds forms but it is a minor inconvenience.

 

[The professor]

Hi Pat,

Thanks very much for that helpful reply. I'll give it go.

Prof.

 

[Galaxiom]

I have never seen the appeal of the Switchboard. It is a form with a RecordSource rendered useless for data purposes by having to manage object lists. Moreover, customisation of what they display is relatively complex.

 

[Pat Hartman]

I like the concept of the Switchboard which is why I use it. Out of the "box" the Switchboard form has a couple of shortcomings that I fixed years ago.
1. It doesn't support enough items on a page so I generally increase the number from 8 to 12.
2. I can't directly open main forms in DS view so I add that option.
3. The close option closes the application but not Access so I change that.
4. I also make a couple of other changes.


Once this is done, I just copy the form and table from app to app and change the color scheme to match the current app. The changes I mentioned are actually only a few lines of code here and there. But once you make them, you need to be careful when using the wizard to add/remove items. Frequently, I just change the table directly.

I started building my own switchboard form that is a basic continuous subform rather than the MS version that works with object lists. I also added the ability to use custom pictures on the buttons. So my switchboard app has two forms. The form to display the switchboard and the form with subforms to define the items.

 

[ChrisO]

A similar thing came up on a Microsoft site very recently.

The code is very small and it looks wrong, but it works. Also works for Queries.

See attachment.

Chris.

 

[Galaxiom]

The code is very small and it looks wrong, but it works. Also works for Queries.

What looks wrong? It is just a Locked subformcontrol with a Table as the SourceObject. The dropdown on the SourceObject property of any subformcontrol lists the tables and queries as choices.

As far as I can see, setting the RecordsetType changes nothing from the defaults.

I once tried using tables in a subform with LinkFields because I thought it would be good to show a subdatasheet. They take forever to load and I have never been tempted since.

And of course there are no events, data access management properties or conditional formatting.

 

[ChrisO]

It might be a case of ‘Hindsight is the only exact science.’

It looks wrong because people would expect a Form as the SourceObject of a subform control yet, initially, there is no Form. Nor is there a Form, used as a subform, in the database window, which is an important factor. It becomes important in not having to create a Form, which is used as a subform, where that Form has fixed Fields.

It is not just a Locked subform control with a Table as the SourceObject. The Locked property of the subform control in my posted database was a misguidance.

It does not matter if the subform control is Locked or not. The requirement is to set the Form, in that subform control, to 2 (Snapshot). It is the Form in the subform control, not the subform control itself, which needs to be protected from change. Hence the line of code:-
Me.ctlForm.Form.RecordsetType = 2
is required if the Record Source of that Form needs to be protected from change. (Make it read only.{RO})

Anything we may have done in the past is hearsay without the proof supplied.
Data access management properties or conditional formatting is irrelevant to the current requirement. (Even assuming we know what they may mean.)

Post #2 reflected the current understanding at the time; “A user should not open a table.” and others also agreed. I think that response needs to be looked more closely especially since the word ‘user’ has not been defined in this thread. That would be another thread based on the ‘user’ level of security.

-------

In my view, if any criticism is to be made of this technique it would have centred on Queries and not Tables. It may be appropriate to view an action Query or it may not. The point here is that, if this technique was well understood then an objection to action Queries should have been raised not an objection to Tables. Since that objection was not raised then I think the objection to this technique is not well understood.

-------

The requirement of this thread is to Open Table as Read Only.
I think that requirement has been fulfilled.

Chris.

 

[Galaxiom]

It looks wrong because people would expect a Form as the SourceObject of a subform control yet, initially, there is no Form.

Loading the SourceObject to an otherwise empty subformcontrol dynamically is not even unsual.

The ability to display a table or query in a subform would only be a surprise to those who always let Access make their subforms forms for them. Most developers who had manually set the SourceObject property would know that a subformcontrol can also display a table or query.

After all, the property is called SourceObject rather than SourceForm.

It does not matter if the subform control is Locked or not. The requirement is to set the Form, in that subform control, to 2 (Snapshot).

Locking the subformcontrol and changing the RecordsetType to Snapshot are two different ways of achieving very similar user experiences. Locking the control and dispalying the dynaset will reflect updates to the records. The Snapshot won't. Either way the data can't be changed in the form.

However as I said earlier, in my experience the performance of Table SourceObjects is terrible so I would still suggest users don't directly view tables.

However I will concur that the OPs requirement is met with your solution.

 

[ChrisO]

I do not understand this point:-

>>Locking the subformcontrol and changing the RecordsetType to Snapshot are two different ways of achieving very similar user experiences. Locking the control and dispalying the dynaset will reflect updates to the records. The Snapshot won't. Either way the data can't be changed in the form.<<

Given the need to not allow updates, can you please explain your point in code by reposting my example after you have applied your modification(s)?

Chris.

 

[Galaxiom]

The dynaset RecordsetType shows updates to the records that have been changed elsewhere. The Snapshot is fixed at the time it is taken.

Either way this form cannot change the data.

Just change the line of code that defines the recordsettype. Zero will return a dynaset which is the default. Since it is the default you can leave the line out completely.

Nothing can be changed via that form so long as the subformcontrol is Locked.

 

[ChrisO]

Well, that’s what I thought you meant but it is incorrect.

Records could still be deleted so:-
Me.ctlForm.Form.AllowDeletions = False
would be required if the Form’s RecordsetType was in Dynaset mode.

So, why don’t we just leave it up to the original poster as to which way they wish to go?

Chris.

 

[Galaxiom]

Records could still be deleted so:-
Me.ctlForm.Form.AllowDeletions = False
would be required if the Form’s RecordsetType was in Dynaset mode.

Thanks Chris.

What an odd concept of a lock. I would not have guessed that.

I am glad the locks on my doors don't just stop people from changing the layout of my furniture but actually prevent them taking the furniture away.

 

[ChrisO]

Making guesses is fine but they must be followed by testing else they remain just guesses.

That is why I asked you to make your changes to the database I posted and post it back. I think if you had done that then you would have picked up the deletion problem.

Yes, it is confusing, even down to the words we use. Consider a thought experiment as a conversation which uses the word ‘browse’.

What do you want to do?
I want the users to browse a table.
Do you want them to be able to make changes?
No.
When do you want them to browse the table?
Now.

Okay, I deliberately wrote the above thought experiment to imply we actually know what is required. If we take that literally then the browse is fixed in time as of Now. The browse is being made on the dataset which was available as of Now, not some time after Now. Hence it is a snapshot of Now and not dynamic in time.

But the above is still an assumption; we do not know the actual requirement. We do not know if it is required to be a snapshot or dynamic. We have both methods but we do not know which one to apply.

Only one person can make that decision and that is the original poster, not us. It may even be that both methods are required under different circumstance but, again, that is up to the original poster, not us.

What we can do is supply different alternatives to the original poster so that they, not us, can make that decision.

Chris.

 

[Galaxiom]

I did make the changes on a copy of your sample.
However I failed to complete a thorough test.

Indeed I have always been unaware of the fact that locking a subformcontrol does not prevent records being deleted even if the SourceObject is a form.

I am very pleased that I now know this.

I was going to say it is a bug since the there is a mismatch between the documentation and the behaviour.

This is what Microsoft says of the Locked Property of a control:

"The default setting of the Locked property is True. This setting allows editing, adding, and deleting data."

http://msdn.microsoft.com/en-us/library/office/ff192278(v=office.14).aspx

However this documentation is clearly wrong too. Firstly the Default value of the Locked Property is in fact False. Secondly the True setting does not allow editing or adding data.

I believe the error in the documentation is the statement that the default state of Locked is True. Then the claim that it allows Editing Adding and Deleting would make some sense.

Then the failure to prevent records being deleted would indeed be a bug in the software.

 

[The professor]

Hi it's me, the original poster.

I've read the thread with interest, but can't claim to have fully understood all of it, as some of the more technical discussions took me beyond my comfort zone, in programming terms, that is.

What I've decided to do is abandon any idea of simply using a Table and there is no obvious/simple way of making it read only. Instead, I've created a Query with only the information I want to show, an am displaying this in a Form (in datasheet view only) and have used that Form's properties to disable any editing, adding or deleting of Records, and so far this seems to be working really well - unless of course I've missed something obvious, which is always a possibility.

Interestingly, I haven't had to use the 'Record Locks' property, which is set to 'No Locks'; as when it's set to 'All Records', it doesn't allow you to sort the data via the column headings, which is a handy feature to have available. The default sort order is set by the Query, but once displayed in Datasheet View, the user can sort the Records using the column headings. The 'Record Locks: All Records' disables this feature, as far as I can tell.

Prof.