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
Solved

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

Posted on 2011-02-12
12
576 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

860 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