Array Structure problems

Ciarano · March 3rd, 2004, 10:01 AM

Hi guys

I've got the results of a recordset that I want to equate to an array
like so

VarArray = RecordSet.GetRows

I've declared the VarArray like so

Dim VarArray() As Variant

When I query the VarArray, the structure is

VarArray(0,1)
VarArray(0,2)
VarArray(0,3) and so on

I need the structure to start at 1 and be in the following format

VarArray(1,1)
VarArray(2,1)
VarArray(3,1) and so on

I tried putting OPTION BASE 1 at the top of my module but it doesn't seem to be taking effect.

Can anyone help me out?

Cheers

Ciaran

xgbnow · March 3rd, 2004, 10:31 AM

Well I am actually surprised to see someone using DAO. It caught me off guard. I looked at the help files just to verify my thoughts and that is the GetRows() method of the recordset object returns a zero based two-dimensional array. So, you are not going to be able to have the method return an array with base 1.

I wonder why you need to have a base 1 array? But, you can create another array that is based one and copy the array returned from the GetRow() method.

In regards to your statement of

VarArray(0,1)
VarArray(0,2)
VarArray(0,3) and so on

I need the structure to start at 1 and be in the following format

VarArray(1,1)
VarArray(2,1)
VarArray(3,1) and so on

Again that is not really up to you. You need to look at the documentation for the GetRows() method as it explains the way the data is returned. Once you understand that then you need to write your code accordingly.

Larry Asher

Ciarano · March 3rd, 2004, 11:02 AM

Hi Larry

I'm actually using ADO connecting with Oracle9i to issue OLAP command and retrieve the data through RecordSets.

I have a complex Excel Add-in which uses a lot of VB code to manipulate data. The original code calls for arrays to be indexed from 1.

Thanks

Ciaran

joefawcett · March 3rd, 2004, 11:52 AM

Just create a couple of helper methods for your array.

Code:

Public Function GetItem(arr, row, column)
  GetItem = arr(row - 1, column) 
End Function

Then you can treat it as a one based array.

--

Joe

xgbnow · March 3rd, 2004, 12:16 PM

I took a second look at the MSDN help files for the ADO recordset object methods and the GetRows() method is not documented. So, out of curiosity I tried to call the GetRows() method from the ado recordset object and sure enough it's there.

I still think that you can copy the returned array into a 1 based array get what you want.

Larry Asher

joefawcett · March 3rd, 2004, 12:57 PM

http://msdn.microsoft.com/library/de...asp?frame=true

--

Joe

Ciarano · March 4th, 2004, 07:10 AM

Thanks for your replies

Cheers

Ciaran