Error Trapping for Data Validation

[khwaja]

I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:

Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).

Or there are column name spellings and in such cases, I get no error and the simply code hangs.

Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.


'Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb

On Error GoTo Macro1_Err

DoCmd.SetWarnings False

' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE FROM NSW1;"
DoCmd.RunSQL "DELETE FROM [QLD1];"
DoCmd.RunSQL "DELETE FROM [SA1];"
DoCmd.RunSQL "DELETE FROM [VIC1];"
DoCmd.RunSQL "DELETE FROM [WA1];"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "NSW1", "\\Nnorsogfas031\NSP\SOH\SOH Reports\NSW SOH.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "QLD1", "\\Nnorsogfas031\NSP\SOH\SOH Reports\QLD SOH.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "SA1", "\\Nnorsogfas031\NSP\SOH\SOH Reports\SA SOH.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "VIC1", "\\Nnorsogfas031\NSP\SOH\SOH Reports\vic SOH.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "WA1", "\\Nnorsogfas031\NSP\SOH\SOH Reports\WA SOH.xls", True


DoCmd.SetWarnings True
Set dbs = Nothing

MsgBox ("All Data updated")

Call DeleteImportErrorTables

Macro1_Exit:
Exit Sub

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Sub

 

[gemma-the-husky]

why not lock the access template so they cant change row1 headings?

 

[khwaja]

Thanks. This is a good idea and I should be able to use this if it has no impact. But I can't stop users stuffing up the rows or even adding a new column.

 

[gemma-the-husky]

well the other way is this

import the spreadsheet

check that the columns you expect to be imported are there
(fields collection of the tabledef object)

check that the data validates as reasonable for all columns

reject the import if it fails

however, if users can't complete a spreadsheet correctly, then it can be awkward.

the "extra " columns - F16 and the like - can come about because users insert columns and then delete them, which can affect the "true size" of an "all the data sheet" import.

 

[khwaja]

Many thanks for your input. As this import routine is already working, could you guide me on additional code I need to insert so that import is cancelled without having to handle errors.

 

[gemma-the-husky]

import the table

then this sort of process (just a skeleton code)

 for each fld in db.tabledefs("newtable").fields
    if fld.name is not expected then reject input
 next

 
rejectinput segment
msgbox "sorry. the table format is incorrect"
currentdb.tabledefs.delete "newtable" 'delete the import

 

[CJ_London]

can you not provide the users with an excel template.

You can lock the header row so they can't add new headers or change the existing ones. Or lock the headers for the columns you need so if they want to add additional columns, they can do, but you're not interested in them anyway, so it doesn't matter

 

[twcaddell]

Another option is to pre-process the Excel File, place into the proper format, save, close and then perform your transfer