trying to load an array -- trickey!

[bboht]

I am trying to load an array with all the field names in a recordset. I am having a little trouble figuring out how to pull the field names from the table (if this is even possible). Here is my prob. rstTemp is the recordset.

Dim intCount As Integer
intCount = 0
rstTemp.Recordset.MoveFirst
Do While intCount < rstTemp.Recordset.numOfRecords
arrtable(intCount) = rsttemp.Recordset.fieldName
rstTemp.Recordset.MoveNext
intCount = intCount + 1
Wend

I don't know of an "easy" way to get numOfRecords and I have no idea how to get fieldName.

[kovan]

use the sql COUNT to find out how many records there are..
about the fieldName
havent needed to do that so i woudn't know
sorry

[Joacim Andersson]

Is rstTemp a data control or is it a RecordSet object?
If it is a RecordSet do the following

Dim iCount As Integer
Dim i As Integer
Dim arrtable() As String

iCount = rstTemp.Fields.Count - 1
ReDim arrtable(0 To iCount)
For i = 0 To iCount
    arrtable(i) = rstTemp.Fields(i).Name
Next

If it is a data control just add .Recordset after the name

Dim iCount As Integer
Dim i As Integer
Dim arrtable() As String

iCount = rstTemp.Recordset.Fields.Count - 1
ReDim arrtable(0 To iCount)
For i = 0 To iCount
    arrtable(i) = rstTemp.Recordset.Fields(i).Name
Next

Good luck!

[HeSaidJoe]

you can try this out...I didn't test it.

Dim intMyCounter As Integer, intCount As Integer
Dim arttable()

'assuming rstTemp is your datacontrol
rsttemp.Recordset.MoveLast
rsttemp.Recordset.MoveFirst

intMyCounter = rsttemp.recordcount

ReDim arttable(intMyCounter - 1)

Do While intCount < (intMyCounter - 1)
arrtable(intCount) = rsttemp.Recordset.fieldName
rsttemp.Recordset.MoveNext
intCount = intCount + 1

Wend

[HeSaidJoe]

Joacim Andersson
Sorry for dup..I didn't see yours when I posted.

[Paul282]

Just use

Do while not rs.EOF

'... code

Wend

The count method you use is okay, I usually have an array of UDT for the record set if I need it in memory because you can name the fields and the program reads better.

Don't listen to crap about For-Next is faster than While because it's not. The DB is the slow link not VB and if you use

Move last
Get RecordCount
Move first
For-Next Loop

Then the DB has to have finished calculating the RS in order to move last, where as 'while' will start sending you records as the database finds them. As you may know, a large query on a busy DB can give very mixed response times so it's best to grab the records while they are available.

I've never had the luxury of using count as the DBs are so busy that the overhead of waiting for the extra sql command is too slow.

But, If you're reading data from your own MDB file it'll hardly make a difference either way, just make your code neat and readable because it's easy to get lost.

Cheers

[Paul282]

Gee, lots of new thread arrived while I was writing

[MartinLiss]

There are two things you can do about the number of records: 1) Do a MoveLast prior to your MoveFirst. You can then store the rstTemp.RecordCount value, or 2) change the Do loop to Do While Not rstTemp.EOF.

[Joacim Andersson]

Hmmm... I must have misunderstood something. Isn't it the Field names he was asking for? Who cares how many records the recordset contains then?

[HeSaidJoe]

'this one I tested and you get the count as you load

[code]
'you will have to change the data1 to reflect your datacontrol

Dim intCount As Integer
Dim arttable()

Data1.Recordset.movefirst

While Not Data1.Recordset.EOF

ReDim Preserve arttable(intCount)

arttable(intCount) = Data1.Recordset.sName
List1.AddItem arttable(intCount)
intCount = intCount + 1

Data1.Recordset.MoveNext

Wend

[Frans C]

I think two things are getting mixed up.
If your interested in the field names, you don't need to know the recordcount. It's the number of fields you want to know, and not the number of records. Joacim Andersson is the only one who got this right.
If you want to load the records in an array, just loop until eof. Don't waste time to get the recordcount first; if you try the movelast method on a table with 25 million records, you can drink a coffee or two (or more) before you get the recordcount.

[bboht]

ya it was the field names. The code Joacim Andersson posted worked like a charm.

[HeSaidJoe]

looks like I misread...
Joacim is on the mark

and if were records then the post 2 above this, by myself), does the job quite well.

Wayne