Team,
I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has various
datatypes how can you get an array to handle the various data types or am i
missing the point with the migration from queries to excel.

My form for this particular problem is to bring in operational data from a
access database and complete vba routines on the data to quickly crunch the
outputs, produce charts, summary diagrams etc.

Thanks if possible in advance
Shane

Set cn = New ADODB.Connection
cn.ConnectionString = Cs
cn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = Query
cmd.CommandType = adCmdText

Set rs = New ADODB.Recordset
Set rs.Source = cmd

rs.Open

Dim i As Long
Dim j As Long
Dim Data(10000, 100) As Integer



Cells.Select
Selection.ClearContents

i = 1
Do While Not rs.EOF
j = 1
For Each f In rs.Fields
Data(i, j) = f.Value
Cells(i, j) = Data(i, j)
Debug.Print Data(i, j)
j = j + 1
Next f
rs.MoveNext
i = i + 1

Loop

Hi Shane

Just a though for you to consider. Why not populate the
worksheet with the segregated data from a recordedset
and start your manipulation and analysis in excel.

Good Luck
TK


"Shane King" wrote:

Team,
I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has various
datatypes how can you get an array to handle the various data types or am i
missing the point with the migration from queries to excel.

My form for this particular problem is to bring in operational data from a
access database and complete vba routines on the data to quickly crunch the
outputs, produce charts, summary diagrams etc.

Thanks if possible in advance
Shane

Set cn = New ADODB.Connection
cn.ConnectionString = Cs
cn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = Query
cmd.CommandType = adCmdText

Set rs = New ADODB.Recordset
Set rs.Source = cmd

rs.Open

Dim i As Long
Dim j As Long
Dim Data(10000, 100) As Integer



Cells.Select
Selection.ClearContents

i = 1
Do While Not rs.EOF
j = 1
For Each f In rs.Fields
Data(i, j) = f.Value
Cells(i, j) = Data(i, j)
Debug.Print Data(i, j)
j = j + 1
Next f
rs.MoveNext
i = i + 1

Loop

Thanks for the advice but i have some 600,000 rows of data to manipulate..
So this is to large to populate excel..

Thanks S
"TK" wrote in message
...
Hi Shane

Just a though for you to consider. Why not populate the
worksheet with the segregated data from a recordedset
and start your manipulation and analysis in excel.

Good Luck
TK


"Shane King" wrote:

Team,
I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has
various
datatypes how can you get an array to handle the various data types or
am i
missing the point with the migration from queries to excel.

My form for this particular problem is to bring in operational data from
a
access database and complete vba routines on the data to quickly crunch
the
outputs, produce charts, summary diagrams etc.

Thanks if possible in advance
Shane

Set cn = New ADODB.Connection
cn.ConnectionString = Cs
cn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = Query
cmd.CommandType = adCmdText

Set rs = New ADODB.Recordset
Set rs.Source = cmd

rs.Open

Dim i As Long
Dim j As Long
Dim Data(10000, 100) As Integer



Cells.Select
Selection.ClearContents

i = 1
Do While Not rs.EOF
j = 1
For Each f In rs.Fields
Data(i, j) = f.Value
Cells(i, j) = Data(i, j)
Debug.Print Data(i, j)
j = j + 1
Next f
rs.MoveNext
i = i + 1

Loop

Hi Shane

Well there are numerous ways to process data.
Jamie suggested a couple and here is yet another.
Build the (selection, action or whatever)
Query in Access. Then call the Query from Excel
for presentation.

Good Luck
TK

"Shane King" wrote:

Thanks for the advice but i have some 600,000 rows of data to manipulate..
So this is to large to populate excel..

Thanks S
"TK" wrote in message
...
Hi Shane

Just a though for you to consider. Why not populate the
worksheet with the segregated data from a recordedset
and start your manipulation and analysis in excel.

Good Luck
TK


"Shane King" wrote:

Team,
I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has
various
datatypes how can you get an array to handle the various data types or
am i
missing the point with the migration from queries to excel.

