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

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

Posted on 2014-04-18
6
854 Views
Last Modified: 2016-02-13
Experts,

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;
END;
Drop x MSGNO1 - MSGNO15;
Run;      

Is there a way to do this in VBA.  I would like to use this in ACCESS if possible.
0
Comment
Question by:morinia
  • 2
  • 2
  • 2
6 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 40008947
If you always have 15, you can do this:

Dim i as Integer

For i = 1 to 15
  Msgbox yourarray(i)
Next

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.
0
 

Author Comment

by:morinia
ID: 40008969
Scott,

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.

Input  
001   001  055 075

Output
ID      Array_value
001        001
001        055
001        075
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40009196
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 84
ID: 40009318
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.
0
 

Author Comment

by:morinia
ID: 40009377
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
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 40009407
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.AddNew
        rs.fld1 = YourArray(i)
    rs.Update
Next i 

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Single Step Macro 4 39
Microsoft Access 97 and Delphi XE2 9 50
Flowing down data to other tables 13 30
How do you delete the header in a text file with VBA 5 29
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

791 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