none
How to copy records from one table to another if one table has attachments RRS feed

  • Question

  • How do you copy records from one table to another if one table has attachments.  Update queries do not work. I have an indexed table of catalogued books and want to update a Rare Book table which contains multiple images per selected indexed book. Once I've identified the 'Book' in the Catalogued Book table, I want to add this record to the Rare Book table to which I will add the Images.  I can do this manually (by record). How do you do this via ADO?
    Saturday, March 10, 2018 1:35 AM

All replies

  • Hi,

    See if this demo helps with figuring out how to do it. 

    Let us know how it goes. 

    Saturday, March 10, 2018 2:11 AM
  • There is no need to copy non-key data from one table to another, nor should you do so as it would introduce redundancy  and the consequent risk of update anomalies.

    You do not say whether the rare books table can include books which are not represented in the catalogued books table, though I would assume this is not the case, which would mean that, in relational database terms you have a type (catalogued books) and a sub-type (rare books).  A sub-type is characterized by sharing all attributes of its (super) type, but not those of other sub-types, and is represented by a one-to-one relationship type in which the (super) type is modelled by the referenced table.

    The primary key of the sub-type table is consequently also a foreign key referencing the primary key of the (super) type table.  It follows from this that, while the primary key of the referenced  table can be, though not necessarily is, an autonumber, that of the referencing table cannot be.

    To insert a single row into the referencing table you would simply use a bound data entry form with a combo box bound to the primary key, whose RowSource is a list of all catalogued books, along with controls to insert the images and any other attributes represented by columns in the table.  The BoundColumn of the combo box would be the primary key column, which can either be visible or hidden, while other columns in the dropdown list would enable the book to be identified.  Unbound controls in the form would reference the combo box's Column property to show those values once an item has been selected.

    To insert multiple rows simultaneously an unbound form with a multi-select list box could be used, with code in a button's Click event procedure to insert rows into the rare books table, e.g.

        Dim varItem As Variant
        Dim strSQL As String
        Dim strBookIdList As String
        Dim ctrl As Control

        Set ctrl = Me.lstCataloguedBooks

        For Each varItem In ctrl.ItemsSelected
            strBookIdList  = strBookIDList & "," & ctrl.ItemData(varItem)

            strSQL = "INSERT INTO RareBooks(BookID) " & _
                "VALUES(" & ctrl.ItemData(varItem) & ")
            CurrentDb.Execute strSQL, dbFailOnError
        Next varItem

        If Len(strBookIdList) > 0 Then
            strBookIdList =   "BookID In (" & Mid(strBookIdList,2)  ")"
            DoCmd.OpenForm "frmRareBooks", WhereCondition:=strBookIdList
        End If

    This code would insert one row for each of the selected items into the RareBooks table, with a value entered into the primary key BookID column only.  Note that only this column should be 'required' in the table design.  I've assumed BookID is a number data type, and consequently does not need the value to be delimited with literal quotes characters.  The code would then open a form, frmRareBooks (the same as used for inserting a single row), filtered to those rows just entered, ready for you to enter the images and any other data.

    For reporting purposes you'd base a report on a query which joins the tables on BookID, and returns whatever data from each table you wish to be included in the report.

    Ken Sheridan, Stafford, England

    Saturday, March 10, 2018 2:34 PM
  • I don't follow. Isn't this as simple as an IsRareBook Yes/No field in the one and only Books table?

    -Tom. Microsoft Access MVP

    Saturday, March 10, 2018 3:03 PM