Results 1 to 2 of 2
  1. #1
    chris.williams is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    94

    Importation of Cascading Records

    Hello all, I've got a requirement to import specific records from a series of cascading tables from one database to another.



    certTable has a unique column (ticketNum) containing ticket numbers, with a PK of ID
    deviceTable has is related to certTable via a 1:M relationship based on the ticketNum column of both tables, with a PK of ID
    deviceStorageTable is related to deviceTable via a 1:M relationship based on the deviceTable.ID column

    There are TWO databases (or sets of linked tables in a single Access file). One source and one target.

    The source tables are called stg_certTable, stg_deviceTable and stg_deviceStorageTable. All three sets of tables have different auto increment values on the ID fields of each table.

    Importing records for which the record contains the ticketNum is easy, and working fine. However, I need to import all associated records from stage (source) to the target structure - including records in the deviceStorageTable which does not have the ticketNum field and even if it did, the table is related via the ID field which is different on each set of tables.

    Question: How would I go about importing records for the deviceStorageTable while changing the deviceID value for the corresponding record to the NEW ID the deviceTable?

    example:

    stg_certTable.TicketNum = "Ticket123"
    stg_deviceTable.TicketNum = "Ticket123", stg_deviceTable.ID = 1
    stg_deviceStorageTable.deviceID = 1

    Because the ID fields are set when the records are created via the auto increment function, we get results such as:

    certTable.TicketNum = "Ticket123"
    deviceTable.TicketNum = "Ticket123", stg_deviceTable.ID = 37
    deviceStorageTable.deviceID = 1

    and the record in deviceStorageTable is no longer associated with the proper record in deviceTable.

    I need to be able to run through something like a foreach loop at the time for each deviceTable record and again for each deviceStorageRecord, but do not know the best way to do that.

    Logic example:

    Code:
    INSERT INTO certTable from stg_CertTable where TicketNum = "Ticket123"
    FOREACH RECORD IN stg_deviceTable where TicketNum = "Ticket123"
    INSERT INTO deviceTable from stg_deviceTable where TicketNum="Ticket123" var_newDeviceID = deviceTable.ID var_oldDeviceID = stg_deviceTable.ID FOREACH RECORD IN stg_deviceStorageTable WHERE stg_deviceStorageTable = var_oldDeviceID
    INSERT INTO deviceStorageTable from stg_deviceStorageTable (replacing deviceID with var_newDeviceID)
    Or something like that.

    Any help would be greatly appreciated - thanks...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I had to develop a database that allows distribution of blank db to remote locations and at end of season the files are sent to central office and data imported to master file. Autonumber pk fields were such an issue I eventually eliminated them. The impression I got from my struggle is that what you want won't be easy. Might need either temp table or columns in deviceTable and deviceStorageTable for the 'old' IDs from the source. Link tables on the 'old' ID, run update on the 'new' fk in deviceStorageTable, delete the 'old' values.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 02-01-2013, 02:25 PM
  2. Replies: 9
    Last Post: 08-07-2012, 11:50 AM
  3. Replies: 1
    Last Post: 11-02-2010, 03:35 PM
  4. how to xml auto importation
    By IMADOV10 in forum Access
    Replies: 0
    Last Post: 03-27-2010, 03:33 PM
  5. Cascading ?
    By dlburkins in forum Forms
    Replies: 5
    Last Post: 09-27-2009, 04:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums