Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: archive record from tbl to tbl using tbldef/fields

    i'm struggling to use tabledef & fields in a copy from one recordset to another. i want to use field names rather than fields(index) since the structure of the two tables/recordsets is not identical. right now i use explicitly typed field names but i don't like doing that.

    i have:

    -- two tables tblACTIVE and tblARCHIVE with similar structures except tblARCHIVE has some additional fields (dateArchived, strArchivedBy etc etc)

    -- a recordset rstToArchive containing some records from tblACTIVE which need to be archived plus a second recordset rstArchived on tblARCHIVE.

    what i would like to do is:

    while not rstToArchive.EOF
    rstArchived.addnew
    rstArchived!dateArchived = now()
    'etc for other fields unique to the archive
    for each field in tblACTIVE
    rstArchived!howToGetTheFieldName = rstToArchive!howToGetTheFieldName
    next field

    rstArchived.update
    rstToArchive.movenext
    end while

    ...this ought to be simple.

    thanks for any hints (ADO or DAO). izy
    currently using SS 2008R2

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    ever thought about using an Appendquery ?

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    I think emiel has the quickest solution for you and that is a quick and simple append query - it can also be used in conjunction with a duplication check and a deletion is you need to ensure the active table is kept neat and tidy - dont forget to set the DB so that at the end of use it will run a compact - keeps it all tight



    good luck

    gareth

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    an APPEND is do-able, but still requires typing all the field names literally in the SQL since the table structures are not the same.

    a way to generate APPEND sql from tblACTIVE tabledef would be fine, but is more or less equivalent to my first question.

    there are 80 fields in tblACTIVE, 86 in tblARCHIVE and the table structures are still subject to change since the system is still in a very early development stage.

    typing 80 field names and checking them each time the tables are revised... pretty boring. the archive function is central to the application so i don't want to leave it until the table structures are set in concrete.

    as i said earlier: it currently works fine with
    rstArchived!aField = rstToArchive!aField
    explicitly typing each field name - it's just boring to maintain the manually entered field names as the table changes.

    even if you don't think i should use it, there must be a way to get the field names from the tblACTIVE tabledef and use them in a
    For Each ... Next
    construction.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi izy

    your talking about using SQL for the support of the APPEND

    why not just use the humble standard query wizard to make it quicky - yI appeaciate the fact that the DB is still a baby but if there is to be that much of a change you may need to look at producing releases by platform that way you are not always updating - there are little utilities around that work with ACCESS and will make this simple

    i have to append some 200 different fields sometimes - ive set up and used a append and its now automated via a small macro been there since 2000 and still hasnt fallen over - ive made some field changes but uts a drag and drop job

    god luck with it

    gareth

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh, i should also mention that this is a strange "archive" process. the current status of the record is archived but the record itself stays in the active table with around half it's fields reset.

    i was hoping the answer to the first question would let me work out the solution to the "reset" issue: if the field type is date or single, reset to null; leave the rest of the fields alone.
    and all without manually typing any field names.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks for your replies, gareth.

    you are right of course, but there should be a way to use tabledef and then it becomes zero-maintenence: not even drag-and-drop.

    i've got examples of .fields(index) that do the job i want to do, but with the different table structures active/archive i want to use the name of the field for safety.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's the scheme so far:
    Code:
    dim dabs as dao.database
    dim rstToArchive as dao.recordset
    dim rstArchived as dao.recordset
    dim tdf as dao.tabledef
    dim fldCount as integer
    dim fldPoint as integer
    dim fldName as string
    
    set dabs = currentdb
    set rstToArchive = dabs.openrecordset(someSQL)
    set rstArchived = dabs.openrecordset(moreSQL)
    
    set tdf = dabs.tabledef("tblACTIVE")
    fldCount = tdf.Fields.Count
    
    do while not rstToArchive.EOF
      rstArchived.addnew
      for fieldPoint = 0 to fldCount -1
        fldName = tdf.Fields(fldPoint).Name
        'so far so good,  fldName is correctly set
        rstArchived!fldName = rstToArchive!fldName 'DEAD
    'etc

    ...so - looks like i have to use fldName to construct some dynamic SQL for an APPEND or find some alternative syntax for the DEAD line.

    hmmmmmm!

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    i have thought bit

    you should use the append query
    to append the new records

    next to that use an update query to check which records ar changed and must be updated (or, that is what i of course dont know, appended in which case you have to use an append query)

    if there is no checkfield ( for change or no change) in your base table you could add it and set this field if there has been made a change, and let the query criteria us it.

    and finish with an update query to set de checkfield to false or what ever you like

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and finally this seems to work:

    Code:
        Dim strSQL As String
        Dim strSQLtail As String
        Dim tdf As DAO.TableDef
        Dim fldName As String
        Dim fldCount As Integer
        Dim fldPoint As Integer
        Dim dabs As DAO.Database
    
        Set dabs = CurrentDb
        Set tdf = dabs.TableDefs("tblACTIVE")
        fldCount = tdf.Fields.Count
        
        strSQL = "INSERT INTO tblARCHIVE ( "
        strSQLtail = "SELECT "
        For fldPoint = 0 To fldCount - 1
            fldName = tdf.Fields(fldPoint).Name
            strSQL = strSQL & fldName & ", "
            strSQLtail = strSQLtail & "tblACTIVE." & fldName & ", "
        Next
        
        'kill trailing comma-space
        strSQL = Left$(strSQL, Len(strSQL) - 2)
        strSQLtail = Left$(strSQLtail, Len(strSQLtail) - 2)
    
        'and complete the SQL
        strSQL = strSQL & " ) " & strSQLtail
        strSQL = strSQL & " FROM tblACTIVE "
        strSQL = strSQL & "WHERE " & something suitable & "; "
    thanks for forcing me into the query - but i'm still convinced that a for each ... next should be possible somehow.

    now i have to do similar for the UPDATE with
    tdf.Fields(fldPoint).Type

    izy
    Last edited by izyrider; 03-13-04 at 13:38.
    currently using SS 2008R2

  11. #11
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    let me think a bit harder :-)

    but sometimes you have to alter you´re way of thinking to the way the other one is thinking, and that´s valid for me too :-)))

    one good advice don´t make tables with so many fields .... :-)

    i will look now at your way of thinking, and hope that i can help in due time.

    regards emiel

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi emiel,

    ...so many fields: i know!
    12 foreign keys
    6 strings
    2 singles
    17 dates.
    43 bools ...i could play bitwise with these, but it's hardly worth the effort - it's a very small db with only ~2000 records in tblACTIVE.

    ho hum. izy
    currently using SS 2008R2

  13. #13
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    fldName = tdf.Fields(fldPoint).Name
    'so far so good, fldName is correctly set
    rstArchived!fldName = rstToArchive!fldName 'DEAD

    does rstArchived have a field named "fldName" ?

    or how do i say it

    will the variable fldName in rstArchived!fldname result in the real name of the field ?

    stupid thinking of me, tell me anyway

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there is no field fldName in the table.

    fldName = tdf.Fields(fldPoint).Name
    correctly sets
    fldName = "validFieldName"

    but
    rst!fldName does not evaluate to rst!validFieldName

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblPlaces", dbOpenTable)

    Dim fld As String

    fld = "NameCity"

    Debug.Print rst![fld] ' error occurs here ic Runtime error 3265
    ' cannot find element in this collection

    also when rst!fld is used although fld represent an validfieldname the program, at runtime, doesn´t use the contenance of fld, it uses just fld as fieldname

Posting Permissions

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