Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the number of
records returned.
So one query can return a large number of records and the rs.GetRows goes
very fast, whereas another SQLStatement returns less records, but rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make much
difference.
One problem is that I only can see how many records rs.Open produces after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS

If you'll tell me what you are trying to do(big picture), I might be able to
give you some helpful info.
--
RMC,CPA

"RB Smissaert" wrote in message
...
Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the number of
records returned.
So one query can return a large number of records and the rs.GetRows goes
very fast, whereas another SQLStatement returns less records, but rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make much
difference.
One problem is that I only can see how many records rs.Open produces after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS

RB,

Without knowing anything about the system architecture
involved one thing you may try is:

rs.CursorLocation = adUseClient

This caches the recordset rows on your local machine
instead of the database server. If the database you are
connecting to is remote, this could save many roundtrips
to the server.

Another thing to try is:

rs.LockType = adLockOptimistic

This tells the provider (database) to not lock records
unless an Update is called. But you aren't updating
records since

rs.CursorType = adForwardOnly and that is not an
updatable recordset cursor type.

Setting rs.LockType = adLockReadOnly may cause the
provider to place read-only locks on each row of the
result set. This could have a performance impact
depending on whether the provider does row-level or table-
level locking and/or if there are other processes
attempting to operate on the same tables.

Good Luck,

pete...


-----Original Message-----
Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but
not on the number of
records returned.
So one query can return a large number of records and
the rs.GetRows goes
very fast, whereas another SQLStatement returns less
records, but rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it
doesn't make much
difference.
One problem is that I only can see how many records
rs.Open produces after
transferring to an array because rs.RecordCount doesn't
work.

Any advice here greatly appreciated.



RBS


.

Have you tried using vbs to read to a text file? Don't know why, but
the slowdown could be coming from Excel. This would be one way to
check.

On Sun, 10 Aug 2003 19:15:13 +0100, "RB Smissaert"
wrote:

Don't quite understand it but I solved the problem by changing the datatype
for the variable c to integer rather than byte.
How can this make the difference if c can only be byte data from 0 to 4?

Actually running the loop is slightly faster than using the GetRows method,
which seems strange to me as well.
Still both methods are way to slow, considering I can get the data with
IB_SQL in a few seconds, whereas with ADO we are talking about more than 7
minutes.

Any advice greatly appreciated.

RBS


"RB Smissaert" wrote in message
...
As GetRows is so slow I tried to work with just the RecordSet itself
rather
than transferring it to an array.
I therefor tried this loop:

Dim testarray(0 To 1000, 0 To 4) As String

If Not rs.EOF Then
Do While Not rs.EOF
For c = 0 To 4
testarray(n, c) = rs.Fields(c).Value
Next
n = n + 1
rs.MoveNext
Loop
End If

However this breaks down on the first cycle with the message:
Item cannot be found in the collection corresponding to the requested name
or ordinal.
This happens already when the counters n and c are still 0.
Strangely, when I do MsgBox rs.Fields(0) before running this loop it works
fine.
Must be overlooking something really simple here, but just can't see it.
Thanks for any advice.


RBS


"RB Smissaert" wrote in message
...
Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the
number
of
records returned.
So one query can return a large number of records and the rs.GetRows
goes
very fast, whereas another SQLStatement returns less records, but
rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make
much
difference.
One problem is that I only can see how many records rs.Open produces
after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS

Not quite sure what you mean, but this is the connection string:

EasySoftConn = "DSN=" & GetS6000DSN() & ";" & _
"UID=" & Username & _
";PWD=" & Password & _
";DB=" & GetIBPath() & _
";OLDMETADATA=1;"

RBS


"Random" <Random@nwhere wrote in message
...
Have you tried using vbs to read to a text file? Don't know why, but
the slowdown could be coming from Excel. This would be one way to
check.

On Sun, 10 Aug 2003 19:15:13 +0100, "RB Smissaert"
wrote:

Don't quite understand it but I solved the problem by changing the
datatype
for the variable c to integer rather than byte.
How can this make the difference if c can only be byte data from 0 to 4?

Actually running the loop is slightly faster than using the GetRows
method,
which seems strange to me as well.
Still both methods are way to slow, considering I can get the data with
IB_SQL in a few seconds, whereas with ADO we are talking about more than
7
minutes.

Any advice greatly appreciated.

RBS


"RB Smissaert" wrote in message
...
As GetRows is so slow I tried to work with just the RecordSet itself
rather
than transferring it to an array.
I therefor tried this loop:

Dim testarray(0 To 1000, 0 To 4) As String

If Not rs.EOF Then
Do While Not rs.EOF
For c = 0 To 4
testarray(n, c) = rs.Fields(c).Value
Next
n = n + 1
rs.MoveNext
Loop
End If

However this breaks down on the first cycle with the message:
Item cannot be found in the collection corresponding to the requested
name
or ordinal.
This happens already when the counters n and c are still 0.
Strangely, when I do MsgBox rs.Fields(0) before running this loop it
works
fine.
Must be overlooking something really simple here, but just can't see
it.
Thanks for any advice.


RBS


"RB Smissaert" wrote in message
...
Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the
number
of
records returned.
So one query can return a large number of records and the rs.GetRows
goes
very fast, whereas another SQLStatement returns less records, but
rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make
much
difference.
One problem is that I only can see how many records rs.Open produces
after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS