Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: Not binding main form to main table?

    Hi folks,

    I read somewhere recently (sorry, can't remember where) that it is generally a bad a idea to bind your main data entry form to your main data table, because it means that Access has to load up lots of data every time you want to do something simple like add a new record.

    I'm now finding this: a well-used application (separate backend, multiple front ends) is starting to run slowly on the front ends (over 100Mb LAN). We have about 60,000 records.

    How might I fix this?

    One way would be to make a different entry-only form which is unbound, so that only when you hit "Save" is all the data copied (by code) into the main record. That sounds a bit ugly and possibly error-prone (there are about 30 data items in this record/form). It also sounds like it would make the "Next"/"Previous" operation hard - and this is something that is well liked by users, they like being able to flip back a few records to see what's been going on as well as to look for possible errors, omissions and duplications.

    Would I have to have two separate forms (one unbound for entry, and another bound for browsing)? There is quite a lot of code behind the form (for validation, popping up subforms etc) so I would really rather not duplicate it. Is there a clever way to give a form a limited recordset for data entry, and switch to the full dataset when browsing?

    How have other people addressed this issue?

    TIA
    CeejayDBF

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Unbound Forms really defeats the whole purpose of using MS Access.Several developers I know, experienced in both Visual Basic database development and Access development, estimate that development using Unbound Forms by experienced developers takes two to three times as long as it does when using Access and Bound Forms.

    If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.
    1. You can create an EXE file which gives total protection to your code/design
    2. You can distribute the db to PCs without a copy of Access being on board
    3. Your data security is far, far better than anything you can do in Access


    What I'd do is have the Form open in DataEntry Mode and have Command Buttons to toggle between only adding Records and Viewing all Records. The users could open the Form, to enter New Records, or open the Form then simply click a Button to View all Records. This way you wouldn't load all Records unless the user specifically needed to. And after switching to View All mode, the user could still add New Records, if desired. Also, this method would require little modification to your current design.

    In Design View, set the DataEntry Property to Yes, then create two buttons with this code behind them:

    Code:
    Private Sub DataEntryOnly_Click()
     Me.DataEntry = True
    End Sub
    Code:
    Private Sub ViewAll_Click()
     Me.DataEntry = False
     Me.Requery
    End Sub
    By the way, while there may be situations where binding your main data entry form to your main data table is undesirable, given the speed of today's processors, I think that advice is probably outdated. The biggest negative about the internet, in my opinion, is that once something is out there, it's pretty much out there forever, even when it's no longer valid.

    Linq ;0)>
    Last edited by Missinglinq; 05-06-12 at 11:28.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    Quote Originally Posted by Missinglinq View Post
    A big part of the reason to use Access for database development is the speed with which it can be created,
    I quite agree. I do have one unbound form, which I inherited from elsewhere, and it's a pain.

    By the way, while there may be situations where binding your main data entry form to your main data table is undesirable, given the speed of today's processors, I think that advice is probably outdated. The biggest negative about the internet, in my opinion, is that once something is out there, it's pretty much out there forever, even when it's no longer valid.
    I agree with the point about outdated advice on the internet! But it's not so much a processor issue as a networking one, and it is certainly causing me real performance problems at the moment.

    Your suggested solution is interesting, I will certainly give it a go. I'm not keen on asking users to choose which mode they are in, but I think I can make that decision for them without them noticing ... start the form in data entry mode, and switch to view-all mode if the user uses any of the browsing/searching functions. Let's see if this makes a difference for me.

    Thanks!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by CeejayDBF View Post

    ...switch to view-all mode if the user uses any of the browsing/searching functions.
    You might have to replace the native Navigation Controls with Custom Controls, but that should be doable.

    Access works well with LAN, but not so well when the Network covers a wider area.

    Good Luck with your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2010
    Posts
    88
    Quote Originally Posted by Missinglinq View Post
    You might have to replace the native Navigation Controls with Custom Controls, but that should be doable.
    Yep, I already have custom navigation and search buttons so I can easily insert any extra code I need

    Access works well with LAN, but not so well when the Network covers a wider area.

    Linq ;0)>
    I wouldn't dream of trying to use Access in a FE/BE setup over a WAN ... this is a LAN, and the network is slow enough to be causing problems. I know this because the front end that lives on the same machine as the backend is snappily quick, but all the other front ends are much slower, even though at least one is exactly the same spec machine as the "server". Upgrading to a 1GB LAN would help, but that's not feasible at present.

    I'm in the middle of something else just at the moment, but hope to get round to trying some things out in a few days. I will report back!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •