Why Cant I Populate an Array from ADO.Recordset?

[dynamictiger]

Perhaps this is obvious, however I seldom use arrays.  In fact I am struggling to recall last time I used this as my code usually doesn't need it.  However in this instance I think it is the best choice...Perhaps I am wrong?

I have an SQL Server backend.  I am running a number of SP on this and using these with effectively access acting as a friendly form.  This is working well and has the entire app snappy and responding well.  Separate topic really.

In my tables I have a a table set up for Jobs.  We are a kind of maintenance contractor with construction and consulting all mixed in.  Bit unusual compared to larger companies.  Consequently jobs can be opened at different levels including Client, Site, Equipment and so on.

As the jobs can be opened from different levels I am using a link table to hold the jobID and the relevant ID from the Client or Site or Equipment down to level 5.

I then was working on some air code and have just started to work on the long hand version only in Access at the moment, of the code.

WHat I am doing is feeding a SQL Statement with the relevant Table number and RecordID to return the SQL correctly.  An example being:

SELECT * FROM tblJobLinkTable WHERE LinkTable = 4 and LinkID = 31

This is fine and correct for what I am asking for.

I then have a module level array set to hold the JobID returned  being mlngJob and I have scoped this to an array of 30,000 as I am thinking its highly unlikely any client including all their sites and items of equipment would ever have 30,000 jobs open at any point of time.

I have then added the code

Private Sub popArr(strSQL As String)
Dim rst As New ADODB.Recordset
Dim lngJob As Long
    With rst    
        .Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic        
                        
                Do Until .EOF                
                    'Use get rows to pop array                    
                    mlngJob = .Fields("job")                
                    
                    .MoveNext                    
                Loop                
              .Close
        End With
      
End Sub

So effectively I am asking the ADO to populate the array with the returned jobID.  As The next step I need to build a SQL statement by walking the array and going something like mlngJob OR mlngJob OR mlngJob etc on the final query.

However I am getting loverly messages telling me I cant pop an array from a Recordset.  First why on earth not? 

I looked at the world wide confuser and found comments on using GetRows and I looked this up and tried it...but no.  It thinks Im ugly and my mum dresses me funny.  

I can think of about 6 other ways to do this, however I would really like to use this concept.  It would suit the direction I am generally going and I think would be the best approach.  All I want to do is hold these job numbers returned in an Array so I can go back and get the data in a few more lines of code.

Is there another approach that will work for this?  

Thanks

[#2]

@dynamictiger 

In your case as been posted you are looping through the entire recordset so you can assign one value a time to the array. Then you should use an array index:

Dim i as integer

...

Do while not rst.eof

   i = i + 1

   mlngJob[i] = .fields("job")

   rst.MoveNext

Loop

Another solution - and I assume this is the solution you are looking for -  is assigning the recordset rows directly to an array

...

Dim rst As Recordset
Dim mstrArray As Variant

Set rst = CurrentDb.OpenRecordset("yourSQLstatement")

mstrArray = rst.GetRows()

Set rst = Nothing

...