473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 3909
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,834 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

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

Similar topics

5
by: aniket_sp | last post by:
i am using a data adapter and a dataset for filling and retrieving data into .mdb database. following is the code..... for the form load event Dim dc(0) As DataColumn Try If...
3
by: Jakob Petersen | last post by:
Hi, I need to increase the speed when retrieving data from a hosted SQL Server into VBA. I'm using simple SELECT statements. How important is the speed of my Internet connection? (I have...
0
by: DC01 | last post by:
I have added a new measure successfully into the normal cube. I then add it to the virtual cube and reprocess all cubes. I can browse the normal cube successfully. Then when I try and browse the...
1
by: hanusoft | last post by:
This is an example of Inserting and Retrieving data from xml file. private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here ...
1
by: hanusoft | last post by:
This is an example of Inserting and Retrieving data from xml file. private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here ...
4
by: smartin | last post by:
Hi, I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
3
ADezii
by: ADezii | last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method...
10
vikas1111
by: vikas1111 | last post by:
Hi All Can anyone give me an idea to solve the problem.. My Problem is ,, I want to Retrieving data from database and displaying in textbox... If anybody have link of some good tutorial on...
7
by: splendid9 | last post by:
Problem in retrieving data from a XML file.......this is my code- protected void Page_Load(object sender, EventArgs e) { DataSet ds = new DataSet(); ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.