Retrieving Data From a DAO Recordset

[Executable]

Hi everyone,

I have a question. I am creating a dynamic report from a table and need to fetch all the data (values and column names) into an array so that I can manipulate the data. I know there is a way to get the values in the recordset using GetRows() but that Only gives me the values, I also need the Field names of the columns. Does anybody know how to do this?

Thanks,

[robjens]

Check out this you will find all you need there and more

http://allenbrowne.com/func-DAO.html

[ADezii]

I wrote a basic Code Template for you. Simply pass to this Function the name of a Table, Query, or SQL Statement, and it will load the Field Names and Data into a 2-Dimensional Array. I basically threw this together, so it definately can be improved upon.

Public Function fProcessData(strDataSource As String)
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim bytNumOfFields As Byte
Dim lngNumOfRecs As Long
Dim lngRowNum As Long
Dim bytFldCtr As Byte

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset(strDataSource, dbOpenSnapshot)

'Retrieve the Number of Fields in the Recordset
bytNumOfFields = rst.Fields.Count

'Retrieve the Number of Records in the Recordset
rst.MoveLast: rst.MoveFirst
lngNumOfRecs = rst.RecordCount

'Create a 2-Dimensional Array representing Rows and Columns in a Matrix
ReDim astrRecordset(0 To lngNumOfRecs, 0 To bytNumOfFields - 1)

'1st Row (0) contains Field Names, all other Rows Data
For bytFldCtr = 0 To bytNumOfFields - 1
  astrRecordset(0, bytFldCtr) = rst.Fields(bytFldCtr).Name
Next

'Populate Data only
For lngRowNum = 1 To lngNumOfRecs               'Rows containing Data
  For bytFldCtr = 0 To bytNumOfFields - 1       'Field values for each Row
    astrRecordset(lngRowNum, bytFldCtr) = rst.Fields(bytFldCtr)
  Next
    rst.MoveNext
Next

rst.Close
Set rst = Nothing

'Playback Demo
'For lngRowNum = 0 To lngNumOfRecs
  'For bytFldCtr = 0 To bytNumOfFields - 1
    'Debug.Print astrRecordset(lngRowNum, bytFldCtr),
  'Next
    'Debug.Print vbCrLf
'Next
End Function

[Executable]

I wrote a basic Code Template for you. Simply pass to this Function the name of a Table, Query, or SQL Statement, and it will load the Field Names and Data into a 2-Dimensional Array. I basically threw this together, so it definately can be improved upon.

Public Function fProcessData(strDataSource As String)
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim bytNumOfFields As Byte
Dim lngNumOfRecs As Long
Dim lngRowNum As Long
Dim bytFldCtr As Byte

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset(strDataSource, dbOpenSnapshot)

'Retrieve the Number of Fields in the Recordset
bytNumOfFields = rst.Fields.Count

'Retrieve the Number of Records in the Recordset
rst.MoveLast: rst.MoveFirst
lngNumOfRecs = rst.RecordCount

'Create a 2-Dimensional Array representing Rows and Columns in a Matrix
ReDim astrRecordset(0 To lngNumOfRecs, 0 To bytNumOfFields - 1)

'1st Row (0) contains Field Names, all other Rows Data
For bytFldCtr = 0 To bytNumOfFields - 1
  astrRecordset(0, bytFldCtr) = rst.Fields(bytFldCtr).Name
Next

'Populate Data only
For lngRowNum = 1 To lngNumOfRecs               'Rows containing Data
  For bytFldCtr = 0 To bytNumOfFields - 1       'Field values for each Row
    astrRecordset(lngRowNum, bytFldCtr) = rst.Fields(bytFldCtr)
  Next
    rst.MoveNext
Next

rst.Close
Set rst = Nothing

'Playback Demo
'For lngRowNum = 0 To lngNumOfRecs
  'For bytFldCtr = 0 To bytNumOfFields - 1
    'Debug.Print astrRecordset(lngRowNum, bytFldCtr),
  'Next
    'Debug.Print vbCrLf
'Next
End Function

Thanks to all.