unbound forms saving records in access 2003

[Bob]

i have created a unbound local form to record details and wish to save the
record to a table which is linked to the database on the server Access 2003

 

[Baz]

Any reason why you don't just bind the form to the table?

 

[Bob]

The data base is multi user and i tried to let users update the record on
their local pc . i managed to load the details into the unbound details to
reduce time on server but cannot work out how to save the updated details on
the back end which is located on the server . Thank you for taking the time
to respond to my email .Happy new year to you.

 

[Baz]

Sorry, I don't understand. You seem to be saying that you perceived some
advantage to using an unbound form, but I don't understand what.

Access is all about using bound forms and reports. There may be the
occasional very specific situation where an unbound form is appropriate, but
if you want to use unbound forms as a general approach then Access is the
wrong tool for the job.

 

[Larry Daugherty]

You've overthought this thing....

The Front_End/Back_End paradigm assumes that the Front_End on each
user's desktop will link to the single shared copy of the Back_End in
a shared folder on the server. The default open mode *must* be
"Shared". Doing it the way implicit in your question isn't multi-user
at all. Instead it's several independent copies of the application
that you are now trying to synchronize.

Post back if you have questions.

If you're just starting in Access development then I suggest that you
also lurk

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

and that you visit www.mvps.org/access It's a site filled with Access
developer lore and useful to developers at all levels.

HTH

 

[David Portwood]

In a multiuser environment, I had a bound form and found that ham-handed
users would leave records locked indefinitely from that form. This seemed to
wreck performance of reports and queries.

I changed the form to one with unbound controls. Now nothing is ever locked
for more than a few milliseconds and this under program control only. This
small time period is easily covered by the default retry period. My users
can now diddle on the unbound form until the cows come home without hurting
anything. (I hate users...)

I strongly recommend using unbound forms versus bound forms in a multiuser
environment. I should probably avoid absolutes...

As for updating the table from data in the unbound form, you must (what
follows is from memory, check the details, but the strategy is correct) open
the unbound form as a modal dialog, with WindowMode:=acDialog. This is
important, because now when you open the unbound form, program execution
stops and waits in the calling form.

On the unbound form, the OK button hides (Me.visible = False) but does not
close the unbound form. Execution automatically resumes in the calling form
with the unbound form still loaded.

In the calling form, you check to see if the unbound form is still loaded.
There is a simple way to do this, I forget the syntax, something like "if
CurrentProject.AllForms("formname").loaded = True". Maybe someone else could
help out here? Or I could check it at work tomorrow and get back to you.

Anyway, if the above condition is true, the unbound form is still loaded, so
you can access the controls and write their data into your table. Then close
the still-loaded form.

In the Close/Cancel button on your unbound form, just do Me.close.

Btw, unbound forms require extra work validating data. Do this from the OK
button, validate all controls from there, not from the BeforeUpdate method
of each control, which is where you would put control level validation for a
bound form.

 

[Baz]

See comments inline.

In a multiuser environment, I had a bound form and found that ham-handed
users would leave records locked indefinitely from that form. This seemed
to wreck performance of reports and queries.

By default Access uses optimistic locking, which means that users *cannot*
lock records indefinitely. The only way this can happen is if you change
the form's RecordLocks property from "No Locks" (i.e. optimistic locking) to
"All Records" or "Edited Record" (either of which would be madness in a
multi-user environment).

I changed the form to one with unbound controls. Now nothing is ever
locked for more than a few milliseconds and this under program control
only

Which is exactly how Access works if you leave RecordLocks at it's default
setting.

This small time period is easily covered by the default retry period. My
users can now diddle on the unbound form until the cows come home without
hurting anything. (I hate users...)

As they can on a bound form if you leave RecordLocks at it's default
setting.

I strongly recommend using unbound forms versus bound forms in a multiuser
environment. I should probably avoid absolutes...

That's crazy. You are simply doing bound forms wrong. If I were building
all-unbound forms I would throw Access in the bin, bound forms and reports
are the *entire* point of Access.

 

[David Portwood]

By default Access uses optimistic locking, which means that users *cannot*

lock records indefinitely.

Whether or not records are locked indefinitely is not really my point. They
need not be locked for more than a few milliseconds at most, using unbound
controls.

In most situations, in a multiuser environment, I would prefer to use an
unbound form for editing records rather than allowing the user to edit the
record directly using bound controls. A user might begin editing a control,
then get up and go to lunch, leaving the record locked. I don't know how
long the record remains locked in this case, but using unbound controls it
is not locked at all.

Which is exactly how Access works if you leave RecordLocks at it's default
setting.


As they can on a bound form if you leave RecordLocks at it's default
setting.

I am suspicious of this statement. I think there may be an issue of degree
here.

That's crazy. You are simply doing bound forms wrong. If I were building
all-unbound forms I would throw Access in the bin, bound forms and reports
are the *entire* point of Access.

I'm a crazy guy. Maybe you are right. I can only say that I have noticed
fairly dramatic performance improvements in a multiuser environment when I
(mostly) switched to unbound forms for editing. I am sure you will say the
performance benefits occurs because I was doing something wrong with the
bound forms. Maybe so.

I feel a bit like Linus Pauling arguing with the medical community about the
benefits of vitamin C based mostly on personal and anecdotal evidence. All
manner of facts were thrown in his face, to which he had no good response.

