Arrays

[ancientheretic]

Does anyone know if MS Access supports storing data in arrays? My version is 2000. Thanks.

[willakawill]

Does anyone know if MS Access supports storing data in arrays? My version is 2000. Thanks.

Hi
Yes it does in VBA

Dim MyArray(10) As Integer

Or more advanced with regard to your question about data

Dim arData As Variant
 
arData = myRecordset.GetRows()

[ancientheretic]

This is probably a stupid question, but I'm used to storing data explicitly in arrays. Pseudo-wise, the table would be defined something like --

create table XYZ
column name
column phone
column month occurs 12 times

Then you would insert something like this --

Insert into table XYZ
(name, phone, month)
values
(
'Scott',
'555-1234',
1,2,3,4,5,6,7,8,9,10,11,12
)

From your example I see the programming aspect of arrays, but not the table aspect.

[willakawill]

This is probably a stupid question, but I'm used to storing data explicitly in arrays. Pseudo-wise, the table would be defined something like --

create table XYZ
column name
column phone
column month occurs 12 times

Then you would insert something like this --

Insert into table XYZ
(name, phone, month)
values
(
'Scott',
'555-1234',
1,2,3,4,5,6,7,8,9,10,11,12
)

From your example I see the programming aspect of arrays, but not the table aspect.

Right and if you want to query this data directly you would use a recordset which would, in fact, hold an array. If you wanted to move the data to an array you would use the GetRows() method of the recordset which means you would have a 2 dimensional array

ar(columns, rows)
ar(0, 0) would be 'Scott'
ar(1, 0) would be '555-1234'
ar(2, 0) would be 1
and so on

[ancientheretic]

I think I see. So I can't explicitly define columns in Access with a subscript? For instance --

column1, column2, ...column12

rather than --

column(1), column(2), ...column(12)

?

And then in VB (pseudo code) --
select column1, column2, ...column12 from table
into myarray(1), column(2), ...column(12)

Hopefully I've explained it better. Thanks.

[willakawill]

I think I see. So I can't explicitly define columns in Access with a subscript? For instance --

column1, column2, ...column12

rather than --

column(1), column(2), ...column(12)

?

And then in VB (pseudo code) --
select column1, column2, ...column12 from table
into myarray(1), column(2), ...column(12)

Hopefully I've explained it better. Thanks.

In vb you will define a recordset object and use a select statement to populate the object from a table or set of joined tables. Then, in your code, you will use the recordset as an array with a particular syntax:

Dim MyRecordset As Recordset
Dim stSQL As String
Dim lngValue As Long
Dim stValue As String
 
stSQL = "SELECT ColumnName1, ColumnName2 etc FROM MyTable"
 
Set MyRecordset = CurrentDb.OpenRecordset(stSQL)
 
lngValue = MyRecordset("ColumnName1")
stValue = MyRecordset("ColumnName2")

etc
These values only refer to the first row. If you need to step through the rows for example:

Do While Not MyRecordset.EOF
   lngValue = lngValue + MyRecordset("ColumnName1")
   MyRecordset.MoveNext
Loop
 
MsgBox "Total: " & lngValue

Working with recordsets is usually much more involved than this and I have kept it as simple as I can

[willakawill]

You can use an index with the recordset

lngValue = MyRecordset(1)

But this is very bad programming practise and not good for your sanity