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

Arrays

P: 3
Does anyone know if MS Access supports storing data in arrays? My version is 2000. Thanks.
Jan 6 '07 #1
Share this Question
Share on Google+
6 Replies


100+
P: 1,646
Does anyone know if MS Access supports storing data in arrays? My version is 2000. Thanks.
Hi
Yes it does in VBA
Expand|Select|Wrap|Line Numbers
  1. Dim MyArray(10) As Integer
Or more advanced with regard to your question about data
Expand|Select|Wrap|Line Numbers
  1. Dim arData As Variant
  2.  
  3. arData = myRecordset.GetRows()
Jan 6 '07 #2

P: 3
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.
Jan 6 '07 #3

100+
P: 1,646
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
Jan 6 '07 #4

P: 3
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.
Jan 6 '07 #5

100+
P: 1,646
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:

Expand|Select|Wrap|Line Numbers
  1. Dim MyRecordset As Recordset
  2. Dim stSQL As String
  3. Dim lngValue As Long
  4. Dim stValue As String
  5.  
  6. stSQL = "SELECT ColumnName1, ColumnName2 etc FROM MyTable"
  7.  
  8. Set MyRecordset = CurrentDb.OpenRecordset(stSQL)
  9.  
  10. lngValue = MyRecordset("ColumnName1")
  11. stValue = MyRecordset("ColumnName2")
etc
These values only refer to the first row. If you need to step through the rows for example:

Expand|Select|Wrap|Line Numbers
  1. Do While Not MyRecordset.EOF
  2.    lngValue = lngValue + MyRecordset("ColumnName1")
  3.    MyRecordset.MoveNext
  4. Loop
  5.  
  6. MsgBox "Total: " & lngValue
Working with recordsets is usually much more involved than this and I have kept it as simple as I can
Jan 6 '07 #6

100+
P: 1,646
You can use an index with the recordset

Expand|Select|Wrap|Line Numbers
  1. lngValue = MyRecordset(1)
But this is very bad programming practise and not good for your sanity
Jan 6 '07 #7

Post your reply

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