RecordCount Problem

[scott]

Below returns -1 for the record count when it should report a 1 for the Max
Date field when the table has data. I need the RecordCount property to
return the correct number of records because I need to test the table to see
if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Ken Snell [MVP]]

A recordset is not "filled" with all records until you actually access those
records. Thus, add a step to move to the last record before you get the
record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

 

[scott]

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.

A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you get
the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for the
Max Date field when the table has data. I need the RecordCount property
to return the correct number of records because I need to test the table
to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Ken Snell [MVP]]

I believe that ADO recordsets are "forward only" by default
(adOpenForwardOnly). Set the CursorType property to adOpenDynamic before you
open the recordset.


--

Ken Snell
<MS ACCESS MVP>

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.

A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you get
the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for the
Max Date field when the table has data. I need the RecordCount property
to return the correct number of records because I need to test the table
to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Brendan Reynolds]

If you *only* want to know whether the recordset returns records or not, you
can test the BOF and EOF properties. If both are True, the recordset doesn't
return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.

A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you get
the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for the
Max Date field when the table has data. I need the RecordCount property
to return the correct number of records because I need to test the table
to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[scott]

can you send an example of syntax? I read and tried a microsoft solution and
it not like the syntax of my dsn.

I believe that ADO recordsets are "forward only" by default
(adOpenForwardOnly). Set the CursorType property to adOpenDynamic before
you open the recordset.


--

Ken Snell
<MS ACCESS MVP>

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.

A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you get
the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for the
Max Date field when the table has data. I need the RecordCount property
to return the correct number of records because I need to test the
table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[scott]

can you send a full function with code like mine that did the job? I can't
find an example of using a dsn that referenced a front end file, but with
linked tables and front end local tables.

If you *only* want to know whether the recordset returns records or not,
you can test the BOF and EOF properties. If both are True, the recordset
doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.

A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you get
the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for the
Max Date field when the table has data. I need the RecordCount property
to return the correct number of records because I need to test the
table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Brendan Reynolds]

I just realized that we're getting side-tracked on the RecordCount question.
Your problem actually isn't a RecordCount problem at all. Your query will
*always* return one record, even if there are no records in the table - it
will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a date
entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I can't
find an example of using a dsn that referenced a front end file, but with
linked tables and front end local tables.

If you *only* want to know whether the recordset returns records or not,
you can test the BOF and EOF properties. If both are True, the recordset
doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.


A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you
get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for
the Max Date field when the table has data. I need the RecordCount
property to return the correct number of records because I need to
test the table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[scott]

thanks, i'll try in morning, but do you think a SELECT COUNT(*) statement
could be used to actually get a correct count of records? If it wasn't to
much trouble and just for learning sake, i'm curious if you could help me
think if there's another way that actually gives a correct count.

Maybe you could modify the current script to use the COUNT function and give
us a true count records test?

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all. Your
query will *always* return one record, even if there are no records in the
table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a date
entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.

If you *only* want to know whether the recordset returns records or not,
you can test the BOF and EOF properties. If both are True, the recordset
doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward " when
trying your method.


A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you
get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for
the Max Date field when the table has data. I need the RecordCount
property to return the correct number of records because I need to
test the table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Ken Snell [MVP]]

If all you want is a count, then use the DCount function instead of opening
your own recordset. It's explained in Help files.


--

Ken Snell
<MS ACCESS MVP>

thanks, i'll try in morning, but do you think a SELECT COUNT(*) statement
could be used to actually get a correct count of records? If it wasn't to
much trouble and just for learning sake, i'm curious if you could help me
think if there's another way that actually gives a correct count.

Maybe you could modify the current script to use the COUNT function and
give us a true count records test?

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all. Your
query will *always* return one record, even if there are no records in
the table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a
date entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
If you *only* want to know whether the recordset returns records or
not, you can test the BOF and EOF properties. If both are True, the
recordset doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward "
when trying your method.


A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you
get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for
the Max Date field when the table has data. I need the RecordCount
property to return the correct number of records because I need to
test the table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Brendan Reynolds]

Yes, you could use Count(*). The code would be very similar to the previous
example ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
MsgBox "The count is: " & .Fields("TheCount")
.Close
End With

End Sub

Ken's example using .MoveLast will also work as long as you specify the
necessary properties to get a fully scrollable recordset.

In your original example, though ("SELECT Max([DateField]) FROM TableName")
there will *always* be one and only one record, so there is no need to count
it.

--
Brendan Reynolds (MVP)

thanks, i'll try in morning, but do you think a SELECT COUNT(*) statement
could be used to actually get a correct count of records? If it wasn't to
much trouble and just for learning sake, i'm curious if you could help me
think if there's another way that actually gives a correct count.

Maybe you could modify the current script to use the COUNT function and
give us a true count records test?

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all. Your
query will *always* return one record, even if there are no records in
the table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a
date entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
If you *only* want to know whether the recordset returns records or
not, you can test the BOF and EOF properties. If both are True, the
recordset doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward "
when trying your method.


A recordset is not "filled" with all records until you actually access
those records. Thus, add a step to move to the last record before you
get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for
the Max Date field when the table has data. I need the RecordCount
property to return the correct number of records because I need to
test the table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[scott]

I figured there was a way to get around that shortcoming of ADO. What I
can't understand is why the developers of the ADO library left out such a
useful property. Thank you and everyone else who helped.