My form for this particular problem is to bring in operational data from
a
access database and complete vba routines on the data to quickly crunch
the
outputs, produce charts, summary diagrams etc.

Thanks if possible in advance
Shane

Set cn = New ADODB.Connection
cn.ConnectionString = Cs
cn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = Query
cmd.CommandType = adCmdText

Set rs = New ADODB.Recordset
Set rs.Source = cmd

rs.Open

Dim i As Long
Dim j As Long
Dim Data(10000, 100) As Integer



Cells.Select
Selection.ClearContents

i = 1
Do While Not rs.EOF
j = 1
For Each f In rs.Fields
Data(i, j) = f.Value
Cells(i, j) = Data(i, j)
Debug.Print Data(i, j)
j = j + 1
Next f
rs.MoveNext
i = i + 1

Loop

"Shane King" wrote in message ...

i have some 600,000 rows of data to manipulate..
to this is to large to populate excel

Can you do any manipulation using SQL to reduce the number of rows
selected? 600K rows is a lot to work with in memory.

Jamie.

--

Hi Jamie:
Always nice to here from you.

€œCan you do any manipulation using SQL to reduce the number of rows€�

Well a couple ideas come to mind i.e.
Where
Distinct
Group
Join
Having
Ordered BY
Top
Sub queries


TK

"Jamie Collins" wrote:

"Shane King" wrote in message ...

i have some 600,000 rows of data to manipulate..
to this is to large to populate excel

Can you do any manipulation using SQL to reduce the number of rows
selected? 600K rows is a lot to work with in memory.

Jamie.

--

"TK" wrote ...

Hi Jamie:
Always nice to here from you.

“Can you do any manipulation using SQL to reduce the number of rowsâ€�

Well a couple ideas come to mind i.e.
Where
Distinct
Group
Join
Having
Ordered BY
Top
Sub queries


Perhaps I should have said:

The OP should review their SQL in case it is possible to reduce the
number of rows being returned to Excel. If the OP posted their SQL
code, example data and desired results, I'm sure they would get some
useful suggestions here.

Jamie.

--

"Shane King" wrote ...

I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has various
datatypes how can you get an array to handle the various data types or am i
missing the point with the migration from queries to excel.

Some points you may be missing:

You can use the GetRows to return a zero-based variant array based on
the recorset, for which the dimensions will be dynamically sized for
you.
You can use Excel's Application.Transpose to manipulate the GetRows
results array into Excel's (rows,columns) format.
You can set an appropriately sized Range object's Value property to
equal the array transposed as above (Excel takes care of the base 0 to
base 1 conversion).
As an alternative to using an array, you can use Excel's
CopyFromRecordset to write the data to a Range.

For details, see:

http://support.microsoft.com/default...b;en-us;246335

BTW if you are changing values while looping through the recordset
which would prevent you from using the above approaches, you may be
achieve the same while fetching the data (i.e. rather than post
processing the data) by changing your SQL code and/or using data
shaping.

Jamie.

--

Jamie,
Many thanks i have anumber of hints to go our try through
Thanks for your time.
Shane King
"Jamie Collins" wrote in message
om...
"Shane King" wrote ...

I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has
various
datatypes how can you get an array to handle the various data types or
am i
missing the point with the migration from queries to excel.

Some points you may be missing:

You can use the GetRows to return a zero-based variant array based on
the recorset, for which the dimensions will be dynamically sized for
you.
You can use Excel's Application.Transpose to manipulate the GetRows
results array into Excel's (rows,columns) format.
You can set an appropriately sized Range object's Value property to
equal the array transposed as above (Excel takes care of the base 0 to
base 1 conversion).
As an alternative to using an array, you can use Excel's
CopyFromRecordset to write the data to a Range.

For details, see:

http://support.microsoft.com/default...b;en-us;246335

BTW if you are changing values while looping through the recordset
which would prevent you from using the above approaches, you may be
achieve the same while fetching the data (i.e. rather than post
processing the data) by changing your SQL code and/or using data
shaping.

Jamie.

--