using a multidimensional array in an SQL statement

[sub5]

Hi all,
For an access97 dbase, I am trying to replace the temporary tables with multidimentional arrays.

I can create an array OK, but how do I replace the temporary table (tbl2) in this SQL with the array?

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCTROW tbl1.Fld1, tbl2.fld2 FROM (tbl2 LEFT JOIN tbl3 ON tbl2.fld2 = tbl3.fld2) LEFT JOIN tbl1 ON tbl2.fld2 = tbl1.fld2 WHERE (((tbl3.fld2) Is Null));")

 

[sub5]

contd.

The code above is in a procedure.

I also have SQL code within a listbox rowsource. Is there a way to substitute the temporary tbl1 references with the array in that SQL?

Thanks in advance for any help.

 

[Golom]

My interpretation of this is that you want SQL to treat an array as though it was a table. The two are not compatible.

Although an array may be construed to be composed of rows and columns, it is in fact a block of contiguous locations in memory and the "rows and columns" representation is accessible only through subscripted array references.

In contrast a table in a database is in fact composed of a collection of column objects which are a completely different internal representation than arrays.

In short SQL has no mechanisms to handle arrays (other than the fact that you can refer to elements of an array as data values in the SQL statement.)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[sub5]

Thanks Golom,
What I have done instead is to build an SQL string by looping through the Array rows and placing the required column value into the WHERE section of the SQL. Then refer to that SQL when opening a recordset.

My second question : how can I use an array to replace a table in a SQL refered to in a control rowsource is proving harder. I am currently looking at using a "user defined function". Never used them before, struggling to understand them but should get there - any tips? Am I barking up the wrong daffodil?!

 

[Golom]

Depends on the specific control but in general, no you cannot use an array as a rowsource for much the same reasons as those outlined above. Arrays are contiguous memory locations ... not rows and columns. Some controls such as the FlexGrid controls do have a mechanism to refer to row-column intersections using an array-like scheme (look at the .TextMatrix property for example.) You could probably load a FlexGrid with something like

For i = 1 to RowCount
   For j = 1 to ColCount
      myFlexGrid.TextMatrix(i,j) = myArray(i,j)
   Next j
Next i

But that's one-way only by which I mean that a change to a cell in the grid will not cause a change to the corresponding value in the array.

The real issue (to me at least) is "Why do you feel that you need to do this?"

Most controls that support a recordsource have all the bells and whistles to support adding, deleting and modifying values in the recordsource. If you could somehow coerce an array to be the record source then you would need to re-invent the wheel by writing your own code to support all those activities.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[sub5]

Hi Golom,
thanks for your thoughts. The reason i need to do this is because I have several tables which the user needs to update to reflect a single instance of a real life document. As such ids need to be carried from one table to another. So in my process the user updates the details in table one, eg a header, then the ids for the header records (can be more than one for a single real life document) are held in an array. The "products" they have chosen to populate the first table then need to have the details hung onto them in the second table. I want a control on the second form to have the products populated in the first as it's row source, but if the user updates product one then the control looses product one from its list.