469,913 Members | 2,648 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

Retrieving Data From a DAO Recordset

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,
Apr 30 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fProcessData(strDataSource As String)
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim bytNumOfFields As Byte
  5. Dim lngNumOfRecs As Long
  6. Dim lngRowNum As Long
  7. Dim bytFldCtr As Byte
  8.  
  9. Set MyDB = CurrentDb()
  10. Set rst = MyDB.OpenRecordset(strDataSource, dbOpenSnapshot)
  11.  
  12. 'Retrieve the Number of Fields in the Recordset
  13. bytNumOfFields = rst.Fields.Count
  14.  
  15. 'Retrieve the Number of Records in the Recordset
  16. rst.MoveLast: rst.MoveFirst
  17. lngNumOfRecs = rst.RecordCount
  18.  
  19. 'Create a 2-Dimensional Array representing Rows and Columns in a Matrix
  20. ReDim astrRecordset(0 To lngNumOfRecs, 0 To bytNumOfFields - 1)
  21.  
  22. '1st Row (0) contains Field Names, all other Rows Data
  23. For bytFldCtr = 0 To bytNumOfFields - 1
  24.   astrRecordset(0, bytFldCtr) = rst.Fields(bytFldCtr).Name
  25. Next
  26.  
  27. 'Populate Data only
  28. For lngRowNum = 1 To lngNumOfRecs               'Rows containing Data
  29.   For bytFldCtr = 0 To bytNumOfFields - 1       'Field values for each Row
  30.     astrRecordset(lngRowNum, bytFldCtr) = rst.Fields(bytFldCtr)
  31.   Next
  32.     rst.MoveNext
  33. Next
  34.  
  35. rst.Close
  36. Set rst = Nothing
  37.  
  38. 'Playback Demo
  39. 'For lngRowNum = 0 To lngNumOfRecs
  40.   'For bytFldCtr = 0 To bytNumOfFields - 1
  41.     'Debug.Print astrRecordset(lngRowNum, bytFldCtr),
  42.   'Next
  43.     'Debug.Print vbCrLf
  44. 'Next
  45. End Function

3 3386
robjens
37
Check out this you will find all you need there and more
http://allenbrowne.com/func-DAO.html
May 1 '10 #2
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fProcessData(strDataSource As String)
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim bytNumOfFields As Byte
  5. Dim lngNumOfRecs As Long
  6. Dim lngRowNum As Long
  7. Dim bytFldCtr As Byte
  8.  
  9. Set MyDB = CurrentDb()
  10. Set rst = MyDB.OpenRecordset(strDataSource, dbOpenSnapshot)
  11.  
  12. 'Retrieve the Number of Fields in the Recordset
  13. bytNumOfFields = rst.Fields.Count
  14.  
  15. 'Retrieve the Number of Records in the Recordset
  16. rst.MoveLast: rst.MoveFirst
  17. lngNumOfRecs = rst.RecordCount
  18.  
  19. 'Create a 2-Dimensional Array representing Rows and Columns in a Matrix
  20. ReDim astrRecordset(0 To lngNumOfRecs, 0 To bytNumOfFields - 1)
  21.  
  22. '1st Row (0) contains Field Names, all other Rows Data
  23. For bytFldCtr = 0 To bytNumOfFields - 1
  24.   astrRecordset(0, bytFldCtr) = rst.Fields(bytFldCtr).Name
  25. Next
  26.  
  27. 'Populate Data only
  28. For lngRowNum = 1 To lngNumOfRecs               'Rows containing Data
  29.   For bytFldCtr = 0 To bytNumOfFields - 1       'Field values for each Row
  30.     astrRecordset(lngRowNum, bytFldCtr) = rst.Fields(bytFldCtr)
  31.   Next
  32.     rst.MoveNext
  33. Next
  34.  
  35. rst.Close
  36. Set rst = Nothing
  37.  
  38. 'Playback Demo
  39. 'For lngRowNum = 0 To lngNumOfRecs
  40.   'For bytFldCtr = 0 To bytNumOfFields - 1
  41.     'Debug.Print astrRecordset(lngRowNum, bytFldCtr),
  42.   'Next
  43.     'Debug.Print vbCrLf
  44. 'Next
  45. End Function
May 1 '10 #3
@ADezii
Thanks to all.
May 6 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.