Option Base and reading from Access
 

I have a VBA macro that is getting an array of data from an Access
database. I use this code to retrieve the data:

Set rs = db.OpenRecordset("Select Age,Layer from Factors where LOB ='" &
Rnames(j) & "';")
[error testing]
DBRecord = rs.GetRows(30)

The module it resides in has Option Base 1 at the top. However the array
DBRecord that it returns has an Option Base of 0. This becomes very
confusing as the array is then passed as an argument to a function that is
entirely set up as Option Base 1, yet now inside it this one array has a
different Option Base.

Is there some elegant solution other than rewriting the function and
converting it to Option Base 0?

Don <donwiss at panix.com.

Option Base and reading from Access
 

Don,

As a general programming practice, you should never explicitly
hard code the bottom (or top) of an array when reading or writing
its elements. Use LBound and UBound instead.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Don Wiss" wrote in message
...
I have a VBA macro that is getting an array of data from an
Access
database. I use this code to retrieve the data:

Set rs = db.OpenRecordset("Select Age,Layer from Factors where
LOB ='" &
Rnames(j) & "';")
[error testing]
DBRecord = rs.GetRows(30)

The module it resides in has Option Base 1 at the top. However
the array
DBRecord that it returns has an Option Base of 0. This becomes
very
confusing as the array is then passed as an argument to a
function that is
entirely set up as Option Base 1, yet now inside it this one
array has a
different Option Base.

Is there some elegant solution other than rewriting the
function and
converting it to Option Base 0?

Don <donwiss at panix.com.

Option Base and reading from Access
 

On Wed, 21 Jan 2004 19:07:32 -0600, Chip Pearson wrote:

As a general programming practice, you should never explicitly
hard code the bottom (or top) of an array when reading or writing
its elements. Use LBound and UBound instead.

Oh I am, but this is intricate indexing into an array for interpolation on
two dimensions. I'm gathering the solution is to rewrite the function into
Option Base 0.

Don <donwiss at panix.com.

Option Base and reading from Access
 

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
want to consider the ConvertBase function.

E.g., if myArray is a 2-D 0-based array (in both dimensions),

ConvertBase myArray, 1, 1 will increase both the lower and upper bound
of each dimension by 1.

Alan Beban

Don Wiss wrote:
I have a VBA macro that is getting an array of data from an Access
database. I use this code to retrieve the data:

Set rs = db.OpenRecordset("Select Age,Layer from Factors where LOB ='" &
Rnames(j) & "';")
[error testing]
DBRecord = rs.GetRows(30)

The module it resides in has Option Base 1 at the top. However the array
DBRecord that it returns has an Option Base of 0. This becomes very
confusing as the array is then passed as an argument to a function that is
entirely set up as Option Base 1, yet now inside it this one array has a
different Option Base.

Is there some elegant solution other than rewriting the function and
converting it to Option Base 0?

Don <donwiss at panix.com.