ADORecordset.RecordCount = -1

[lho]

Hi everyone,

Would you please tell me why this is happening? I use ADO with MS Access 2002 and here is what I do:

'Declarations
Private adoConn As New ADODB.Connection
Private adoRS As New ADODB.Recordset

Private StringCnn As String
Private AccessCnn As String
.......
'make the connection for the DB
AccessCnn = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=EmailData.mdb;" & "DefaultDir=" & App.Path + "\" & ";" & "UID=admin;PWD=;"
StringCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\EmailData.mdb;Persist Security Info=False;"

'''These are the codes from by Sub
sSource = "SELECT * FROM Server" 'Server is the table name
adoConn.Open StringCnn & App.Path & "\EmailData.mdb" 'Emaildata is the DB file name
adoRS.Open sSource, adoConn, adOpenDynamic, adLockOptimistic

With adoRS
If .RecordCount Then
.MoveFirst
Do While Not .EOF
...........I do lots of things in here
Loop
End If

.Close
Set adoRS = Nothing
adoConn.Close
Set adoConn = Nothing
End With

I have 2 records in table "Server" but .RecordCount gives me -1, this is NOT right. Then I deleted ALL records from table "Server" but now .RecordCount still gives me -1.

What is wrong with it? How to fix it?

Thanks,

[HairyMonkeyMan]

Before you access the recordcount property, do this:

adoRS.MoveLast
adoRS.MoveFirst

That should get it working properly

[lho]

[QUOTE=HairyMonkeyMan]Before you access the recordcount property, do this:

adoRS.MoveLast
adoRS.MoveFirst

Then when it is a "Brand NEW" table with zero (0) record, the adoRS.MoveLast will create an Error.

I used the statement "If adoRS.RecordCount" to check to see if there is any record in the DB, if there is no record, the loop will never get executed.

Thanks for your input. Is there something wrong with Access or ADO????

Thanks,

[murali9677]

hi,

The property returns -1 when ADO cannot determine the number of records. Reading the RecordCount property on a closed Recordset causes an error.

try closing the recordset,before opening,
try this sample


Private sub CmdSample_Click()

Dim sql
sql = "Select * from Table2"

On Error GoTo ErrMessage

If rs.State = 1 Then
rs.Close
End If



With rs
.ActiveConnection = cn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open sql

If .EOF Or .BOF Then
MsgBox "No Records"
Exit Sub
Else
.AddNew
.Fields!Name = "Murali"
.Fields!Age = "28"
.Update
End If

Set rs = Nothing

End With

Exit Sub
ErrMessage:
If Err.Number <> 0 Then
MsgBox Err.Description
Exit Sub
End If

end sub

As per the above example...i didnt felt any necessay to use RecordCount property, for just checking whether there are any records or not.

I've found myterious activity when i used Recordcount property using ADO, when trying to delete one or more records having a smilar primarykey in a table.

Ill try using Recordcount property, and try the above solution, if it fits ur problem..and im sorry, if the above sloution didnt answer ur question.

[Patzer]

Hi,

you have to set the .cursorlocation property of the recordset to aduseclient.
It is quite clear that .recordcount cannot work without this, as the recordset object "assumes" that the cursor may be server-sided.
You should - additonally - set the .cursortype property to adopenstatic.

[lho]

Hi,

you have to set the .cursorlocation property of the recordset to aduseclient.
It is quite clear that .recordcount cannot work without this, as the recordset object "assumes" that the cursor may be server-sided.
You should - additonally - set the .cursortype property to adopenstatic.

Yes, when I set the .cursortype to adOpenStatic or adOpenKeyset, the .RecordCount give me the correct values.

Thanks guys.