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

"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.

--

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.

--

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.

--

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.

--

Jamie:

I agree and that was my initial suggestion but the O.P.
immediately dismissed it stating they were working with
600,000 rows and I dont know what an Access row is.
Obviously we dont want to start the selection process in
a program thats whole purpose in life is to store and
analysis data.

Good Luck
TK



"Jamie Collins" wrote:

"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.

--

"TK" wrote ...

Obviously we don’t want to start the selection process in
a program that’s whole purpose in life is to store and
analysis data.

In general terms, I agree i.e. pass parameters from the client to a
stored procedure on the database server, process on the server side
and sent the results set back to the client. However, because the
source is a Jet (MS Access) database and the target (i.e. Excel) is
also a Jet datasource, it may be possible to take advantage of a few
tricks e.g. creating a JOIN between data in the .xls and .mdb can save
a lot of processing time and trouble parsing a delimited string of key
values from the client.

I don’t know what an Access row is

What the theoretical SQL language community calls a 'row', the MS
Access community calls a 'record'.

Jamie.

--

Hi Jamie:

"Jamie Collins" wrote:

because the
source is a Jet (MS Access) database and the target (i.e. Excel) is
also a Jet datasource, it may be possible to take advantage of a few
tricks e.g. creating a JOIN between data in the .xls and .mdb can save
a lot of processing time and trouble parsing a delimited string of key
values from the client.

Jamie.


Not to say it has not been done, I have never seen a JOIN written like that
maybe you could share an example.

TK

"TK" wrote ...

creating a JOIN between data in the .xls and .mdb can save
a lot of processing time and trouble parsing a delimited string of key
values from the client

Not to say it has not been done, I have never seen a JOIN written like that
maybe you could share an example.

Of course. Try the following code in a standard module inside a new/blank workbook:

Option Explicit

Sub Test()

Dim Cat As Object
Dim rs As Object
Dim strConJet As String
Dim strConXL As String
Dim strSql1 As String
Dim strSql2 As String
Dim strSql3 As String
Dim lngCounter As Long
Dim oTarget As Excel.Range

' Amend the following constants to suit
Const PATH As String = "" & _
"C:\"

Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

Const FILENAME_XL As String = "" & _
"New_XL_DB.xls"

' Do not amend following constants
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME"

Const CONN_STRING_XL As String = "" & _
"[Excel 8.0;HDR=YES;" & _
"Database=<PATH<FILENAME]"

' Build connection strings
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH", PATH)
strConJet = Replace(strConJet, "<FILENAME", FILENAME_JET)

strConXL = CONN_STRING_XL
strConXL = Replace(strConXL, "<PATH", PATH)
strConXL = Replace(strConXL, "<FILENAME", FILENAME_XL)

' Build sql statements
strSql1 = ""
strSql1 = strSql1 & "CREATE TABLE EmployeeDetails ("
strSql1 = strSql1 & " employee_ID CHAR(10) NOT NULL,"
strSql1 = strSql1 & " lname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " fname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " mname VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,"
strSql1 = strSql1 & " CONSTRAINT pk__ee PRIMARY KEY (employee_ID),"
strSql1 = strSql1 & " CONSTRAINT ch__ee_id_alphanum CHECK "
strSql1 = strSql1 & "(employee_ID LIKE '[0-9][0-9][0-9][0-9][0-9]"
strSql1 = strSql1 & "[0-9][0-9][0-9][0-9][0-9]'),"
strSql1 = strSql1 & " CONSTRAINT ch__ee_id_CheckDigit CHECK (TRUE "
strSql1 = strSql1 & "= (CLNG(RIGHT(employee_ID,1))"
strSql1 = strSql1 & "=(CDBL(LEFT(employee_ID,9)) MOD 11)))"
strSql1 = strSql1 & ");"

' Note: Excel has weak data typing and no constrains!
strSql2 = ""
strSql2 = strSql2 & "CREATE TABLE " & strConXL & ".Earnings ("
strSql2 = strSql2 & " employee_ID VARCHAR(255) NULL,"
strSql2 = strSql2 & " earnings_amt CURRENCY NULL,"
strSql2 = strSql2 & " effective DATETIME NULL"
strSql2 = strSql2 & ");"

strSql3 = ""
strSql3 = strSql3 & "SELECT EE.lname AS Employee, EN.effective"
strSql3 = strSql3 & " AS [From], EN.earnings_amt AS Earnings FROM"
strSql3 = strSql3 & " EmployeeDetails EE INNER JOIN " & strConXL
strSql3 = strSql3 & ".Earnings EN ON EE.employee_ID = EN.employee_ID"
strSql3 = strSql3 & " ORDER BY EE.lname, EN.effective DESC;"

' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet

' 'inherit' the connection
With Cat.ActiveConnection

' Create tables
.Execute strSql1
.Execute strSql2

' Create some sample data
.Execute "" & _
"INSERT INTO EmployeeDetails (employee_ID, lname, fname)" & _
" VALUES ('4548181814', 'Katewudes', 'A')"
.Execute "" & _
"INSERT INTO EmployeeDetails (employee_ID, lname, fname)" & _
" VALUES ('7055727558', 'Tinatotac', 'B')"
.Execute "" & _
"INSERT INTO EmployeeDetails (employee_ID, lname, fname)" & _
" VALUES ('2300007864', 'Norarules', 'C')"
.Execute "" & _
"INSERT INTO EmployeeDetails (employee_ID, lname, fname)" & _
" VALUES ('9377223119', 'Helenahen', 'D')"

