By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Retrieving Data From a DAO Recordset

P: 8
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

Share this Question
Share on Google+
3 Replies


P: 37
Check out this you will find all you need there and more
http://allenbrowne.com/func-DAO.html
May 1 '10 #2

ADezii
Expert 5K+
P: 8,607
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

P: 8
@ADezii
Thanks to all.
May 6 '10 #4

Post your reply

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