I still urge the original poster to try using unbound forms (for editing
purposes) for himself. I think he will be very happy with the result.

 

[Baz]

Whether or not records are locked indefinitely is not really my point.
They need not be locked for more than a few milliseconds at most, using
unbound controls.

With No Locks specified on a bound form, the record is locked for no longer
than it takes to actually perform the update.

In most situations, in a multiuser environment, I would prefer to use an
unbound form for editing records rather than allowing the user to edit the
record directly using bound controls. A user might begin editing a
control, then get up and go to lunch, leaving the record locked. I don't
know how long the record remains locked in this case, but using unbound
controls it is not locked at all.

No, you simply don't understand. When a bound form's RecordLocks property
is left at the default setting of No Locks, a record is *not* locked while
the user is editing it. It is only locked for the duration of the actual
update. When you update a record, it *must* be locked for the duration of
the update. Whether the update is initiated by a bound form, or by you
submitting an UPDATE query or updating a recordset, it's exactly the same:
the record *must* be locked for the (very brief) duration of the update.
But, and I stress again, unless you have changed the RecordLocks property to
Edited Record or All Records, the record is *not* locked while the user is
editing it.

I am suspicious of this statement. I think there may be an issue of degree
here.

Absolutely no issue of degree or anything else: unless you specify that you
want pessimistic locking (i.e. Edited Record or All Records) there is *no*
lock on a record *except* for the very brief duration of the actual update
of the database.

I'm a crazy guy. Maybe you are right. I can only say that I have noticed
fairly dramatic performance improvements in a multiuser environment when I
(mostly) switched to unbound forms for editing. I am sure you will say the
performance benefits occurs because I was doing something wrong with the
bound forms. Maybe so.

Exactly so. But, whatever reason anyone might feel they have for using
unbound forms, what is crazy is persisting with using Access in such a
circumstance. The benefits of using Access all accrue from it's wonderful
capabilities with bound forms/reports. If you want unbound, there are much
better tools.

I feel a bit like Linus Pauling arguing with the medical community about
the benefits of vitamin C based mostly on personal and anecdotal evidence.
All manner of facts were thrown in his face, to which he had no good
response.

If you have blind faith that you are right then there is no point in
discussing it.

I still urge the original poster to try using unbound forms (for editing
purposes) for himself. I think he will be very happy with the result.

But I will urge the OP to ignore you on the grounds that you are completely
wrong. He might be happy in functional terms with the results of using
unbound forms but, if they understood what was going on, I'm sure his
paymasters would not be happy with the colossal waste of time/effort/money
invested in circumventing something which Access does perfectly well anyway.

 

[Larry Linson]

(I hate users...)

As for me, I _love_ users. Without users, what need would there be for our
developer services? If this stuff were easy, they'd be doing it with
minimum-wage temps from a temp service.

If you were having performance problems because of locked records, then
chances are you could have solved the problem a lot simpler, easier, and
faster by using appropriate locking options. There is just no reason for
using unbound forms in a multiuser environment unless you are trying to
squeeze the last ounce of performance before you convert to client server,
and even in that circumstance it often is a waste of effort.

Get to know your tools and you can save yourself a great deal of time and
effort... a great deal.

Larry Linson
Microsoft Access MVP

 

[Baz]

Thank you Larry, I was beginning to think the lunatics had taken over the
asylum.

 

[Bob]

Thank you for your support concerning my post , i may not have may my reasons
clear and therefore caused misunderstandings . i have obtain an example of
the the process of using unbound forms in a shared data base and the
mircosoft expert by the name of John Viescas gives it the name of Pushing
the envelope, and he bares out what you have written.

 

[Bob]

Thank you that was most helpful . The syntaax you mentioned would be much
appreciated. i am committed to this procedure and as i am doing it to help a
charity i am stuck with access Thanks again Bob Fortune

 

[Baz]

http://www.mail-archive.com/[email protected]/msg01927.html

Hardly a ringing endorsement is it?

Certainly Access will run out of steam if you throw enough users at it, but
it will be an overall performance problem, nothing to do with the illusory
locking non-issue which David Portwood is so concerned with (even though he
has evidently failed to grasp even the most basic principles e.g. the
difference between optimistic and pessimistic locking).

If you are "pushing the envelope" with Access in performance terms (and
assuming you haven't done something stupid like choosing the wrong locking
options), abandoning bound forms is still the wrong answer. The *right*
answer is to keep your bound forms and to convert your back end to SQL
Server. It's easier, cheaper and much more effective than trying to squeeze
micro-efficiencies out of Access by turning it into something it doesn't
want to be.

(Incidentally, David, you can't screw up the locking with SQL Server, it
only supports optimistic locking).

And if, despite everything, you are a masochist who likes doing things the
hard way, then for unbound forms Delphi, Visual Studio 2005 and even good
old Visual Basic 6 are all better choices than Access.

But hey, what do I know? You guys have, what, *months* of experience
between you, so I guess you must know what you are doing.

 

[Bob]

Thank you for time , I admit I am a novice to access and was advised to seek
help from the forum. My original post was never about locking issues , and
the server in use is very low speck, so my only goal was to reduce time the
user was accessing the server and allow him/her to work on a particuar record
in their leisure. I regret if my poor expanation of what was trying to
acheive as cause a strorm in a tea cup.