Tags: access, created, database, details, form, forms, linked, local, microsoft, mysql, oracle, record, records, save, saving, server, sql, table, therecord, unbound

unbound forms saving records in access 2003

On Database » Microsoft Access

14,943 words with 9 Comments; publish: Fri, 30 May 2008 20:37:00 GMT; (250140.63, « »)

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

RFortune

All Comments

Leave a comment...

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

      "Bob" <Bob.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      news:0928FA10-5E67-485F-ABB3-6A025E43B814.ms-access.questionfor.info.microsoft.com...

      > 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

      > --

      > RFortune

      #1; Fri, 30 May 2008 20:38:00 GMT
    • 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.

      RFortune

      "Baz" wrote:

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

      > "Bob" <Bob.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      > news:0928FA10-5E67-485F-ABB3-6A025E43B814.ms-access.questionfor.info.microsoft.com...

      >

      >

      #2; Fri, 30 May 2008 20:39:00 GMT
    • 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.

      "Bob" <Bob.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      news:898A5E34-8A46-4D91-B206-8EFBD2E628A5.ms-access.questionfor.info.microsoft.com...[vbcol=seagreen]

      > 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.

      > --

      > RFortune

      >

      > "Baz" wrote:

      #3; Fri, 30 May 2008 20:40:00 GMT
    • 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

      -Larry-

      "Bob" <Bob.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      news:898A5E34-8A46-4D91-B206-8EFBD2E628A5.ms-access.questionfor.info.microsoft.com...

      > 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[vbcol=seagreen]

      > to respond to my email .Happy new year to you.

      > --

      > RFortune

      >

      > "Baz" wrote:

      to save[vbcol=seagreen]

      Access[vbcol=seagreen]

      #4; Fri, 30 May 2008 20:41:00 GMT
    • 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" <baz.ms-access.questionfor.info.nospam.nowhere> wrote in message

      news:477a18f8$0$13941$fa0fcedb.ms-access.questionfor.info.news.zen.co.uk...

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

      > "Bob" <Bob.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      > news:0928FA10-5E67-485F-ABB3-6A025E43B814.ms-access.questionfor.info.microsoft.com...

      >

      #5; Fri, 30 May 2008 20:42:00 GMT
    • See comments inline.

      "David Portwood" <Mondrogan.ms-access.questionfor.info.yahoo.com> wrote in message

      news:477a6691$0$9621$4c368faf.ms-access.questionfor.info.roadrunner.com...

      > 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.

      #6; Fri, 30 May 2008 20:43:00 GMT
    • > 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.

      #7; Fri, 30 May 2008 20:44:00 GMT
    • "David Portwood" <Mondrogan.ms-access.questionfor.info.yahoo.com> wrote in message

      news:477b4a49$0$15398$4c368faf.ms-access.questionfor.info.roadrunner.com...

      > 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.

      #8; Fri, 30 May 2008 20:45:00 GMT
    • "David Portwood" <Mondrogan.ms-access.questionfor.info.yahoo.com> wrote

      > (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

      #9; Fri, 30 May 2008 20:46:00 GMT