Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Bound vs. Unbound forms

    I have a project manager that insistes that every form be unbound and that we never use autoincrement - we write functions to get next number on primary key number fields. My contention is that in a Jet environment the autonumber bound form will present less problems , save data faster and is easier to maintain as you do not have to always add that extra line for

    myrecordset!field = myform!field

    every time a new field might be added to the table

    the overall maintenence in my humble opinion is easier.

    Am I missing the boat? he says unbound is cleaner faster and more "accurate" (what ever that means)

    Any thoughts guys?
    Dale Houston, TX

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    Different tools for different jobs. My current lean is unbound forms but you're right there is more work in setting them up. The flip side is that I can do a lot of insanity with the recordsource that I haven't been able to pull off with bound forms. Plus, I'd rather it not save the data on every exit of a field.
    For your particular app, you may be absolutely right. We all have our favorite approaches and techniques.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    [bias]
    go unbound
    upside is total control
    downside is total control (as in you have to control everything)
    [/bias]

    you don't necessarily have to get sucked into the
    myrecordset!field = myform!field
    trap - why not loop thru the fields in the recordset?

    ...but i do love autonumbers - they are so meaningless: a unique (pun intended) quality that i value highly.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    As Starkmann and Izy state, the right coding for the right job.

    Unbound forms are great for a multi-user environment where you need to have the form open fast and don't want to worry about tables bound to a form with users in the form for a long period of time. Especially slow network environments. I used unbound forms for the customer entry screens for an application I wrote which was utilized by hundreds of users. Users tended to keep the main customer entry form all the time (even while they went to lunch) and if the form was bound to a table, time-out and networking issues become a problem. But I also had dozens of other popup forms which were bound.

    There are some forms though where it simply doesn't pay to write code to retrieve/update/delete recordsets. You can get "unboundilitis" where you think "everything" must be written unbound. Not true. It's more code writing for forms which are unbound and if you need to complete a project within a month (verses 6 months), you have to look at how you're going to do it. If user's tend to keep a form open all the time (i.e. customer entry forms), making them unbound makes sense. If you have millions of records, multiple users, and need a form to handle it, unbound forms make sense. But for a popup form where the user enters a few fields quickly and then closes the form, it's your option on the amount of pain and time you want to go through.

    Keep in mind that using jet tables and bound forms gives you limitations (access tables have a hard time managing a form bound to a jet table with more than 1/2 dozen users in it at the same time - especially large recordsets and slow networks.) Hence the philosophy "Access isn't an Enterprise level application." - hogwash! If you use MSAccess as a front-end (like you would with other programming languages), it's Enterprise level (I'll debate this all day and prove it.) If you use MSAccess with Jet tables and bound forms, it isn't. Using unbound forms avoids some of those jet table limitations and you can expand the number of users in the same form at the same time (why not put the coding techniques taught in school to use?)

    Regarding the autonumber increment...having an autonumber field just plain makes sense and you don't benefit making this a number field where you have to maintain finding the next number, not duplicating it, etc.. You open yourself to problems. Let the autonumber field do the work. That's why it's there. Obviously your partner has not had to deal with a recordset in the millions size and compare the time of having the field an autonumber verses looking up the last number and generating a new number (negligable on a fast network but on a slow network with daisy chain hubs and you're counting every second....).

    ...he says unbound is cleaner faster and more "accurate" (what ever that means) - Yes unbound forms are definately faster - hands down. And cleaner if the code is designed correctly. More accurate? That depends on what the meaning of "more accurate" is. If it means that you standardize your retrieve/update/delete code so it's the same universally by all forms accessing the same tables, yes. If it means having an unbound form will have more accurate information than bound forms, that can be debated and depends on your setup. If I design a really crudy procedure to update data, an unbound form could be more inacurate if I forgot to let's say...write data from a certain field on a form. On a bound form, it's difficult to dispute that field not being updated. You have more of a chance missing the field in writing a procedure to update it than on a bound form.

    In my opinion and based on my experience, the best setup I had was when I used an MSAccess front-end, SQL Server back-end tables and stored procedures to retrieve/update/delete records in the table. The main customer entry form was unbound. Since new Energy Conservation programs were appearing/disappearing, all I had to do was add a button to the customer entry form to open the form/mdb to enter the supporting information. I had over 5 million customers in the customer table and using unbound forms, retrieving a customer happened very, very fast (with hundreds of users in the same interface.) All the customer information was stored in 1 SQL Server database and I created a new SQL Server database for the other information when a new program came online, linking the tables to the main customer tables. If I needed to add a new field to the customer table, I simply created that field in the table, modified the retrieve/update/delete stored procedure and added the field to the interface form.

    So in conclusion to all this babbling (apologizies - I've babbled an awful lot here), unbound forms do take more coding time but are great for large recordsets, multiple users and especially slow networks. I wouldn't necessarily design unbound forms for say...a Travel Voucher program which had a few thousand records and was utilized by 1 or 2 users at the same time and I had a month to do it (depends though.) But for a customer entry screen with hundreds of users entering data at the same time and millions of records...definately.
    Last edited by pkstormy; 10-26-07 at 19:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I know I know - It's just that the freaking form has has over 80 fileds and I am the guy having to do the work

    I agree with IZy on looping through the recordset = I will try that makes sense - I do call my textboxes the fielnames with the "txt" in fron of it. But we use unbound everything!!!! Even if it is to add on piece of data or next record or previous record or find record:

    I swear I am going to scream if I see another
    myfield = myrecordset!fieldname
    thanks guys.
    Dale Houston, TX

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    PK,

    You reminded me of the latest post on the Access Team Blog, about using Access as an in house front end, MSSQL as a DB and Ruby on Rails for a web component.
    http://blogs.msdn.com/access/archive...-services.aspx

    Lately we have been trying to hire a department manager, I was surprised when all of them wanted to bag on Access as being useless. Ignorance....

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Starkmann,

    Thanks for the link. I liked what was said in it and Ruby on Rails sounds interesting. I tend to get a tad descriptive when it comes to ingorance of a programming tool and pre-categorizing its non-use as an enterprise product. While it's true that some tools are better than others, I was in the beginning stages of Access 1.0 and it's development into today's product (I was using dbase at the time and was looking for something new (more graphical) to develop in when the store clerk handed me a free copy of Access 1.0 and said "here - try this.") Every since then, I was hooked and was calling the Access developers daily in their development of Access 2.0.

    While I've developed in everything (from dbase, Pascal, foxpro, vb, Assembly, etc...etc...), I've never found another product I could create a full-featured program in within a day (but I'm most likely biased from my years of using it.) When someone says MSAccess isn't Enterprise level, I have to ask..."Are you talking about Jet tables or are you talking about Access as a front-end?" Can you develop a front-end in say MySQL or SQL Server? No...I think not. But if you combine Access with SQL Server (which Microsoft has worked on implimenting), don't you get the best of both worlds? An easy-to-develop front-end combined with an easy-to-develop back-end. When I first got my copy of SQL Server, I didn't need a book to use it. I started creating tables immediately. Same with Access. I can't say I could necessarily do that with other tools but then again, everyone has a different knowledge-grasping level. Although I've done my fair share of SQL Server coding, for those who say real developers program in SQL Server (or Oracle, MySQL, etc..) I have to ask, "How are you going to get the data to and from the users?" There has to be a good front-end (web or non-web) or the best table structure and Server coding in the world is essentially pointless (like having a super-turbo engine for a car without the rest of the car.)

    I've seen Microsoft spend a lot of effort with MSAccess throughout the years and although there are many, many anti-Microsoft fans, you can't deny their effort to produce an easy-to-use product. Perhaps too easy as the critics tend to focus on finding it's faults rather than focusing on it's merits. If I were developing a product such as MSAccess or SQL Server and I had to make a call on an easy-to-use feature verses difficult-to-use but more powerful feature, it might be a tough call and I'm guessing the development team has to face these decisions daily. Personally, I don't care if someone gets rich developing products such as Microsoft has (even if they have to run over a few bugs to do it as long as they PROPERLY compensate the party - although competition is always healthy) but there are those who envy the almighty dollar and people who get excessively rich with it. Greed and envy are the biggest downfalls in today's society but gaining riches from trying to produce a good quality product (and continually trying to improve it) should not be an open door for critizism. Everywhere I go (at least in the Madison, WI area) there is MSAccess. I'm not sure if that's true where you are but for myself, I try to develop in what seems to be popular and easy.

    I'm not willing to "struggle" trying to develop with another product simply because it's NON-Microsoft and has 1 or 2 features Microsoft didn't include in their product. I've heard the stories about "Wow - this product can do this which Microsoft can't do" or the "Look at the bug I found with this Microsoft feature" but when you compare another product against all the things a Microsoft product CAN do, it ultimately loses. I worked with an extremely talented Pro-Linux and very Anti-Microsoft individual who often tried to gear users to a Linux operating system verses a Microsoft operating system. I had no problems with that but spending weeks on trying to get Linux to work on the user's computer I couldn't help but ask "Is the user really benefiting from this?" Of course I could go on and on about the Linux vs Microsoft debate (I even have Linux on one of my computers at home.)

    Don't get me wrong though, Microsoft has failed with several products (and features). But their continued drive to enhance their products for users and developers keeps me coming back to them. Even though there are many things I don't like about MSAccess, they far outweigh the things I do like.

    After all, who can't say they've developed a program which ultimately was a "flop" and none of their users ever used or only used for a month. Or what developer can't say that they put in a feature (that they thought was the greatest thing in the world), which no one ever used.

    ok...I have to stop now or I'll end up writing another book. Again, I've babbled to a point that could draw up some controversy but I have my opinons and I don't mind reading other opinions.
    Last edited by pkstormy; 10-27-07 at 15:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Having to calculate your own auto increment is stupid - in fact, having your project manager tell you that is stupid. Tell him to go away and leave you to do what you know best.

    Ahem. Anyhow, I'd personally stick to unbound. I feel this allows greater control because you have to code every event and tehrefore you control what goes in your tables and when.

    Creating your own auto-icrement is re-inventing the wheel. Remember that the database can do a large chunk of the work for you (enforcing RI, etc) so doing this yourself is just adding pointless time on to a project. (that sounds like a good arguement to go to your PM with (you're welcome))

    Enjoi Dale
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    > Having to calculate your own auto increment is stupid

    depends - if you need consecutive numbers, that's the only way, identity/autonumbers fail....

    unbound/bound - Access is designed for bound forms, and does so extremely well. Going completely unbound, is throwing everything Access is designed for out the bathwater - so why use Access at all, when you're programming everything yourself, that Access can give you for free? Why not use Classic VB or .Net ...?
    Roy-Vidar

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I agree with everything said so far - even when it is contradicting itself - because infact both sides have merits. I am strongly opposed to the getnextnumber functions I have to write for every table, especially to get an id that is only used by the developer - if autonumber was so bad it certainly would not have made it's way in some form or fashion to SQL and other mission critical backends.

    BETTER control of the data FOR UNBOUND is not really a valid argument - any data that can be accepted by the table for Unbound can be accepted or rejected the same for a bound field. Once the user clicks save for bound or unbound the data is saved and all edits work the same (bound - change the field data and tab out or unbound - change the field data and click save)

    For SQL backend data I am a fan of dsn-less and ADO but for Jet - I really feel programming for unbound forms for data entry is a waste of time

    With that said I do have more time than money so I might as wll do what I am told.
    Dale Houston, TX

Posting Permissions

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