Data Append from Excel with condition

[htadis]

good day ! Forum,

I am trying to append some data from an excel sheet. Following code used for same and successful. However, i need to put few condition while importing the data to the Access table. i.e;

Table : tblMain
Filed : Vessel, Voyage, contianerNo, ......, ......, .....,

while importing, need to check whrt "containerNo" which is importing already in the table under the particular voyage. If yes error msg should pop up warning that the particular container is already exists. If same container number is already exists in the same table under a different voyage, data should be imported without any error.

Kindly request a possible amendment to below code to meet above requirement.

Code

Private Sub Command0_Click()
Dim Filepath As String
Filepath = "D:\Xxxx\tbl_Impts_main.xlsx"
If FileExist(Filepath) Then
DoCmd.TransferSpreadsheet acImport, , "MainImportsfromxls", Filepath, True
If IsNull(DLookup("[ContainerNo]", "newdata")) Then
MsgBox " No neW data to add"
Else
DoCmd.OpenQuery "data_ApendtoMain", acViewNormal
MsgBox " New data Imported"
End If
Else
MsgBox "Data Source file is not available. Please check the file name or location.", vbInformation, " Data source file not available"
End If
DoCmd.DeleteObject acTable, "MainImportsfromxls"
End Sub

Code Source : https://www.youtube.com/watch?v=vQZRadmm7Ho

 

[GinaWhipp]

The best way to do this is to create a Temp table import the data there first then check it against the live table and then you add and/or update what you want.

 

[htadis]

Hi Gina,

Many thanks for your reply.

yes, i tried that as well. But, unfortunately what i don't know is how to check against each other in accordance to the conditions which i required. Could you pls help me on that.

brgds
Htadis

 

[GinaWhipp]

You will need to write queries, i.e.

The query will have two tables the Temp table and the Live table which you can match on Container No showing all from the Temp table. If the Container No is not in the Live table you can use the query to write an append query to send those missing records.

Sounds a little confusing I know, I wrote it and it sounded confusing. So, I see the name of the Temp table but I don't see the name of the Live table. Post that and I'll post the SQL of the query.

 

[htadis]

Gina,

thanks for your last. my live table is 'tbl_import_Main". By using above code, i imports data from excel file to "tblMain" which is a temp table and then through an append query transfer data to the live table. So, in the process of data transferring ( or any other suitable or convenient occasion ) to live table i need to check aforesaid condition. do not know i'm clear enough you to understand the requirement.

Appreciated your kind support. thanks !

 

[GinaWhipp]

Your first query will find all the *new* records not in tbl_import_Main...

SELECT tbl_import_Main.[Container No], tblMain.[Container No]
FROM tbl_import_Main LEFT JOIN tblMain ON tbl_import_Main.[Container No] = tblMain.[Container No]
WHERE (((tbl_import_Main.[Container No]) Is Null));

Your second query will use your first query to APPEND all those new records to your live table tbl_import_Main.

 

[htadis]

thanks Gina. I'll chk and revert the outcome.