Reading an array and outputting a record for each column in VBA


I have this code in SAS which read a record that has 15 msg columns
MSG0 - MSG15.  In SAS I can use the following code to generate an output line for each message,

Data Merge_Edits_Array;
      Set Edits;
      ARRAY EditsArray MSGNO1 - MSGNO15;
Do x = 1 to 15;
      Edit = EditsArray[x];
      If Edit ~= " " then OUTPUT;
Drop x MSGNO1 - MSGNO15;

Is there a way to do this in VBA.  I would like to use this in ACCESS if possible.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
If you always have 15, you can do this:

Dim i as Integer

For i = 1 to 15
  Msgbox yourarray(i)

I'm not sure what you mean by "output" line - can you explain a little further exactly what you want to do with each element of that array?

If your number of elements in the array is variable, you'd have to change that a bit:

Dim i As Integer

For i = 0 to UBound(YourArray)
  Msgbox YourArray(i)
Next i

Note too that this depends on how you're getting that array from SAS, and whether the object type is compatible with VBA.
moriniaAdvanced Analytics AnalystAuthor Commented:

By output I mean to write a record.  I will then have a file with an ID number and multiple lines representative of each value in the array.

001   001  055 075

ID      Array_value
001        001
001        055
001        075
There is no concept of a column array because the tabledef/querydef collection is one and if you had sub arrays that included only some of the columns, that would violate first normal form and therefore would simply not be supported by relational database.

Take a look at the querydefs/tabledefs/ fields collection.  There are samples in help.  From your example it looks like the first column is the Foreign Key and the other columns are all instances of some value.  So you can reference the first column specifically by name and the others in a loop starting at 1 or 2 depending on whether your array is 0 or 1 based.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to give us a little more information about the structure, both incoming and output. As Pat mentioned, it looks like your first value if a Foreign Key, but that's hard to say unless we know more about it.
moriniaAdvanced Analytics AnalystAuthor Commented:
Using the code Scott gave as a base, how would I write out the record in VB to table in ACCESS instead of displaying in in MSGBOX.  I think with that I can figure the rest out.

Dim i As Integer

For i = 0 to UBound(YourArray)
  Msgbox YourArray(i)
Next i
PatHartmanConnect With a Mentor Commented:
You need to open a recordset using DAO or ADO.  Then inside the loop, you would write the record to the recordset instead of displaying it.

Dim i As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim rs As DAO.Recordset

Set db = CurrentDB()
set td = db.TableDefs!yourtablename
Set rs = td.OpenRecordset

For i = 0 to UBound(YourArray)
        rs.fld1 = YourArray(i)
Next i 

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.