Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: ADO recordsets vs unbound forms with select and update queries

    So, I am reading a bit here and there, and the whole recordset thing is "starting" to sink in, but I am wondering about the advantage of doing this over say unbound forms where I open a form with an argument that retrieves one record of data, and I make appropriate changes and then save the changes with an update query.

    I had thought that the recordset model was better the more I read about it until I considered:
    Instead of using autonumber fields if I increment my primary fields myself, I have to lookup the latest primary field value the instant before I write a new record - in case from the time I opened the form until the time I submit the data someone else has added something to the table.

    The only argument I can see is being able to navigate backward and forward, and on "that type of form" I could see the value, but my preference is except for certain applications (letting a dispatcher see all of the work orders and step through them for example), when we get to distinct units (customer, addresses, etc) I generally like to have a have a continuous subform with a "list" of the subform items, and if I want to edit or add a new item, I would launch a new form and then refresh/requery the list.

    What are the thoughts on database design, or best application here?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I always have an autonumber field in the table regardless (it's not like having an autonumber field is doing any damage having it.) There are more issues with NOT having an autonumber and 0 issues having one.

    I believe healdem (and/or Izy) are big into unbound forms. For me, it comes down to a time thing and overall recordset count. Unbound forms are great for large recordsets but as you may have found out, it's a little more time consuming designing them.

    If you're designing unbound forms, you may want to have 3 functions:

    UpdateCustomerID (updates or adds a new record)
    GetCustomerInfo (retrieves customer info to the form)
    DeleteCustomer

    So you might have a function like this:

    Function GetCustomerInfo(CustomerID as variant)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustomerID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    msgbox "No customer returned."
    rs.close
    set rs = nothing
    exit function
    else
    Forms!MyCustomerForm!CustomerID = rs!CustomerID
    Forms!MyCustomerForm!FirstName = rs!FirstName
    Forms!MyCustomerForm!LastName = rs!LastName
    blah
    blah
    blah
    rs.close
    set rs = nothing
    End if

    and then the UpdateCustomerInfo takes what is on the form and writes it to rs (ie...
    rs!FirstName = Forms!MyCustomerForm!FirstName
    ....
    ....
    ...
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2003
    Posts
    103

    Cool Thanks, that's helpful

    Quote Originally Posted by pkstormy
    I always have an autonumber field in the table regardless (it's not like having an autonumber field is doing any damage having it.) There are more issues with NOT having an autonumber and 0 issues having one.

    I believe healdem (and/or Izy) are big into unbound forms. For me, it comes down to a time thing and overall recordset count. Unbound forms are great for large recordsets but as you may have found out, it's a little more time consuming designing them.

    If you're designing unbound forms, you may want to have 3 functions:

    UpdateCustomerID (updates or adds a new record)
    GetCustomerInfo (retrieves customer info to the form)
    DeleteCustomer

    So you might have a function like this:

    Function GetCustomerInfo(CustomerID as variant)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustomerID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    msgbox "No customer returned."
    rs.close
    set rs = nothing
    exit function
    else
    Forms!MyCustomerForm!CustomerID = rs!CustomerID
    Forms!MyCustomerForm!FirstName = rs!FirstName
    Forms!MyCustomerForm!LastName = rs!LastName
    blah
    blah
    blah
    rs.close
    set rs = nothing
    End if

    and then the UpdateCustomerInfo takes what is on the form and writes it to rs (ie...
    rs!FirstName = Forms!MyCustomerForm!FirstName
    ....
    ....
    ...
    Thanks, That's helpful I think. We'll find out if I really understand this over the next few days. . .

    I just have to get over the apprehension of starting this project wrong an having to start over again. It's a simple project really, but I want to bring this project to the next level if you know what I mean.

    Thanks for your help

    edit, um, for the always having autonumber, I take it you always use the autonumber as the primary key and for anything meaningful to the customers (such as work orders) have a seperate field and update the data yourself . . .?
    Last edited by jdostie; 09-26-08 at 20:31.

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    With regard to the comments on autonumbers and suchlike, I always use two keys - the autonumber for structural use (foreign keys, mostly) and a code for user eyeball use. The code always accompanies the numeric key so that when users are doing ad-hoc queries etc., they know what the numeric key is referring to. Autonumber keys ensure that no one can accidentally stuff up the architecture. There is a small downside in that care must be taken to maintain the codes properly. For instance, in a subform, when you create a record, you must be sure to set the main form code in the subform before insert. There is also a secondary upside in that if the code is damaged, it is easy to write routines that can ripple through the database and refresh the codes.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Your autonumber field does NOT have to necessarily be the primary key. The primary key is used for contstraint on not allowing duplicate values for that field/those fields which you cluster and make a primary key. My primary key is sometimes the autonumber field but most of the time, it's on the fields where I don't want duplication.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    pkstormy, you can have the best of both worlds, by making your primary key a combination of an autonumber and a list of fields that you want to ensure are collectively unique. The point is that the fields you mention are actually a control on data quality in that particular table, while the autonumber field provides both uniqueness and a compact reference to the record in the table for structural integrity in relationships. If you were to use your list of fields as a foreign key in subtables (or whatever) you run the risk of corruption because these fields in the subtable are not guaranteed to maintain their uniqueness except through programmatic intervention.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Jim Wright
    pkstormy, you can have the best of both worlds, by making your primary key a combination of an autonumber and a list of fields that you want to ensure are collectively unique. The point is that the fields you mention are actually a control on data quality in that particular table, while the autonumber field provides both uniqueness and a compact reference to the record in the table for structural integrity in relationships. If you were to use your list of fields as a foreign key in subtables (or whatever) you run the risk of corruption because these fields in the subtable are not guaranteed to maintain their uniqueness except through programmatic intervention.
    no no no
    keep it simple.....
    the primary key should be just enough to uniquely identify a record, don't complicate it. if there is no obvious natural key (effectively something standing out as unique) then either use an autonumber/autogenerated number. theres nothing wrong, if you wish in creating a composite key and making that your primary key. if the composite key references several tables or has several elements (say more than 2 or 3) then I'd favour an autonumber /autogen PRIMARY key and create a unique index for the other elements.

    the fact you are appending other elements to an autonumber key suggests that those elements are ripe for being a primary key, and in my books it comes down to performance or convenience (yours as system designer) as to whether to use an auto or composite key

    its quite common to have PK which is a composite of the FK to a 'parent' table and something else (eg an order No + itemno for details of items ordered, or order+productno.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by healdem
    no no no
    keep it simple.....
    the primary key should be just enough to uniquely identify a record, don't complicate it. if there is no obvious natural key (effectively something standing out as unique) then either use an autonumber/autogenerated number. theres nothing wrong, if you wish in creating a composite key and making that your primary key. if the composite key references several tables or has several elements (say more than 2 or 3) then I'd favour an autonumber /autogen PRIMARY key and create a unique index for the other elements.

    the fact you are appending other elements to an autonumber key suggests that those elements are ripe for being a primary key, and in my books it comes down to performance or convenience (yours as system designer) as to whether to use an auto or composite key

    its quite common to have PK which is a composite of the FK to a 'parent' table and something else (eg an order No + itemno for details of items ordered, or order+productno.
    I can see your point, but my line of thought has always been that the primary key is the one unique record identifier - whether a combination of other fields, or a unique auto-numbered field, and I usually use that field as the parent for the parent-child link for subforms . . . Some fields, while I want them to be unique (say a serial number) may be blank at the time the user fills the form out because the user does not yet know the serial number (customer calls in a problem, but does not know the serial number of the machine, we still have to respond). Yet, the machine is a unique unit, and I would identify it by a unique (typically autonumbered) field. That this leaves the challenge of ensuring that someone comes back and fills in the serial number is another challenge.

    But, I am trying to reason out why you would want both an autonumber and a seperate primary key. Let me postulate an idea, and you can tell me if this would be a good example:
    1. I have a tblmodelmaster, which lists the "valid" manufacturer names and model numbers for equipment along with some information about them.
    2. The model master is used as a source for combo box when user's add equipment to contracts, and then also used to populate some fields with "default" values that may be overridden (otherwise I could just keep the data in the model master field right?)
    3. Currently I have unique identifier (autonumber) in the model master as the primary key, but I don't use it for anything. And Manufacturer and Model (are not, but will be when I rebuild) as an index field with no duplicates allowed.

    Under your model, I would have manufacturer and model number fields combined as a primary key, and use the autonumber as the reference? In other words, when someone enteres the model number and serial number I want I would actually store the number in the table. I do something similar when the user is inputting a "single" field, say technician, I put store the technician ID, although the user enters the full name of the technician (yes firstname and lastname are stored seperately,but the user doesn't see that in these particular forms.

    Anyway, the point being, unless you use the autonumber for something, it seems like it's redundant, but if you do - you have a purpose - like reducing the size of data stored in "dependant" tables.

    Am I far off the mark here?

  9. #9
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    So far as I am concerned, the only real primary key I consider is the uatonumber field. The addition of the other fields was not to add new rules for a primary key, but rather to provide inbuilt control over what is put in those additional fields. Would it help if we renamed the non-autonumber fields as a candidate key ? If the primary key is unique and the candidate key is unique, the combination is also unique. I have used this concept a lot in enterpprise-level databases such as Informix. However, I do concede one point specific to Access and that is that Access tends to create relationships on the fly when it sees the same key name in multiple tables. Hence, if you just wish to keep the autonumber field as a foreign key, Access will not see the link and you will have to create the relationship manually.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id keep them separate...
    autonumber is a unique index (and also the primary key)
    and you can make up another index comprised of other columns and make that unique. In fact you can make as any indexes as you wish unique, the |DB engine doesn't care, nor should it. however if you have other columns will collectively are unique then its debatable whether you need the autonumber, as the autonumber columns' sole role is to guarantee a unique value.

    as said before the only reason I'd want to use a autonumber IN ADDITION to a composite key would be for performance, such as the composite key is too long or its liable to change.

    ultimately its only an opinion, and if you have used your technique over a period of time, and are happy with it then run with it. after all should it go kaboom then at leat you are dealing with a system design you known and are familiar with, rather than relying on someone else's opinion
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    OK, OK, you win ! Looking back on the only system I have which is exactly like this, I realised that it was because I converted it from an old system which had used a multiple field primary key (no autonumbers anywhere in the system) and a fairly heavy nesting of subtables. In order to write secure VBA code to do the conversion, I added autonumber fields everywhere, then moved the data across, then removed the original fields from every table except the one where they had originally formed the primary key. As you say, whatever works, but thinking of all of the tyros drinking in all this accumulated wisdom (well, from you rather than me) it is nice to stick to the rigorous design concepts inherent in relational databases. Building from scratch, one would, of course, separate all indexing which served different purposes. I always analyse and normalise right down from top to bottom, assuming one-to-many relationships everywhere (easy to implement with autonumbers), then slowly retreat, making sure that we have a simple re-entrant strategy in place against the day when the client suddenly realises that he really did need more than one telephone number !

  12. #12
    Join Date
    Oct 2008
    Posts
    1
    Quote Originally Posted by pkstormy
    I always have an autonumber field in the table regardless (it's not like having an autonumber field is doing any damage having it.) There are more issues with NOT having an autonumber and 0 issues having one.

    I believe healdem (and/or Izy) are big into unbound forms. For me, it comes down to a time thing and overall recordset count. Unbound forms are great for large recordsets but as you may have found out, it's a little more time consuming designing them.

    If you're designing unbound forms, you may want to have 3 functions:

    UpdateCustomerID (updates or adds a new record)
    GetCustomerInfo (retrieves customer info to the form)
    DeleteCustomer

    So you might have a function like this:

    Function GetCustomerInfo(CustomerID as variant)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustomerID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    msgbox "No customer returned."
    rs.close
    set rs = nothing
    exit function
    else
    Forms!MyCustomerForm!CustomerID = rs!CustomerID
    Forms!MyCustomerForm!FirstName = rs!FirstName
    Forms!MyCustomerForm!LastName = rs!LastName
    blah
    blah
    blah
    rs.close
    set rs = nothing
    End if

    and then the UpdateCustomerInfo takes what is on the form and writes it to rs (ie...
    rs!FirstName = Forms!MyCustomerForm!FirstName
    ....
    ....
    ...
    The original topic was about opening forms based on recordset objects and using unbound forms, though the thread was sidetracked by a discussion on how to form primary keys. No big deal, but I was wondering if you could talk about the original topic a bit. jdostie, the OP, asked about the the advantages between unbound forms and recordset-based forms, but your example shows that the two methods aren't exactly mutually exclusive. Could you talk a bit about why one would create a regular unbound form versus an unbound form based on recordsets?

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmm - i don't see the dichotomy.

    an unbound form needs to get data from something, and the "something" is typically a recordset (in my case a one-record recordset).

    why unbound?
    you have total control

    why not unbound?
    you must take the trouble to impose total control

    a regular unbound form versus
    not trying to be flippant, but an unbound form with no data at all is a waste of pixels. can you explain more about this regular unbound form?

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to be flippant... an unbound form without data could be dubbed a "switchboard"
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or a splash screen
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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