Reading an array and outputting a record for each column in VBA
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER
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
Dim i As Integer
For i = 0 to UBound(YourArray)
Msgbox YourArray(i)
Next i
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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