[MS ACCESS] Import from excel to append table with a primary key linked to another table RRS feed

  • Question

  • I would like to programatically import and append a table with a button click.  Automation aside I am currently unable to do so even with the import wizard, as  I am attempting to append a table which has a primary key that links to another table.  I would like to drop the primary key constraint and delete the relationship in vba then recreate after the import is finished. I found the MsysRelationships table but not sure where to look for table constraint names.  

    ccolumn grbit icolumn szColumn szObject szReferencedColumn szReferencedObject szRelationship

    Private Sub cmdImport_click()

        'Home page has a combo box using the primary key
         DoCmd.Close acForm, "HomePage"

    Dim dbs As DAO.Database
    Dim strTbl  As String
    Dim strPath As String
    Dim strSQl  As String

    Set dbs = CurrentDb
    strTbl = "deleteME"
    'strPath = 
    strPath = CurrentProject.Path & "\documents\" & strPath

        'Drop "temp table"
        If IsObject(strTbl) Then
            dbs.Execute "DROP TABLE " & strTbl & ";"
        End If

        'import XL into temp table
        DoCmd.TransferSpreadsheet acImport, , strTbl, strPath, True

        'Drop Constraint and Relationship
        'SOME CODE

        'truncate table
        dbs.Execute "DELETE * FROM destination_table"

        'insert new records
        dbs.Execute "INSERT INTO destination_table " & _
                    "SELECT * FROM " & strTbl & ";"

        'Add Back Constraint and Relationship
        'MO CODE

        'Drop "temp table"
        dbs.Execute "DROP TABLE " & strTbl & ";"


       DoCmd.OpenForm "HomePage"

    End Sub

    Thanks in advance

    • Edited by ChaZui Tuesday, May 9, 2017 5:12 PM forgot to specify software
    Tuesday, May 9, 2017 5:05 PM

All replies

  • You don't want to modify system tables. Just use SQL DDL:

    ALTER TABLE Orders DROP CONSTRAINT OrdersRelationship (Foreign Key Constraint)

    DROP INDEX PrimaryKey ON TableName (Primary Key Constraint)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 9, 2017 5:26 PM
  • ciao ChaZui,

    are you sure you cannot just link Excel file instead than import into temporary table, delete the PK, drop the relationship and restore it?

    in case not, what about to execute a DML query directly from Access ?

    SELECT [nomeFoglioXls$].nomeCampoXls, [nomeFoglioXls$].nomeCampoXls2

    INTO yourTable
    FROM [Excel 12.0;DATABASE=c:\fullPathFileExcel.xlsx].[nomeFoglioXls$];


    INSERT INTO yourTable
    SELECT [XlsFile$].fieldXLSname AS fieldName, [XlsFile$].fieldXLSname AS NomeSocietà
    FROM [Excel 12.0;DATABASE=C:\fullPath\XlsFile.xlsx].[xlsSheet$];

    Sql predicate can be applied in case xls file has for each column the relevant heading :

    IDCliente NomeSocietà Contatto
    AAAAA ss  
    ALFKI Alfreds Futterkiste Maria Anders
    ANATR Ana Trujillo Emparedados y helados Ana Trujillo
    ANTON Antonio Moreno Taquería Antonio Moreno
    AROUT Around the Horn Thomas Hardy
    ASCDA scd scd
    ASDFA asdfasdf asdf


    Ciao, Sandro.

    Tuesday, May 9, 2017 5:50 PM