Validate input table uploaded (1 Viewer)

[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.

 

[Pat Hartman]

The approach you take depends on whether or not you want to update the spreadsheet as well. If you are not going to import the spreadsheet if there is missing/invalid data, then link to the spreadsheet and look for problems with a query. If the query returns records with problems, display them on a form so the user can have a reference to go and fix up the spreadsheet.

If you are going to allow the user to fix the errors, then I would import the spreadsheet to a temp table (more about this later). Then run the validation query against the temp table and allow the user to enter missing/invalid values. When they are done, they can run the import process again and if no errors are found, the last step of the process is to add the spreadsheet rows to the Access table.

When you have a process that requires temp tables, you want to avoid database bloat caused by importing/deleting or Make Tables. To do that, I create a template database that has empty copies of the temp tables. At the beginning of the import process, my code copies the template from the server directory to a local directory where it overlays the previous version of the file. The main application remains linked to the template database so none of your queries break and you don't have to do anything to refresh them. Access just picks up the values from the replacement database.