Import/Update table from Excel ? (1 Viewer)

pookie62

Registered User.
Local time
Today, 10:17
Joined
Jan 16, 2005
Messages
47
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:
Code:
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

Registered User.
Local time
Today, 09:17
Joined
Feb 8, 2002
Messages
1,115
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

Registered User.
Local time
Today, 10:17
Joined
Jan 16, 2005
Messages
47
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

Registered User.
Local time
Today, 05:17
Joined
Nov 3, 2004
Messages
40
Newbie Clarification

pookie62 said:
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:
Code:
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?
 

Users who are viewing this thread

Top Bottom