.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('4548181814', 14000, '2000-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('4548181814', 24000, '2001-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('4548181814', 40000, '2004-10-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('7055727558', 55000, '2001-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('7055727558', 65000, '2002-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('7055727558', 71000, '2003-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('2300007864', 79000, '2003-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('2300007864', 83000, '2004-01-01');"
.Execute "" & _
"INSERT INTO " & strConXL & ".Earnings" & _
"(employee_ID, earnings_amt, effective)" & _
" VALUES ('9377223119', 95000, '2004-02-01');"

' Open recordset
Set rs = .Execute(strSql3)

End With

' Copy data to ThisWorkbook
With rs

Set oTarget = ThisWorkbook.Worksheets(1) _
.Range("A1")
For lngCounter = 1 To .fields.Count
oTarget(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next

End With

With oTarget
.Cells(2, 1).CopyFromRecordset rs
.Worksheet.UsedRange.EntireColumn.AutoFit
End With

End Sub


Jamie.

--

"Jamie Collins" wrote:

"TK" wrote ...

creating a JOIN between data in the .xls and .mdb can save
a lot of processing time and trouble parsing a delimited string of key
values from the client

Not to say it has not been done, I have never seen a JOIN written like that
maybe you could share an example.

Of course. Try the following code in a standard module inside a new/blank workbook:


Jamie:

Well the example worked well, but really Jamie
create two DBs in memory and then use a join to present the
data. You have got to step back from the coffee pot.

I thought you were indicating there is a way to use a join between
worksheet data (none db) and a db, obviously you were not. .

Anyway, I have to stand by my original argument that to process data
at the server and return the narrowest cursor needed is usually the
fastest and probably the most stable.


Good Luck
TK

"TK" wrote ...

Well the example worked well, but really Jamie
create two DBs in memory and then use a join to present the
data. You have got to step back from the coffee pot.

You have misread the code. It creates a .mdb file on disk, then an
..xls file on disk. Notice from the SQL DDL

CREATE TABLE [Excel 8.0;HDR=YES;Database=C:\New_XL_DB.xls].Earnings
....

that the second file created is an Excel workbook. Then it creates a
join between them. After it has run, check the file locations to see
if the .mdb and .xls have been persisted on disk.

I dunno, some people you just can't please <g. I've done all the work
of creating the files and then you claim I've done some 'in memory'
sleight of hand!

I thought you were indicating there is a way to use a join between
worksheet data (none db) and a db, obviously you were not. .

I'll be clear: yes, I am saying (and demonstrating) that it is
possible to create a JOIN between worksheet data and an .mdb database.

Perhaps you aren't a code monkey and I lost you a bit <g. Here's a
"users" version:

In Excel, create a new/blank workbook.
In cell A1 on Sheet1, enter the value 55
In cell B1 on Sheet1, enter the text: 'This is Excel'.
Save the workbook as C:\Temp.xls.
Close the workbook.
In MS Access, create a new/blank .mdb.
From the menu, choose: Insert, table, Design View, OK.
Add the flowing columns:
Field Name=MyNumberCol, Data Type=Number (Long Integer);
MyTextCol=MyTextCol, Data Type=Text, Field Size=50.
Save the table as Table1 (just say No to the message about a primary
key).
Open the table and enter the following values:
MyNumberCol=55
MyTextCol=This is MS Access/Jet.
Move to the newly-inserted line (the next line down) to ensure the
change is written to disk.
Close the table.
From the menu, choose: Insert, Query. Design View, OK, cancel the Add
tables dialog (something like that, anyhow; I'm doing this from
memory).
When the query builder tool thing appears - sorry, I don't know how
to use its clickly-click tools so I'll briefly revert to code -
choose: View, SQL.
In the SQL window, enter the following:

SELECT T1.MyTextCol, T2.F2
FROM Table1 AS T1
INNER JOIN
[Excel 8.0;HDR=NO;Database=C:\Temp.xls;].[Sheet1$]
AS T2 ON T1.MyNumberCol = T2.F1

Hit the toolbar button with the exclamation mark (looks a bit like a
footprint).
The result should show the values from and MS Access/Jet and Excel
respectively, joined on the common key value.

If you are still a doubter, post some data of your own and I'll write
the SQL for you. Otherwise mine's a double ristretto, cheers.

Jamie.

--

Jamie:

You have misread the code. It creates a .mdb file on disk, then an
..xls file on disk. Notice from the SQL DDL

Sorry about €œMy Confusion€� I mistyped the path and could not find the
created files, late watching election results ect.

I'll be clear: yes, I am saying (and demonstrating) that it is
possible to create a JOIN between worksheet data and an .mdb database.


So I conclude from your post: the ws data only has to be entered in a way
that you can create a search condition


TK

"TK" wrote ...

I conclude from your post: the ws data only has to be entered in a way
that you can create a search condition

This approach isn't restricted to Jet data sources; odbc sources may
be used. This example uses any Jet connection (e.g. to a non-existent
workbook) to join northwind (Jet) and pubs (SQL Server) tables:

SELECT
MSJet.ProductName, MSSQL.job_desc
FROM
[Database=C:\Program Files\Microsoft Visual
Studio\VB98\NWIND.mdb;].Products
AS MSJet
INNER JOIN
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd= ***;].jobs
AS MSSQL
ON MSJet.ProductID = MSSQL.job_id
;

Jamie.

--