Yes, you could use Count(*). The code would be very similar to the
previous example ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
MsgBox "The count is: " & .Fields("TheCount")
.Close
End With

End Sub

Ken's example using .MoveLast will also work as long as you specify the
necessary properties to get a fully scrollable recordset.

In your original example, though ("SELECT Max([DateField]) FROM
TableName") there will *always* be one and only one record, so there is no
need to count it.

--
Brendan Reynolds (MVP)

thanks, i'll try in morning, but do you think a SELECT COUNT(*) statement
could be used to actually get a correct count of records? If it wasn't to
much trouble and just for learning sake, i'm curious if you could help me
think if there's another way that actually gives a correct count.

Maybe you could modify the current script to use the COUNT function and
give us a true count records test?

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all. Your
query will *always* return one record, even if there are no records in
the table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a
date entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
If you *only* want to know whether the recordset returns records or
not, you can test the BOF and EOF properties. If both are True, the
recordset doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward "
when trying your method.


A recordset is not "filled" with all records until you actually
access those records. Thus, add a step to move to the last record
before you get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1 for
the Max Date field when the table has data. I need the RecordCount
property to return the correct number of records because I need to
test the table to see if it actually has any records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Ken Snell [MVP]]

Sorry, but I'm not understanding... what "shortcoming" of ADO are you
needing to work around?

--

Ken Snell
<MS ACCESS MVP>

I figured there was a way to get around that shortcoming of ADO. What I
can't understand is why the developers of the ADO library left out such a
useful property. Thank you and everyone else who helped.

Yes, you could use Count(*). The code would be very similar to the
previous example ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
MsgBox "The count is: " & .Fields("TheCount")
.Close
End With

End Sub

Ken's example using .MoveLast will also work as long as you specify the
necessary properties to get a fully scrollable recordset.

In your original example, though ("SELECT Max([DateField]) FROM
TableName") there will *always* be one and only one record, so there is
no need to count it.

--
Brendan Reynolds (MVP)

thanks, i'll try in morning, but do you think a SELECT COUNT(*)
statement could be used to actually get a correct count of records? If
it wasn't to much trouble and just for learning sake, i'm curious if you
could help me think if there's another way that actually gives a correct
count.

Maybe you could modify the current script to use the COUNT function and
give us a true count records test?


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all.
Your query will *always* return one record, even if there are no
records in the table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a
date entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
If you *only* want to know whether the recordset returns records or
not, you can test the BOF and EOF properties. If both are True, the
recordset doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward "
when trying your method.


message A recordset is not "filled" with all records until you actually
access those records. Thus, add a step to move to the last record
before you get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1
for the Max Date field when the table has data. I need the
RecordCount property to return the correct number of records
because I need to test the table to see if it actually has any
records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

 

[Brendan Reynolds]

RecordCount works in ADO - see Ken's posts. It's just that ADO defaults to
using the type of recordset that requires the least amount of resources.
That's a forward-only, read-only recordset. Other types of ADO recordset are
available, which provide additional functionality (including backward
scrolling) at the cost of requiring more resources, you just have to specify
the CursorLocation, CursorType, and LockType properties.

--
Brendan Reynolds (MVP)

I figured there was a way to get around that shortcoming of ADO. What I
can't understand is why the developers of the ADO library left out such a
useful property. Thank you and everyone else who helped.

Yes, you could use Count(*). The code would be very similar to the
previous example ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
MsgBox "The count is: " & .Fields("TheCount")
.Close
End With

End Sub

Ken's example using .MoveLast will also work as long as you specify the
necessary properties to get a fully scrollable recordset.

In your original example, though ("SELECT Max([DateField]) FROM
TableName") there will *always* be one and only one record, so there is
no need to count it.

--
Brendan Reynolds (MVP)

thanks, i'll try in morning, but do you think a SELECT COUNT(*)
statement could be used to actually get a correct count of records? If
it wasn't to much trouble and just for learning sake, i'm curious if you
could help me think if there's another way that actually gives a correct
count.

Maybe you could modify the current script to use the COUNT function and
give us a true count records test?


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

I just realized that we're getting side-tracked on the RecordCount
question. Your problem actually isn't a RecordCount problem at all.
Your query will *always* return one record, even if there are no
records in the table - it will return Null in that situation. So ...

Public Sub HasRecords()

Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Max([TestDate]) AS TheDate FROM tblTest"
.Open
If IsNull(.Fields("TheDate")) Then
MsgBox "There aren't any records - or at least none with a
date entered"
Else
MsgBox "The date is: " & .Fields("TheDate")
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)

can you send a full function with code like mine that did the job? I
can't find an example of using a dsn that referenced a front end file,
but with linked tables and front end local tables.


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
If you *only* want to know whether the recordset returns records or
not, you can test the BOF and EOF properties. If both are True, the
recordset doesn't return any records, otherwise it does ...

rst.Open
If (rst.BOF And rst.EOF) Then
'no records
Else
'records
End If

--
Brendan Reynolds (MVP)

I get an error saying "Rowset does not support fetchuing backward "
when trying your method.


message A recordset is not "filled" with all records until you actually
access those records. Thus, add a step to move to the last record
before you get the record count.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Below returns -1 for the record count when it should report a 1
for the Max Date field when the table has data. I need the
RecordCount property to return the correct number of records
because I need to test the table to see if it actually has any
records.

Any ideas?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "Select Max([myDateField]) From t_import"

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing