Validate input table uploaded

[Jimal]

Hi friends
I have a form to upload my excel file in to a table. (selecting the file by "msoFileDialogFilePicker" & then doing "DoCmd.TransferSpreadsheet")
normally the file gets uploaded.

But I need to check if any of the cells in the uploaded file is empty (while uploading).
and if possible generate an error log

appreciate your help

regards
ji

 

[Gasman]

Either check the workbook itself, or upload to a temporary table and check that.
If fine then append to your normal table, if not show the error message. ?

 

[Jimal]

HI @Gasman
thanks for the reply.
Would you mind briefing it--- im bit rusty in the logic you meant

 

[Gasman]

Well the easiest way would be to make a structure copy of your table and save it as tmpTable.
Then you use your current process to transfer to that table.
Then you run some code to check the fields for being Null or empty, depending on what is required.
If good, then just run a simple append query from tmpTable toTable and delete records from tmpTable.

This will cause some bloat in Access. You could have a tnpTableMaster which is the structure but empty, and then copy that table to create the tmpTable, use that as above and then delete the table instead of deleting records. Not sure how copying/deleting table affects bloat though. I do know that if a lot of this was needed, it is recommeded to use another DB completely and copy that when needed. Then no bloat is created in the main DB, but that might be well over the top for what you need/are able to do?

The other way would be to use Excel automation to do pretty much the same thing but in Excel, just checking if any cells are emoty within a certain range. A little harder to code.