Getrows method not working

[prabha_friend]

Sub Test()
Dim myrset As Recordset
Dim temp As Variant
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
temp = myrset.GetRows(myrset.RecordCount)
MsgBox UBound(temp)
End Sub

I also tried with including the patrol (myrset.movelast and myrset.movefirst) before accessing the recordcount. Still its giving the UBound 0. But Recordcount is 2.

 

[pr2-eugin]

If you wish to get the record count you simply use the RecordSet.Recordcount method, you do not have to mess around with GetRows function. A few suggestions to code,

Sub Test()
    Dim myrset As DAO.Recordset
   [COLOR=Green] 'Always declare the right library to make sure it is unambiguous.[/COLOR]
    Dim reCount As Long
    [COLOR=Green]'Try to declare variables as they are. Long if it is a number, String if it is Text etc.[/COLOR]
    
    Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
    [COLOR=Green]'Optional, but to get the right count you should do,[/COLOR]
    If myrset.RecordCount <> 0 Then
        [B]myrset.MoveLast[/B]
        myrset.MoveFirst
        reCount = myrset.RecordCount
    Else
        reCount = 0
    End If
    
    MsgBox reCount
End Sub

NOTE: On a big data set, the MoveLast could slow down your code. However to be 100% sure of the record count, it is advisable to use MoveLast.

PS: Please use Code Tags when posting VBA Code

 

[vbaInet]

By the way, you do realise that GetRows returns a 2-dimensional array right? You also need to specify the dimension you're after unless it will assume the first dimension.

And modify your recordset to be a dbSnapshot.

 

[vbaInet]

Just a quick edit of code:

Sub Test()
    Dim myrset As DAO.Recordset
    dim mycount as long
    dim temp as variant
    
    mycount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays;", dbOpenSnapshot, dbReadOnly).Fields(0)
    Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
    
    temp = myrset.GetRows
    Msgbox Ubound(temp, 2)
End Sub

Just typed this so watch for typos.

 

[prabha_friend]

Paul. Actually I need that recordset to be converted into an array, Because I want to use the Networkdays function in the excel application using the excel.application.worksheetfunction.NetworkDays(Arg1, Arg2, Arg3)

Note:
Arg3 - Optional Variant Holidays - an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

 

[prabha_friend]

Msgbox Ubound(temp, 2)

Still its giving the 0 as Message...

 

[vbaInet]

What code did you try?

 

[vbaInet]

And it's Ubound(temp,1) for the first boundary and Ubound(temp, 2) for the second. So try both.

I forgot to include the row count:

temp = myrset.GetRows([COLOR="Red"]mycount[/COLOR])

 

[prabha_friend]

And it's Ubound(temp,1) for the first boundary and Ubound(temp, 2) for the second. So try both.

For Both Dimensions its returing 0 only

I am feeling bit strange on calling you as vbaInet. You won't disclose your name?

 

[vbaInet]

Did you see my last post?

 

[prabha_friend]

Did you see my last post?

Yeah I have tested including the recordcount only.

 

[vbaInet]

Let me see the full code you tested.

 

[pr2-eugin]

Prabha, try this.

Public Sub PrabhaTest()
    Dim myrset As DAO.Recordset
    Dim reCount As Long
    Dim temp As Variant
    
    Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
[B]    myrset.MoveLast
    myrset.MoveFirst[/B]
    reCount = myrset.RecordCount
    temp = myrset.GetRows(reCount)
    
    MsgBox "Total Records - " & reCount & vbCrLf & vbCrLf & _
            "Total Number of Columns - " & UBound(temp, 1)[B] + 1[/B] & vbCrLf & _
            "Total Number of Rows - " & UBound(temp, 2) [B]+ 1[/B]
End Sub

 

[prabha_friend]

Let me see the full code you tested.

Hi Your code works perfectly...
Sorry I did not included this code properly before. Sorry for the negligence

mycount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays;", dbOpenSnapshot, dbReadOnly).Fields(0)

If you have time please explain on why its not working when I tried with mycount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays;")

Thank you...

 

[prabha_friend]

Paul, Your code is also giving the right values. Sorry I was confused a bit.

 

[pr2-eugin]

No, I was only trying a fool proof method. vbaInet's method is more efficient. It only gets the count, and uses that to get the rows from the table. You should go with that. I only made a point that the RecordCount is not always reliable as mentioned in my first comment. Glad you have it working.

 

[vbaInet]

If you have time please explain on why its not working when I tried with mycount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays;")

Paul already mentioned it in his code, to get the record count from a recordset, you need to move down and move back up. With my code I get the record count from a query and return the value to the recordset so no need to move down and up.

Edit: I'm a bit slow today Paul.

 

[pr2-eugin]

Edit: I'm a bit slow today Paul.

It's alright vbaInet, I will let you off today. Ha Ha !

 

[vbaInet]

Thank you kind sir

 

[prabha_friend]

How to pass only a single dimension of an array?

Hi all,
Thank you for your answers. Now the getrows is working fine at my end. Now I have a different problem at hand:

Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox myrset.RecordCount
MsgBox excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, Array(#8/15/2014#, #8/29/2014#))
MsgBox excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, excel.Application.WorksheetFunction.Index(myrset.GetRows(myrset.RecordCount), 0, 1))
End Sub

Recordcount is coming fine
Manual Array is also giving the right result
But the Index method is giving the wrong value. Why?

(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)