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,
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
3 3909
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
...
|
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
...
|
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...
|
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()...
|
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...
|
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...
|
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();
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |