[AccessD] How to import row number from Excel Sheet ???

I'm not sure what you're after. You're importing all these Excel records
into an existing table. Why do you want to add the AutoNumber
programmatically if the table already exists?

Susan H. 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bhupinder Singh
Sent: Thursday, March 01, 2007 8:33 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] How to import row number from Excel Sheet ???


hi ,
  I am working with MS Access 2000 and Excel. I need to keep track of row
number of the record that i have imported from excel using 
"DoCmd.TransferSpreadsheet"  (VBA)
  As it does not import the rows number.
  it imports all the records randomly.
   
  Apart from this could you pls tell me how can i add a autonumber column to
an existing table programiticaly or by a query so that i can call it in my
program.
   
  as MS Access does not support "identity".
   
  Purpose: I am importing more then 300 sheets from different excel files. 
  After improting them to different tables i collate all the data in a
singel table. In the collated table I want to know the source row. As i can
keep trak of the excel file.
  from where they are imported .BUT not able to keep track of each record
within the sheet.
   
  Following is the code snippt.
   
  acImport, acSpreadsheetTypeExcel9, strTablename, sXlsFileName, True,
StrSheetName & "!"
   
      '//Create ID Column
    sAppendRegionCol = "Alter table [" & strTablename & "] add column
[RowID] number"
    CurrentDb.Execute sAppendRegionCol
   
  Dim rst As DAO.Recordset
Dim iRowID As Integer
'Dim iCount As Integer
Dim sRowIDUpdate As String
  
iRowID = 1
  sRowIDUpdate = "Select * from [" & strTablename& "]"
Set rst = CurrentDb.OpenRecordset(sRowIDUpdate)
rst.MoveFirst
Do While (Not rst.EOF)
    rst.Edit
    rst.Fields(argRowID).Value = iRowID
    rst.Update
    iRowID = iRowID + 1
    rst.MoveNext
Loop
   
  I tried to give the serial number to newly generated table thru the above
code. 
  But the records in the newly generated table are not in the same order as
they are in the sheet.
   
  kindly let me how to tackle with the above problem.
   
   
   
   
  Thanx in advance.
  
Bhupinder Singh


 				
---------------------------------
 Here's a new way to find what you're looking for - Yahoo! Answers 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.4/703 - Release Date: 2/26/2007
2:56 PM