-
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.
-
use COUNT
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:(
-
Is rstTemp a data control or is it a RecordSet object?
If it is a RecordSet do the following
Code:
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
Code:
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!
-
<?>
you can try this out...I didn't test it.
Code:
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
-
<?>
Joacim Andersson
Sorry for dup..I didn't see yours when I posted.
-
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
-
Gee, lots of new thread arrived while I was writing
-
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.
-
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?
-
<?>
'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
-
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.
-
ya it was the field names. The code Joacim Andersson posted worked like a charm.
-
<?>
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