Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Attach/Detach Subdatasheet in code (2000-2007)

    OK Im operating in Access 2007 but the format is Access 2000

    I am looking to automate a process to Attach and Detach a subdatasheet relevent to an imported File, during a new file creation process.
    The Code I will need runs:
    Delete Relationship between Table1 and Table2
    Delete old File2
    Import new File2
    Create File1
    Create Relationship between File1.Field1 and File2.Field1 (Child)
    Open File1 with File2 as a Subdatasheet.

    Any suggestions will be greatfully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Attach/Detach Subdatasheet in code (2000-2007)

    Instead of deleting a table then importing into a new table, I'd use a delete query to delete all records from the table and an append query to import the records into it.

    Oh, and by the way, I'd avoid using subdatasheets. I always turn them off in all tables in all my databases. They have a significantly negative impact on performance.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Attach/Detach Subdatasheet in code (2000-2007)

    Unfortunately Hans I'm commited to using subdatasheets as any other method would require re-training staff as we reference and copy data from previous entries via a keyword.

    Is there anyway of doing it or is there somewhere on the web that'll give me some sort of idea of the right commands to manipulate relationships and subdatasheets. I had a quick look and didn't find anything.

    tx

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Attach/Detach Subdatasheet in code (2000-2007)

    Deleting and appending records still seems a better idea to me than to delete and create tables, relationships etc. But if you wish, you can use DAO:

    To remove or set a subdatasheet:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Table1")
    ' Remove subdatasheet
    tdf.Properties("SubDataSheetName") = "[None]"
    ' Set subdatasheet to Table2
    tdf.Properties("SubDataSheetName") = "Table2"

    A DAO database object has a Relations collection.
    See CreateRelation method and Delete method for help on how to create/delete relationships.

Posting Permissions

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