SQL Error Handling

[maccten2000]

Hi All

I have code which imports an excel sheet into a MS Access Database as a linked Table. I then have a Query which selects all the items in the table and inserts them into another table.

My problem with this is the user gets to select the xls sheet they wish to import. The SQL statement that moves the Linked Table records into the Access Database Table is column specific for example

Select col1,col2,col4 from Linked_table.

If the user selects an incorrect spreadsheet where these columns dont exist Access automatically prompts me for the missing columns

Instead of being prompted i would like to catch an error and send it to the user that they have selected an incorrect spread sheet.

Can anyone help me on this

Cheers

 

[SkipVought]

Hi,

Please post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maccten2000]

I must apologize, The table is transfered into the Access Database

' Link Files
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strImportTblNm, mstrXlsFile, True

' Transfer Selected Columns From Linked Table into Specified Table
strSQL = "INSERT INTO tblMCountPickIn ( SpecRecID, ProductID, EquipItemID, Code, [Product Name], Item, TOTAL)" & _
            " SELECT SpecRecID, ProductID, EquipItemID, Code, [Product Name], CStr(NZ([Item],'')) AS ItemTxt, TOTAL" & _
            " FROM " & strImportTblNm & ";"

 

[dhookom]

I expect you will need some code to loop through the fields/columns of the linked table and check for the existance of the appropriate names.

Duane
Hook'D on Access
MS Access MVP