Results 1 to 3 of 3

Thread: Reading Access tables using INNER JOIN

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Reading Access tables using INNER JOIN

    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.

    Code:
        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
    Last edited by Rasm; 02-02-2012 at 07:00 AM.
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Replies: 8
    Last Post: 06-08-2013, 01:24 PM
  2. Full Outer Join in MS Access
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-18-2013, 08:42 PM
  3. Deleting Records Using Join
    By MMishra in forum MS-Access Tips And Tricks
    Replies: 0
    Last Post: 04-24-2013, 04:06 PM
  4. VBA Code to create Pivot tables
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 08-05-2012, 02:41 AM
  5. Reading/Saving binary data
    By Rasm in forum Excel Help
    Replies: 3
    Last Post: 02-26-2012, 08:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •