old array with new version Office 2002

[ruomingxu via AccessMonster.com]

I have an Access database that was designed with Office1995 version. It has
a Sub which has array in it and it doesn't seem working with MS Office 2002
XP version. The program is a bit long but basically it is like this:

(the following Sub is called by another Private Sub)

Sub Oletest(cnn as ADODB.Connection, ....)
Dim SqlQuery As String
Dim RcdSet As DAO.RecordSet
Dim ppArray() As Varian
ReDim ppArray(1 to 10, 1 to 2)
...

SqlQuery = "SELECT * FROM ..."
Set RcdSet = CurrentDb.OpenRecordSet(SqlQuery)
If statement ....
...
Do Until statement ...
...
ppArray(arrayVal,1) = RcdSet![tax_info_1]
...
Loop
End if
...

The database works with Office 1995 but not with Office 2002. And I don't
see anything wrong with the array statement (I suspect the problem may be in
those array statements). Anybody knows about it? Thanks. Appreciate it.

 

[Brendan Reynolds]

Check the references (from the Tools menu in the VBA editor, choose
References). A missing or mis-matched reference is often the problem when
something works in one version but not in another.

The following code executed without error for me. I had to change a few
things in order to test it with my test data and without the missing code.
Comments below.

If you still can't get it to work after this, try describing what happens
when the code runs. Is there an error message?


'The ADODB.Connection is not used in the code posted.
Public Sub Oletest() '(cnn As ADODB.Connection)

Dim SqlQuery As String
Dim RcdSet As DAO.Recordset

'You were missing the 't' from 'Variant' but
'presumably that was just a typo in the
'newsgroup post.
Dim ppArray() As Variant

ReDim ppArray(1 To 10, 1 To 2)

'This wasn't in the posted code. Make sure you
'are assigning a valid value to arrayVal before
'using it.
Dim arrayVal As Variant
arrayVal = 1

'..

'Changed this so I could test with my test data ...
SqlQuery = "SELECT * FROM Assets"

Set RcdSet = CurrentDb.OpenRecordset(SqlQuery)
'If statement ....
' ...
' Do Until statement ...
' ...

'Changed field name to one that exists in my test data.
ppArray(arrayVal, 1) = RcdSet![Description]

' ...
' Loop
'End If
End Sub

 

[ruomingxu via AccessMonster.com]

Brendan,

When runs, the program has no error messages. Since I have an old version
Office95 with which the old programs runs well and I can compare the result
with the one run by Office2002 Access, I found that some data were missing.
That's why I suspected that the array might be a problem. I actually don't
know where the problem is.

The "Varian" is a typo. should be "Variant".

I like your comments and will spend sometime on them and testing. Thanks so
much and I appreciate your tips.

Richard

Check the references (from the Tools menu in the VBA editor, choose
References). A missing or mis-matched reference is often the problem when
something works in one version but not in another.

The following code executed without error for me. I had to change a few
things in order to test it with my test data and without the missing code.
Comments below.

If you still can't get it to work after this, try describing what happens
when the code runs. Is there an error message?

'The ADODB.Connection is not used in the code posted.
Public Sub Oletest() '(cnn As ADODB.Connection)

Dim SqlQuery As String
Dim RcdSet As DAO.Recordset

'You were missing the 't' from 'Variant' but
'presumably that was just a typo in the
'newsgroup post.
Dim ppArray() As Variant

ReDim ppArray(1 To 10, 1 To 2)

'This wasn't in the posted code. Make sure you
'are assigning a valid value to arrayVal before
'using it.
Dim arrayVal As Variant
arrayVal = 1

'..

'Changed this so I could test with my test data ...
SqlQuery = "SELECT * FROM Assets"

Set RcdSet = CurrentDb.OpenRecordset(SqlQuery)
'If statement ....
' ...
' Do Until statement ...
' ...

'Changed field name to one that exists in my test data.
ppArray(arrayVal, 1) = RcdSet![Description]

' ...
' Loop
'End If
End Sub

I have an Access database that was designed with Office1995 version. It
has

[quoted text clipped - 28 lines]

in
those array statements). Anybody knows about it? Thanks. Appreciate it.