Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Add data to a table when uncertain of the number of fields

Discussion in 'Information Technology' started by rdemyan via AccessMonster.com, Jul 28, 2006.

  1. rdemyan via AccessMonster.com

    Guest

    I am downloading data from a website to append to a table. My procedure is:

    1) Download the data to an excel spreadsheet.
    2) Import the spreadsheet to a holding table.

    I'm having now problem getting the data from the spreadsheet to a holding
    table. Now the problem I'm having is with transferring the data from the
    holding table to the main table. The problem is that some of the fields that
    are in the main table may not be present in the holding table. So if I have
    an action query that names each field and some are missing, there is a
    problem.

    The field names in the holding table are the same as the field names in the
    main table. Example:

    Main Table Fields
    Product
    Sub_Product
    Style
    Shape
    Cut
    Manufacturer
    Model

    Holding Table Fields (for a particular download, the quantity of fields might
    change with each download)
    Product
    Sub_Product
    Style
    Manufacturer
    Model

    How do I transfer the rows in the Holding Table to the Main Table when I can
    never be sure exactly which fields will be present in the Holding Table.
    Assume that the main table key fields will always be present in the holding
    table and that the fields present in the holding table is always a subset of
    the main table fields (there will never be any field names in the holding
    table that are not in the main table).

    Thank you.

    --
    Message posted via http://www.accessmonster.com
     
  2. Loading...


  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    My instant reaction is that there is something wrong with the definition of
    this process, because you're clearly going to be losing data, appending rows
    without being able to accommodate all the fields, but that's maybe for
    another post.

    You need to get handy with using the object model to get to the fields in
    tables.

    dim db as dao.database
    set db = currentdb
    dim tdfSource as dao.tabledef
    dim tdfDest as dao.tabledef
    dim fldSource as dao.fielddef
    dim fldDest as dao.fielddef
    dim strFieldList as string

    set tdfSource = db.tabledefs("Inputtablename")
    set tdfDest = db.tabledefs("DestTableName")

    For each fldSource in tdfSource.fields
    for each fldDest in tdfDest.fields
    if fldSource.Name = fldDest.Name then
    if strFieldList <> vbnullstring then
    strFieldList = strFieldList & ", "
    endif
    strFieldList = strFieldList & "[" & fldDest.Name & "]"
    exit for
    endif
    next fldDest
    next fldSource


    The square brackets guard against names with spaces or special characters in
    them. When you fall out of the outer loop, you have a list of fields in
    both tables. It may seem redundant going through the inner loop so many
    times, but once you have the objects in memory that stuff runs so fast
    you'll never notice it. Any other approach, in my opinion, might save a few
    clockticks but will require much more code to write.

    Then you have to complete your append query, using strFieldList as both the
    destination and source field lists.

    dim strSQL as string

    strSQL = "INSERT INTO tlkpuse ( " & strFieldList & " ) " _
    & " SELECT " & strFieldList _
    & " FROM DestTableName;"

    Then execute your query. Since you have a db object already, you could use:

    db.execute strSQL (check for options appropriate to your activity on this
    command)

    Using db.execute, you can check the RecordsAllowed property after the insert
    to see how many records were affected. If you know how many there should
    have been, you can react if the numbers don't match. There are tons of
    refinements, but what's in this post should get the job done.

    Or (much less desireable):

    docmd.runsql strSQL
     
  4. rdemyan via AccessMonster.com

    Guest

    Thanks, Rick.

    I have no control over the download. You're right some data may be lost, but
    that's the client's issue. If it starts to bother them, then they'll require
    the data entry people to be sure that all fields are filled in.
    Unfortunately, the download is missing the fields (columns in the spreadsheet)
    where data was not entered. It's generally not critical data.

    I'll give your code a try and report back.


    Rick Wannall wrote:
    >My instant reaction is that there is something wrong with the definition of
    >this process, because you're clearly going to be losing data, appending rows
    >without being able to accommodate all the fields, but that's maybe for
    >another post.
    >
    >You need to get handy with using the object model to get to the fields in
    >tables.
    >
    >dim db as dao.database
    >set db = currentdb
    >dim tdfSource as dao.tabledef
    >dim tdfDest as dao.tabledef
    >dim fldSource as dao.fielddef
    >dim fldDest as dao.fielddef
    >dim strFieldList as string
    >
    >set tdfSource = db.tabledefs("Inputtablename")
    >set tdfDest = db.tabledefs("DestTableName")
    >
    >For each fldSource in tdfSource.fields
    > for each fldDest in tdfDest.fields
    > if fldSource.Name = fldDest.Name then
    > if strFieldList <> vbnullstring then
    > strFieldList = strFieldList & ", "
    > endif
    > strFieldList = strFieldList & "[" & fldDest.Name & "]"
    > exit for
    > endif
    > next fldDest
    >next fldSource
    >
    >The square brackets guard against names with spaces or special characters in
    >them. When you fall out of the outer loop, you have a list of fields in
    >both tables. It may seem redundant going through the inner loop so many
    >times, but once you have the objects in memory that stuff runs so fast
    >you'll never notice it. Any other approach, in my opinion, might save a few
    >clockticks but will require much more code to write.
    >
    >Then you have to complete your append query, using strFieldList as both the
    >destination and source field lists.
    >
    >dim strSQL as string
    >
    >strSQL = "INSERT INTO tlkpuse ( " & strFieldList & " ) " _
    > & " SELECT " & strFieldList _
    > & " FROM DestTableName;"
    >
    >Then execute your query. Since you have a db object already, you could use:
    >
    >db.execute strSQL (check for options appropriate to your activity on this
    >command)
    >
    >Using db.execute, you can check the RecordsAllowed property after the insert
    >to see how many records were affected. If you know how many there should
    >have been, you can react if the numbers don't match. There are tons of
    >refinements, but what's in this post should get the job done.
    >
    >Or (much less desireable):
    >
    >docmd.runsql strSQL


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  5. rdemyan via AccessMonster.com

    Guest

    Tried the code. Had to make a few changes for a situation which I didn't
    mention in the post. Also in the variable definitions, I couldn't find a
    property called DAO.FieldDef so I replaced it with DAO.Field.

    Seems to work just fine, but it needs more testing.

    Thanks.

    rdemyan wrote:
    >Thanks, Rick.
    >
    >I have no control over the download. You're right some data may be lost, but
    >that's the client's issue. If it starts to bother them, then they'll require
    >the data entry people to be sure that all fields are filled in.
    >Unfortunately, the download is missing the fields (columns in the spreadsheet)
    >where data was not entered. It's generally not critical data.
    >
    >I'll give your code a try and report back.
    >
    >>My instant reaction is that there is something wrong with the definition of
    >>this process, because you're clearly going to be losing data, appending rows

    >[quoted text clipped - 56 lines]
    >>
    >>docmd.runsql strSQL


    --
    Message posted via http://www.accessmonster.com
     
  6. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Yes, dao.field. Sorry about that. There is no DAO.FieldDef
     

Share This Page