Getrows method not working (1 Viewer)

P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
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

pr2-eugin

Super Moderator
Local time
Today, 22:15
Joined
Nov 30, 2011
Messages
8,494
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,
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
 
V

vbaInet

AWF VIP
Local time
Today, 22:15
Joined
Jan 22, 2010
Messages
26,374
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.
 
V

vbaInet

AWF VIP
Local time
Today, 22:15
Joined
Jan 22, 2010
Messages
26,374
Just a quick edit of code:
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.
 
P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
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.
 
V

vbaInet

AWF VIP
Local time
Today, 22:15
Joined
Jan 22, 2010
Messages
26,374
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:
Code: 
temp = myrset.GetRows([COLOR="Red"]mycount[/COLOR])
 
Last edited:
P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
vbaInet said:
And it's Ubound(temp,1) for the first boundary and Ubound(temp, 2) for the second. So try both.
Click to expand...
For Both Dimensions its returing 0 only :(

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

pr2-eugin

Super Moderator
Local time
Today, 22:15
Joined
Nov 30, 2011
Messages
8,494
Prabha, try this.
Code: 
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
 
P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
vbaInet said:
Let me see the full code you tested.
Click to expand...
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...
 
P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
Paul, Your code is also giving the right values. Sorry I was confused a bit.
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 22:15
Joined
Nov 30, 2011
Messages
8,494
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.
 
V

vbaInet

AWF VIP
Local time
Today, 22:15
Joined
Jan 22, 2010
Messages
26,374
prabha_friend said:
If you have time please explain on why its not working when I tried with mycount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays;")
Click to expand...
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.
 
P

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:45
Joined
Mar 22, 2009
Messages
783
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#)
 
Last edited:
You must log in or register to reply here.

Similar threads

B
Overlapping dates with some null data
Replies
7
Views
394
bilakos93
B
J_Fo
Solved Add record if not found, still adding record
Replies
6
Views
381
J_Fo
J_Fo
oxicottin
How to stop form from closing
Replies
7
Views
732
Pat Hartman
P
B
VBA problem relating to SQL linked table
Replies
6
Views
534
CJ_London
C
M
Multiple attachments and a report in the same email
Replies
15
Views
938
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom