Number records in table from transferspreadsheet

[Guest]

Hello ol wise ones,
I am importing an excel spreadsheet to my Access 2002 table. I have set the
Excel NAME range to all rows in columns a thru g, named "Info" I have the
vba code in Access to that works fine.
'by sight
docmd.transferspreadsheet acimport, , "temp_Excel_Info", strInputFileName,
True, "Info"

I would like to use a field named "ROW" in temp_Excel_Info table that would
store the corresponding ROW number of the excel spreadsheet so if there is a
validation error, I can use the ROW number in a message box to the user. I
have thought about using the ID (autonumber) field plus 1 since row 1 of the
excel spreadsheet is column names, but that goes to over 65,000 when
importing. I have a query to delete the blank rows in the table, but when
user imports another spreadsheet, the ID starts at 65,000.
I could compact and repair before import to reset the autoid, but not sure
that is practical with Runtime users.

Can I run an update query to populate the ROW field in table tmp_Excel_Info
that would be the record number in the record set?

if so, what would this query be?

If not, is compact and repair before import the best method?

Any assistance is greatly appreciated.

 

[Guest]

I have found a method to run an append query to temp2 to store the ID from
the temp_Excel_Info table and it's rank. I then use an update query to
update the field rank from temp2 to temp_Excel_Info table joined on ID from
temp_Excel_Info and the ID added from the append query named ID2 in the temp2
table.

using techniques explained at this link.

http://support.microsoft.com/kb/q120608/


It works. If anyone has other suggestions, I would appreciate the input.