Ado add an id variable to a table

[metricsinstitute]

Hello,

I'm creating a recordset with the following (partial) code:


rs1 = MainTableRangeSheet & "$" & MainTableRangeNoDollar & "]"
Sql = "SELECT * FROM " & rs1 & " AS T1
objRecordset.Open Sql, ObjConnection, adOpenForwardOnly

The code works fine...

I'm interested at adding a unique ID variable to each record (1,2,3...)

Could you help?

Thanks
Avi

 

[GS]

Hello,

I'm creating a recordset with the following (partial) code:


rs1 = MainTableRangeSheet & "$" & MainTableRangeNoDollar & "]"
Sql = "SELECT * FROM " & rs1 & " AS T1
objRecordset.Open Sql, ObjConnection, adOpenForwardOnly

The code works fine...

I'm interested at adding a unique ID variable to each record
(1,2,3...)

Could you help?

Thanks
Avi

Assuming row1 contains fieldnames, and records start in row2, and colA
is empty...

In A2 type =Row()-1 and copy down to last record. Then do a
Copy/PasteValues so your unique IDs don't get screwed up when you
add/remove records. Of course, you can always [re]set this at any time
by assigning a formula with VBA...

With Range("A2:A" & lLastRow)
.Formula = "=Row()-1":.Value = .Value
End With

...where you've already determined the row number of the last record in
the tabe.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion