Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Type Mismatch Load Array from Recordset

    Hi all,

    Usaing Access 2007.

    I am trying to lead an array from a recordset using the .GetRows method of the recordset. But my code below returns a Type mismatch error at Debug.Print

    What am I doing wrong?

    thx
    w

    Code:
    Option Compare Database
    Option Explicit
    Option Base 1
    
    
    Sub GenFiles()
        '
        'Purpose:
        '1.) Clear part Key table
        '2.) Update part key table
        '3.) Load part keys into an array
        '4.) Loop array
        '4.) Pass part key to SQL
        '5.) Run SQL to query
        '6.) Export each query to Excel file
        '
        '
        'Date       Developer       Action                      Comments
        '---------------------------------------------------------------------------------
        '08/03/12   hds             Created
        
        'Initialize
         DoCmd.SetWarnings False
    
    
        'Load the part keys into an array
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim intArrayCount As Integer
         Dim varrpartKey() As Variant
         Dim i, j, k As Long
         Dim strTbl As String
    
         strTbl = "tbl_partKeys"
         Set db = CurrentDb()
         Set rs = db.OpenRecordset(strTbl)
         i = rs.RecordCount
         j = 1
         ReDim varrpartKey(1, 1 To i)
    
         With rs
            .MoveFirst
            Do Until .EOF
                varrpartKey(1, j) = .GetRows
                j = j + 1
            Loop
            .Close
            Set rs = Nothing
         End With
         
        'Test the array
         For k = i To 1 Step -1
            Debug.Print varrpartKey(1, k);
         Next k
         
        'Tidy up
         DoCmd.SetWarnings True
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    GetRows does not work like that because the array is dimentioned by the GetRows method every time you invoke it. If you want to retrieve n rows from a recordset, simply use (without a loop):
    Code:
    MyVariantVariable = MyRecordSet.GetRows(n)
    If you want to determine the value of n (i.e. the number of rows to retrieve) before using the GetRows method, use:
    Code:
    With MyRecordSet
        .MoveLast
        n = .RecordCount
        .MoveFirst
        MyVariantVariable = .GetRows(n) 
        .Close
    End With
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    Got it working, the final code is below
    Is there a better way to handle the final debug.print loop
    The current code does not seem to be the most efficient.

    thx
    w

    Code:
    Option Compare Database
    Option Explicit
    
    
    Sub GenFiles()
        '
        'Purpose:
        '1.) Clear Part Key table
        '2.) Update Part key table
        '3.) Load Part keys into an array
        '4.) Loop array
        '4.) Pass Part key to SQL
        '5.) Run SQL to query
        '6.) Export each query to Excel file
        '
        '
        'Date       Developer       Action                      Comments
        '---------------------------------------------------------------------------------
        '08/03/12   ws              Created
        
        'Initialize
         DoCmd.SetWarnings False
    
        'Clear the Part key table
         DoCmd.OpenQuery "qdel_tblPartKeys_Clear"
    
        'Append unique Part keys from the market map table to the partion key table
         DoCmd.OpenQuery "qapp_tblPartKeys"
    
        'Load the Part keys into an array
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim intArrayCount As Integer
         Dim varRecords As Variant
         Dim varrPartKeys() As Variant
         Dim i, j, k As Long
         Dim strTbl As String
    
         strTbl = "tbl_PartKeys"
         Set db = CurrentDb()
         Set rs = db.OpenRecordset("Select * from " & _
            strTbl, dbOpenSnapshot)
            
         With rs
            .MoveLast
            .MoveFirst
            i = rs.RecordCount
         End With
         
        '(Column, Row)
         varRecords = rs.GetRows(i)
         j = 0
         
    
        'Test
         Do While j <= i
            If j >= i Then
                Exit Do
            Else
                Debug.Print varRecords(0, j)
            End If
            j = j + 1
         Loop
         Debug.Print i
         
        'Tidy up
         DoCmd.SetWarnings True
    End Sub

Posting Permissions

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