Import/Update table from Excel ?

[pookie62]

Hi all,
I use an Excel import to update tabel 'deelnemer'. This works oke BUT when user 'x' has a linked field in tabel 'B' the import fails due to RI (I think..)
How can I make it so in this code that the user record is updated and can I restore the RI (otherwise my forms won't work..)
This is the code I use for importing the Excel file:

DoCmd.CopyObject , "Deelnemer_copy", acTable, "Deelnemer"
 DoCmd.SetWarnings False
 DoCmd.RunSQL "Delete * from deelnemer"
 DoCmd.SetWarnings True
   
 ImportFile = Application.CurrentProject.Path & "\Deelnemer.xls"
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Deelnemer", ImportFile, True

Please help me on this, breaking my head over it and I'm newbie on all this..
Thanks in advance !

 

[RV]

By running a DELETE statement yes, you're screwing up RI.
You should use an INSERT statement which only insert records that does not exist yet in your table deelnemer.

FYI, there's a very good Dutch Access forum:

http://office.webforums.nl/forum/index.php

RV

 

[pookie62]

Thanks for you reply RV
You don't happen to have a sample of how to accomplish the things I want ?
Would be very helpfull...

 

[Superock]

Newbie Clarification

Hi all,
I use an Excel import to update tabel 'deelnemer'. This works oke BUT when user 'x' has a linked field in tabel 'B' the import fails due to RI (I think..)
How can I make it so in this code that the user record is updated and can I restore the RI (otherwise my forms won't work..)
This is the code I use for importing the Excel file:

DoCmd.CopyObject , "Deelnemer_copy", acTable, "Deelnemer"
 DoCmd.SetWarnings False
 DoCmd.RunSQL "Delete * from deelnemer"
 DoCmd.SetWarnings True
   
 ImportFile = Application.CurrentProject.Path & "\Deelnemer.xls"
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Deelnemer", ImportFile, True

Please help me on this, breaking my head over it and I'm newbie on all this..
Thanks in advance !

I am just learning VBA an am specifically looking at importing & updating tables from excel spreadsheets.

I just want to make sure I understand the code above, can someone tell me if I am getting it right?

It seems that the person is first copying data from "Deelnemer" to "Deelnemer_copy".
Then deleting * records from "Deelnemer"
Then populating "Deelnemer" table with "Deelnemer.xls" with new data.

Is this right?