|
 |
access thread: Multi-Dimensional Arrays
Message #1 by "JJ Campbell" <jjcampbell@w...> on Fri, 13 Jul 2001 02:04:38
|
|
I'm an old Clipper Head and I'm really struggling with Access and VB
arrays. I want to create a muli-dimensional array in code by assigning
the values directly to the array.
For example (simple), if I had two rows and three columns of values that
looked like this:
"strTblName1", "strDatabaseType1", blnStructureOnly1
"strTblName2", "strDatabaseType2", blnStructureOnly2
In Clipper I could do somthing like this (excuse me If I get it wrong -
I'm getting a little rusty):
MyArray := { {"strTblName1", "strDatabaseType1", blnStructureOnly1}
{"strTblName2", "strDatabaseType2",
blnStructureOnly2} }
I've searched for hours and there are plenty of examples of how you can do
this in VBA for single dimension arrays but I can not find anything for
multi dimensional arrays. Do I have to do this value-by-value?
--
*****
* J. J. Campbell
* Arlington, Virginia
* MailTo:jjcampbell@w...
*****
Message #2 by "Bob Bedell" <bdbedell@m...> on Sat, 14 Jul 2001 21:47:09
|
|
Dear Mr. Old Clipper Head,
The GetRows method of a recordset object (ADO or DAO) is your ticket to an
Access two-dimensional array. A peek in the Object Browser reveals that
both ADO and DAO recordset objects have a GetRows method, so you can swing
both ways. I'm doing ADO.
The GetRows method copies the data of a recorset into a Variant variable.
Access sets the size of the array for you. Check out the help file on the
GetRows method for an explanation of its parameters.
Anyway, open up Northwind, rename the ?Employees? table to ?tblEmployees?
(I insist on standardized naming conventions, and will not tolerate
slackers) create a new module and, past the following code into it.
Public Sub FillArray()
Dim rst As ADODB.Recordset
Dim varData As Variant ' Stores the array
Dim intCount As Integer ' Counts records in recordset
Dim intArrayRecord As Integer ' First value stored is intCount-1
because the array in this example is zero-based.
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "tblEmployees"
' Get all rows and two specified columns.
varData = rst.GetRows( _
Fields:=Array("LastName", "FirstName"))
rst.Close
Set rst = Nothing
intCount = UBound(varData, 2) + 1
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1, intArrayRecord)
Next intArrayRecord
End Sub
Open the Locals Window and the Immediate Window. Place your cursor
anywhere in the procedure and press F8. Continuing pressing F8 to step
through the code. The array is filled at the line varData = rst.GetRows
(Fields:=Array("LastName", "FirstName")). A plus sign will appear next to
the varData listing in the Locals Window when the array receives values.
Click the plus sign and the array elements will be displayed. Clicking a
plus sign for a particular element will display the values it holds.
Resume pressing F8. The following values should fill your Immediate Window:
Dodsworth Anne
Callahan Laura
King Robert
Suyama Michael
Buchanan Steven
Peacock Margaret
Leverling Janet
Fuller Andrew
Davolio Nancy
Your array contains 9 elements.
Suppose you want to add a third column to your array output (not a third
dimension, mind you, that?s beyond me, just display column three of your 2-
dimensional row/column array. Just modify the following lines of code:
varData = rst.GetRows( _Fields:=Array(?"LastName", "FirstName"))
becomes?
varData = rst.GetRows( _Fields:=Array
(?EmployeeID?, "LastName", "FirstName"))
? adds the EmplyeeID Field
and?
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1, intArrayRecord)
Next intArrayRecord
becomes?
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1,
intArrayRecord), varData(2, intArrayRecord)
Next intArrayRecord
Run these modifications and your Immediate Window should now display:
9 Dodsworth Anne
8 Callahan Laura
7 King Robert
6 Suyama Michael
5 Buchanan Steven
4 Peacock Margaret
3 Leverling Janet
2 Fuller Andrew
1 Davolio Nancy
If you want fast access to data that you don?t have to write to, plus fast
access to random data in that recordset, fill a forward-only Arrray!
Quickest route to take.
I really hope all this worked. If it didn?t, it?s a mere typo or two. I
tested it a few times and it flew for me. If you run into problems, I?m at
bdbedell@m... Love to hear from you.
> I'm an old Clipper Head and I'm really struggling with Access and VB
> arrays. I want to create a muli-dimensional array in code by assigning
> the values directly to the array.
>
> For example (simple), if I had two rows and three columns of values that
> looked like this:
> "strTblName1", "strDatabaseType1", blnStructureOnly1
> "strTblName2", "strDatabaseType2", blnStructureOnly2
>
> In Clipper I could do somthing like this (excuse me If I get it wrong -
> I'm getting a little rusty):
>
> MyArray := { {"strTblName1", "strDatabaseType1", blnStructureOnly1}
> {"strTblName2", "strDatabaseType2",
> blnStructureOnly2} }
>
> I've searched for hours and there are plenty of examples of how you can
do
> this in VBA for single dimension arrays but I can not find anything for
> multi dimensional arrays. Do I have to do this value-by-value?
>
> --
> *****
> * J. J. Campbell
> * Arlington, Virginia
> * MailTo:jjcampbell@w...
> *****
Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 16 Jul 2001 08:52:09 -0700
|
|
One other way to go is with a user-defined type. For instance (warning:
"air code" follows)
' ============================================
Public Type CopySpec
strTblName as String
strDatabaseType as String
blnStructureOnly as Boolean
End Type
' Then in your code you can create a single dimension array of the UDT:
Dim Specs(0 to 8) as CopySpec
Specs(0).strTblName = "Table1"
Specs(0).strDatabaseType = "MSAccess"
Specs(0).blnStructureOnly = False
' ============================================
Or if you're not crazy about that, you can also manually declare a
multidimensional array (since you want to store different data types, you'll
have to go with an array of Variants):
Dim Specs(0 to 8, 0 to 2) as Variant
Specs(0,0) = "Table1"
Specs(0,1) = "MSAccess"
Specs(0,2) = False
Hope that helps,
-Roy
-----Original Message-----
From: Bob Bedell [mailto:bdbedell@m...]
Sent: Saturday, July 14, 2001 2:46 PM
To: Access
Subject: [access] Re: Multi-Dimensional Arrays
Dear Mr. Old Clipper Head,
The GetRows method of a recordset object (ADO or DAO) is your ticket to an
Access two-dimensional array. A peek in the Object Browser reveals that
both ADO and DAO recordset objects have a GetRows method, so you can swing
both ways. I'm doing ADO.
The GetRows method copies the data of a recorset into a Variant variable.
Access sets the size of the array for you. Check out the help file on the
GetRows method for an explanation of its parameters.
Anyway, open up Northwind, rename the "Employees" table to "tblEmployees"
(I insist on standardized naming conventions, and will not tolerate
slackers) create a new module and, past the following code into it.
Public Sub FillArray()
Dim rst As ADODB.Recordset
Dim varData As Variant ' Stores the array
Dim intCount As Integer ' Counts records in recordset
Dim intArrayRecord As Integer ' First value stored is intCount-1
because the array in this example is zero-based.
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "tblEmployees"
' Get all rows and two specified columns.
varData = rst.GetRows( _
Fields:=Array("LastName", "FirstName"))
rst.Close
Set rst = Nothing
intCount = UBound(varData, 2) + 1
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1, intArrayRecord)
Next intArrayRecord
End Sub
Open the Locals Window and the Immediate Window. Place your cursor
anywhere in the procedure and press F8. Continuing pressing F8 to step
through the code. The array is filled at the line varData = rst.GetRows
(Fields:=Array("LastName", "FirstName")). A plus sign will appear next to
the varData listing in the Locals Window when the array receives values.
Click the plus sign and the array elements will be displayed. Clicking a
plus sign for a particular element will display the values it holds.
Resume pressing F8. The following values should fill your Immediate Window:
Dodsworth Anne
Callahan Laura
King Robert
Suyama Michael
Buchanan Steven
Peacock Margaret
Leverling Janet
Fuller Andrew
Davolio Nancy
Your array contains 9 elements.
Suppose you want to add a third column to your array output (not a third
dimension, mind you, that's beyond me, just display column three of your 2-
dimensional row/column array. Just modify the following lines of code:
varData = rst.GetRows( _Fields:=Array(""LastName", "FirstName"))
becomes...
varData = rst.GetRows( _Fields:=Array
("EmployeeID", "LastName", "FirstName"))
' adds the EmplyeeID Field
and...
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1, intArrayRecord)
Next intArrayRecord
becomes...
For intArrayRecord = intCount - 1 To 0 Step -1
Debug.Print varData(0, intArrayRecord), varData(1,
intArrayRecord), varData(2, intArrayRecord)
Next intArrayRecord
Run these modifications and your Immediate Window should now display:
9 Dodsworth Anne
8 Callahan Laura
7 King Robert
6 Suyama Michael
5 Buchanan Steven
4 Peacock Margaret
3 Leverling Janet
2 Fuller Andrew
1 Davolio Nancy
If you want fast access to data that you don't have to write to, plus fast
access to random data in that recordset, fill a forward-only Arrray!
Quickest route to take.
I really hope all this worked. If it didn't, it's a mere typo or two. I
tested it a few times and it flew for me. If you run into problems, I'm at
bdbedell@m... Love to hear from you.
> I'm an old Clipper Head and I'm really struggling with Access and VB
> arrays. I want to create a muli-dimensional array in code by assigning
> the values directly to the array.
>
> For example (simple), if I had two rows and three columns of values that
> looked like this:
> "strTblName1", "strDatabaseType1", blnStructureOnly1
> "strTblName2", "strDatabaseType2", blnStructureOnly2
>
> In Clipper I could do somthing like this (excuse me If I get it wrong -
> I'm getting a little rusty):
>
> MyArray := { {"strTblName1", "strDatabaseType1", blnStructureOnly1}
> {"strTblName2", "strDatabaseType2",
> blnStructureOnly2} }
>
> I've searched for hours and there are plenty of examples of how you can
do
> this in VBA for single dimension arrays but I can not find anything for
> multi dimensional arrays. Do I have to do this value-by-value?
>
> --
> *****
> * J. J. Campbell
> * Arlington, Virginia
> * MailTo:jjcampbell@w...
> *****
---
|
|
|
View Cart
