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

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.  

<tfoot></tfoot>

MSysRelationships

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 & ";"

    dbs.Close

   DoCmd.OpenForm "HomePage"

End Sub

Thanks in advance

                              

Answer 1

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)

Answer 2

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$];

or

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

HTH.

Ciao, Sandro.