Open recordset problems

[Sunil Korah]

I am having some trouble with opening recordsets. I have used code
more or less straight from the access help. But still I am getting
some errors. I am unable to work out what exactly I am doing wrong.

1.When I try the following code it gives the error message "compile
error- type mismatch" on the last line. ( I have seen in past postings
that this error is usually because of DAO/ADO mismatch. Is the
recordsource for forms in Access 2000 by default ADO?)

Dim dbsTemp As DAO.Database
Dim rstTemp As DAO.Recordset

Set dbsTemp = OpenDatabase("C:\Documents and Settings\j\My
Documents\db1.mdb")
Set rstTemp = dbsTemp.OpenRecordset( "SELECT * FROM Table1")

With rstTemp | This is just to test whether it
.MoveLast | opens the recordset. I get a count of 6
.MoveFirst | which is the number of records in the table
MsgBox (rstTemp.RecordCount)
End With
Me.RecordSource = rstTemp -> the error appears here

2. In the following code, the record count is shown as "–1". Thias is
the same table as above, containing 6 records

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSource As String
strSource = "SELECT * FROM Table1;"
rst.Open strSource, cnn, adOpenDynamic

If rst.EOF Then
MsgBox ("No records")
Exit Sub
Else
rst.MoveLast
rst.MoveFirst
MsgBox (rst.RecordCount)
End If
Sunil Korah

[Terry Kreft]

Relying on the RecordCount property in a recordset is not good practice.

The following code is better for your purposes

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSource As String

strSource = "SELECT Count(*) FROM Table1;"
rst.Open strSource, cnn, adOpenStatic

If not (rst.EOF AND rst.BOF) Then
MsgBox (rst.Fields(0))
Else
MsgBox ("No records")
Exit Sub
End If
--
Terry Kreft
MVP Microsoft Access
"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I am having some trouble with opening recordsets. I have used code
more or less straight from the access help. But still I am getting
some errors. I am unable to work out what exactly I am doing wrong.

1.When I try the following code it gives the error message "compile
error- type mismatch" on the last line. ( I have seen in past postings
that this error is usually because of DAO/ADO mismatch. Is the
recordsource for forms in Access 2000 by default ADO?)

Dim dbsTemp As DAO.Database
Dim rstTemp As DAO.Recordset

Set dbsTemp = OpenDatabase("C:\Documents and Settings\j\My
Documents\db1.mdb")
Set rstTemp = dbsTemp.OpenRecordset( "SELECT * FROM Table1")

With rstTemp | This is just to test whether it
.MoveLast | opens the recordset. I get a count of 6
.MoveFirst | which is the number of records in the table
MsgBox (rstTemp.RecordCount)
End With
Me.RecordSource = rstTemp -> the error appears here

2. In the following code, the record count is shown as "-1". Thias is
the same table as above, containing 6 records

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSource As String
strSource = "SELECT * FROM Table1;"
rst.Open strSource, cnn, adOpenDynamic

If rst.EOF Then
MsgBox ("No records")
Exit Sub
Else
rst.MoveLast
rst.MoveFirst
MsgBox (rst.RecordCount)
End If
Sunil Korah

[Darryl Kerkeslager]

As to #2, the rst.RecordCount is not a reliable way to count the records in
some ADO recordsets, is not possible in other recordsets, and should be
avoided because of speed issues in most other situations. Use Count(*)
instead:

With rst
.Source = "SELECT Count(*) FROM Table1"
.Open , cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
If .Fields(0) = 0 Then
MsgBox ("No records")
Exit Sub
Else
MsgBox "Count: " & .Fields(0)

You can then close the recordset and re-open it as you want, or change the
rst.Source and rst.Requery. In either case, there is practically no
overhaead.

.Close
.Source = "SELECT * FROM Table1"
.Open , cnn, adOpenKeyset, adLockOptimistic, adCmdText

' Do recordset stuff

.Close
End If
End With
Set rst = Nothing
"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I am having some trouble with opening recordsets. I have used code
more or less straight from the access help. But still I am getting
some errors. I am unable to work out what exactly I am doing wrong.

1.When I try the following code it gives the error message "compile
error- type mismatch" on the last line. ( I have seen in past postings
that this error is usually because of DAO/ADO mismatch. Is the
recordsource for forms in Access 2000 by default ADO?)

Dim dbsTemp As DAO.Database
Dim rstTemp As DAO.Recordset

Set dbsTemp = OpenDatabase("C:\Documents and Settings\j\My
Documents\db1.mdb")
Set rstTemp = dbsTemp.OpenRecordset( "SELECT * FROM Table1")

With rstTemp | This is just to test whether it
.MoveLast | opens the recordset. I get a count of 6
.MoveFirst | which is the number of records in the table
MsgBox (rstTemp.RecordCount)
End With
Me.RecordSource = rstTemp -> the error appears here

2. In the following code, the record count is shown as "-1". Thias is
the same table as above, containing 6 records

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSource As String
strSource = "SELECT * FROM Table1;"
rst.Open strSource, cnn, adOpenDynamic

If rst.EOF Then
MsgBox ("No records")
Exit Sub
Else
rst.MoveLast
rst.MoveFirst
MsgBox (rst.RecordCount)
End If
Sunil Korah