Array size limits?

[mferrisi]

I create an array that needs to be (1 To 1895, 1 To 5)
However, when I run the query, my array becomes (1 To 837, 1 To 5)

When I ran it with the query as
querystring = "SELECT the_name FROM THEDATABASE.THETABLENAME"
and
querystring = "SELECT the_name, the_id FROM THEDATABASE.THETABLENAME"
my arrays came back as (1 To 1895, 1 To 1) and ((1 To 1895, 1 To 2)

With a third component, e.g.
querystring = "SELECT the_name, the_id, the_period FROM THEDATABASE.THETABLENAME"
my array came back (1 To 1362, 1 To 3)

and with all five, (1 To 837, 1 To 5)

Is this a memory issue, and is there a way to create a 2000 X 5 array?

Thank you,

Matt

[Oorang]

Are you running the query via ADO or some other method?

[Charlize]

Array of 2000 rows by 5 columns.[VBA]Sub create_array()
Dim v_array() As String
Dim v_row As Long
Dim v_column As Long
Dim result As Variant
Dim v_show As Variant
ReDim Preserve v_array(2000, 5)
For v_row = 0 To 1999
For v_column = 0 To 4
v_array(v_row, v_column) = "Row " & v_row & " - Column " & v_column
Next v_column
Next v_row
result = Application.InputBox("Give no from 0 to 1999", _
"Show values in array ...", Type:=1)
v_show = "Item : " & result & " - Data" & vbCrLf
For v_column = 0 To 4
v_show = v_show & v_array(result, v_column) & vbCrLf
Next v_column
MsgBox v_show
End Sub[/VBA]Charlize

[Oorang]

Hi Charlize,
A couple things:
1.) In the above code, things are going to go horribly wrong unless you use "Option Base 1"
2.) I don't think it's an array or memory issue I think it's an issue with what the method you are using to return a recordset. This is why I asked how you were performing your query. Would you mind posting the code you are using to perform the query?

[Charlize]

Hi Charlize,
A couple things:
1.) In the above code, things are going to go horribly wrong unless you use "Option Base 1"

I thought that when you don't specify this, it will default to 0 for an array (not a collection).

2.) I don't think it's an array or memory issue I think it's an issue with what the method you are using to return a recordset. This is why I asked how you were performing your query. Would you mind posting the code you are using to perform the query?

Haven't got much time now. Will play with this one at home using an access database.

Charlize

[Oorang]

Erm right but you dimmed it to 2000 (0 to 2000) then looped from 0 to 1999 (So you have an unused element set). Then the first thing your loop adds is "Row0". There is no row 0 :-D

[Charlize]

Erm right but you dimmed it to 2000 (0 to 2000) then looped from 0 to 1999 (So you have an unused element set). Then the first thing your loop adds is "Row0". There is no row 0 :-D

Well I thought it were 2000 rows by 5 columns (my mistake). v_row = 0 is the placeholder for the position in the array, starting at 0. The added value is off course normally the first item in the recordset of the database until the end of the file and not the row = 0.

Basically I would first count the records in the returned recordset after the query, then create an array with that number and fill the array with the records that match the query. Or something like that ...

Charlize

[Oorang]

lol Sorry wasn't trying to nitpck Because just adding base 1 would make it function correctly I originally thought you wrote it using Base 1 and just didn't post it.

mferrisi> Would you mind posting the code you are using to perform the query?

[Charlize]

A little tryout by me with acces database and two fields. Don't choose 0 at the question (array starts at 1).[vba]Option Explicit
Option Base 1
Sub Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library
'This one works
Dim conn As New Connection
Dim rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&
Dim v_array() As String
Dim v_message As String
Dim result As Variant

'worksheet where we put data of table
Set ws = ThisWorkbook.Worksheets("Data")
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _
"Data Source=" & ThisWorkbook.Path + "\db1.mdb;"
'fields data, count in order of data, count
sql = "SELECT Data, Count " & _
"FROM Table1 ORDER BY Data, Count"
'do the query
rec.Open sql, conn
'loop selection to determine no of records
While Not rec.EOF
i = i + 1
rec.MoveNext
Wend
'go back to first to fill array
rec.MoveFirst
ReDim Preserve v_array(i, 2)
i = 0
While Not rec.EOF
i = i + 1
ws.[a1].Cells(i) = rec!Data + ", " + Str(rec!Count)
v_array(i, 1) = rec!Data
v_array(i, 2) = rec!Count
rec.MoveNext
Wend
'close connection
rec.Close: conn.Close
result = Application.InputBox("Give recordno from 1 to " & i & " :", Type:=1)
v_message = "Result for record no : " & result & vbCrLf
v_message = v_message & v_array(i, 1) & " - " & v_array(i, 2)
MsgBox v_message, vbInformation
End Sub
[/vba]Charlize