PDA

View Full Version : How to make a Table from an array or an Array from a table?



mud2
05-13-2005, 08:16 AM
I have a Two-Dimensional Array that I want to make a table of, or conversely, I want to set up a table and transfer its contents into a two dimensional array. The array is convienient for VBA'ing, while the table is convienient for Querying.

OBP
05-15-2005, 11:44 AM
Make the table first, assuming you can visualise it's structure. Create a form for the table, add a command button to the form use it's on event procedure to run VBA to create the array from the forms data.

mud2
05-16-2005, 09:20 PM
I was hopeing there was a more direct way! OK, I enter a string of keys, separated by commas or blanks. Then using the MID function I read and parse the sting, putting each key into an array. Then I make a blank table, and a Form based on that table. In a command box in the form I Go to record 1, set the key value in the form = to the first element of the array, and in a FOR loop, advance to the next record, and set the key value to the next array value, etc. Then I use this table as the row source for a list box.
WOW! But it works!

xCav8r
05-17-2005, 10:39 PM
Mud, there are indeed much easier ways to do this?easier, in fact, than what you have been thinking. The easiest ways involve using either ADO or DAO. Reference to these libraries are included by default in Access, so you can learn more about these by looking at the crappy help documentation in the Visual Basic Editor.

First, in both ADO and DAO, the GetRows method of the Recordset object returns a two dimensional array from a table where the first dimension contains the fields (columns) and the second dimension contains the records (rows). For more information, search for GetRows in the Visual Basic Editor. The examples there should be sufficient, but if not, let me know.

Second, turning an array into a table isn?t as convenient as the converse, but it?s still not difficult. I?ve never tried to do or learn this in ADO (don?t even know if it?s possible), so I?ll just say look up CreateTableDef in the Visual Basic Editor, and that?ll give you the DAO information you need to create a table. As for sticking data in the table after it?s created, that?s just a matter of looping through the array. I suspect from your post that you?re familiar enough with loops to figure that out after looking at the help documentation. If not, let me know. I've got eight years of examples. :)