Incorrect data import

[MI man]

Hi,

I have an Access form through which am importing data from Excel into the Access table (Docmd.transferspreadsheet, acimport, acSpreadsheetTypeExcel8, "TableName", "SourceExcelSheetPath", True).

This above code has been assigned to a command button and is working fine, but by default, the import is taking a 61 record slot,i.e., say, even if there are only 10 records in excel, the record count in the table is getting to 61 (post-import)...unable to understand why so many empty records are being imported into Access table.

How to import the records and eliminate the empty records at one go (with the single command button)..??

Help required.

Thank you.

 

[DCrake]

Thi sis due to blank lines at the bottom of your Excel spreadsheet. Even though they may now be empty once upon a time they were not and Excel remembers this.
Delete all blank lines in Excel before importing.

 

[gemma-the-husky]

alternatively - examine the table after the import, and find some field you can safely use to delete unrequired records

so you get

docmd.transferspreadsheet ... etc
currentdb.execute "delete * from tablename where somefield is null"

and you can also use use this idea to add any other validation tests you need on the imported table. Its easier than trying to tidy up the xl sheet