Solved

How to Load Multiple Array Elements As A Group With One Line Of Code

Posted on 2011-02-12
12
577 Views
Last Modified: 2012-05-11
Is it possible in Access VBA to create multidimensional array elements and load them at once?

For example, instead of loading array elements at a time:
MyArr (0,1) = "Joe"
MyArr (0,2) = "Jones"
MyArr (0,3) = "225 Baldwin St"
Myarr (0,4) = "Princeton"
MyArr (0,5) = "Calif"
MyArr(0,6) = "98765"

Is there a more compact way to write this code on one line?

Thanks
0
Comment
Question by:Davisron867
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34879785
where are the data coming from?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 34879808
look at GetRows() function

Use DAO GetRows Method to Fill Variant Arrays
http://support.microsoft.com/kb/129856

http://msdn.microsoft.com/en-us/library/ff194427.aspx
0
 
LVL 84
ID: 34880049
As cap said, GetRows will work if it suits your needs.

If not, then I don't know of any way to load a multidimensional array in a single command. You could perhaps use a UserDefined Type, but you'd still have to fill that UDT and then put it into your array.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Davisron867
ID: 34880057
Microsoft-Visual-Basic.pdf

Hi Capricorn1. Pleasure to converse with you.

I attached an example of the code, which links or imports tables from a list of network locations

Thanks
0
 

Author Comment

by:Davisron867
ID: 34880165
(followup comment)
Sorry LSM...didn't see your comment until after my post, but its a pleasure to share my question with you as well.

The reason for my question is that my final dataset had an error last week, so I improved the commenting and am trying to simplify the code to make it easier to read and troubleshoot.
0
 

Author Comment

by:Davisron867
ID: 34880200
(followup comment)
The code I'm specifically referring to is

'LOAD ARRAY===========================================
ReDim aFileSpecs(7, 3)

'Set FileNames in Array
aFileSpecs(1, 1) = NewestFile(sStratDir, "*.xls")
aFileSpecs(2, 1) = NewestFile(sAcctsDir, "*.xls")
aFileSpecs(3, 1) = NewestFile(sLEDir, "*.xls")
aFileSpecs(4, 1) = NewestFile(sVenDir, "*.xls")
aFileSpecs(5, 1) = NewestFile(sSourceDir, "*.xls")
aFileSpecs(6, 1) = NewestFile(sNDPDir, "*.xls")
aFileSpecs(7, 1) = NewestFile(sALODir, "*.xls")

'Set FilePaths in Array
aFileSpecs(1, 2) = sStratDir & aFileSpecs(1, 1)
aFileSpecs(2, 2) = sAcctsDir & aFileSpecs(2, 1)
aFileSpecs(3, 2) = sLEDir & aFileSpecs(3, 1)
aFileSpecs(4, 2) = sVenDir & aFileSpecs(4, 1)
aFileSpecs(5, 2) = sSourceDir & aFileSpecs(5, 1)
aFileSpecs(6, 2) = sNDPDir & aFileSpecs(6, 1)
aFileSpecs(7, 2) = sALODir & aFileSpecs(7, 1)

'Set FileType Flag in Array
aFileSpecs(1, 3) = "Strats"
aFileSpecs(2, 3) = "Accts"
aFileSpecs(3, 3) = "LE"
aFileSpecs(4, 3) = "Vendors"
aFileSpecs(5, 3) = "SourceList"
aFileSpecs(6, 3) = "NDP"
aFileSpecs(7, 3) = "ALO"

'EMPTY ARRAY
For i = LBound(aFileSpecs) To UBound(aFileSpecs)
'Delete First Row (if empty) of PS Query output files
   Call DeleteFirstRow(aFileSpecs(i, 2), aFileSpecs(i, 3))
'Delete existing table link in Access
   DoCmd.DeleteObject acTable, aFileSpecs(i, 3)
'Link New Table to Access
   DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, aFileSpecs(i, 3), aFileSpecs(i, 2), True
Next i
End Sub
0
 
LVL 84
ID: 34880238
So you are getting an error?

Unless you specify differently, arrarys are zero-based - that is, they start at 0 and increment form there. So an array with three elements would be accessed like this:

MyArray(0) '/first
MyArray(1) '/second
MyArray(2) '/third

Note that you CAN set the lower bound of an array to 1, but I don't see where you've done that in this code specifically.

So you would do this:

aFileSpecs(0, 1) = NewestFile(sStratDir, "*.xls")
aFileSpecs(1, 1) = NewestFile(sAcctsDir, "*.xls")
aFileSpecs(2, 1) = NewestFile(sLEDir, "*.xls")
aFileSpecs(3, 1) = NewestFile(sVenDir, "*.xls")
aFileSpecs(4, 1) = NewestFile(sSourceDir, "*.xls")
aFileSpecs(5, 1) = NewestFile(sNDPDir, "*.xls")
aFileSpecs(6, 1) = NewestFile(sALODir, "*.xls")

0
 

Author Comment

by:Davisron867
ID: 34880303
LSM:

Thanks for your response.

There is an Option Base 1 Statement in the Module declarations. I'm not getting any run-time errors...i simply had a issue where I was making updates to the code and suddenly the data stopped tying-out. When I went through the code, I realized that mistakes can be made when its hard to read, so I was just trying to organize it better and make it more compact and efficient.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34881120
If you declare myarr as variant, you can do this:

myarr = Array(Array("Joe", "Jones", "225 Baldwin St", "Princeton", "Calif", "98765"))

it works - watch myarr in debug and you'll see this makes

myarr(0,1) = "Joe"
myarr(0,2) = "Jones"

etc...

Dave
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 34881160
I "mistyped".  You do it like this (create an array of arrays - and can work with them like 2-dim arrays, just the referencing is a bit difference - see the debug.print statement, below:

Sub loadArray()
Dim myarr() As Variant

    myarr = Array(Array("Joe", "Jones", "225 Baldwin St", "Princeton", "Calif", "98765"))
   
    For i = 0 To 5
        Debug.Print myarr(0)(i)
    Next i
   
   
End Sub
0
 

Author Comment

by:Davisron867
ID: 34881422
Thanks guys.

I actually meant to have dlmille's answer show as the Accepted Solution and Capricorn1's answer be the Assisted Solution, but no big deal.

Both the GetRows method and the Array of Arrays structure are perfect for this task.
0
 

Author Comment

by:Davisron867
ID: 34881427
FYI...
Tushar Mehta's overview of the Array of Arrays Structure -  http://www.tushar-mehta.com/publish_train/book_vba/08_variants.htm

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question