Reading Access tables using INNER JOIN

[Rasm]

Below is my code to read records from two acces tables using VBA in xl2007 - rather than reading specific fields - I use the 'FROM *' - so I figure this is much faster than having a much longer and complicated SQL statement - hower my records sets can be very large (Recordcount>20K and FieldCount>30) - should I be concerned about memory usage - or is the memory released by a RstRcd.Close line in my code.

I would be interested in comments of using a complicated SQL statement with WHERE and >= clauses and so on Vs the memory concern by just reading the lot.

Second question:
I immdeiately copy the data into a temporary sheet - so I can use the Sort function in Excel - I know of no way to easily sort multidimentional arrays - If anybody has a good sort routine for sorting arrays I would appreciate a copy - I looked at Chip Pearson's code example - but it only handles single dimentional arrays.

Third Question:
The ADO connection has a memory leak - was covered in earlier post here - any suggestion what to use as alternative - should I use an ODBC connection or similar - This is all new to me - so I am not sure if I am going to have memory issues.



Set adoConn = CreateObject("ADODB.Connection")
Set RstRcd = CreateObject("ADODB.Recordset")
On Error Resume Next
With adoConn
Err.Clear
.provider = "Microsoft.Jet.Oledb.4.0"
.Properties("Jet OLEDB:Database Password") = DB_PW
.Open "Data Source=" & strDBPath
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Cannot find database - select another"
Exit Sub
End If
End With
'***** Reads the SampleHeader/SampleData Tables - using relationship
strTable = "SampleHeader"
Application.StatusBar = "Reading Access table --- 'SampleHeader'"
Astr = "SELECT * FROM (SampleHeader INNER JOIN SampleData ON SampleHeader.SampleID = SampleData.SampleID) INNER JOIN Parameter ON SampleData.ConstituentID = Parameter.ParameterID"
RstRcd.Close
RstRcd.Open Astr, adoConn, 3, 3
'******* Copies the entire dataser into worksheet "TempData" -- in ThisWorkBook
ThisWorkbook.Worksheets("TempData").UsedRange.Clear
For ii = 1 To RstRcd.fields.Count
ThisWorkbook.Worksheets("TempData").Cells(1, ii).Value = RstRcd.fields(ii - 1).Name
Next ii
ThisWorkbook.Worksheets("TempData").Cells(2, 1).CopyFromRecordset RstRcd

[Excel Fox]

Rasm

For your first question,

Instead of pulling a set of records that do not have any WHERE clause or other conditionals, it would be better to restrict your data by adding the required conditions you need using WHERE and HAVING. It might increase the computing a little more, but the resultant data set would at least be smaller than a SELECT *

2. Instead of sorting it after pulling in Excel, why don't you sort using the ORDER BY command in SQL itself. That will save you the time required to sort in Excel. Of course ORDER BY will add that extra computation need, but isn't it better to use a database command on a large data set, than to use an Excel command.

For your third query, I believe ODBC would be more efficient, but that is just a speculation that I am not sure about.

[Rasm]

Fox

First thanks for answer --- In respect to #1 - I will try that - I been messing with the Query Wizard in Access and is starting to get it - Item #2 I have to sort the data multiple times as I am moving Rows alone into Columns & rows ( due to the Primary key/Foreign Key relationship. Item #3 - it appear that I am not having a memory leak - at least I have not managed to crash the program yet. So I will stick with the ADO for now - why fix it if it is not broke.

Rasm