RecordCount doesn't work?

[KenHigg]

Can anyone see why this returns -1 in the msgbox?

    Dim tstrTable As String
        
    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    
    tstrTable = "Table1"
    
    rs1.Open tstrTable, cn, adOpenDynamic, adLockOptimistic
    
    MsgBox rs1.RecordCount
     
    rs1.Close

kh

 

[Mile-O]

It probably doesn't know how many records to count as you have not "populated" the recordset.

With rs1
    .MoveLast
    .MoveFirst
    MsgBox .RecordCount
End With

 

[KenHigg]

Fixed!

rs1.Open tstrTable, cn, [COLOR=Red]adOpenDynamic[/COLOR], adLockOptimistic

kh

 

[EMP]

Your first post:
rs1.Open tstrTable, cn, adOpenDynamic, adLockOptimistic

Your second post:
rs1.Open tstrTable, cn, adOpenDynamic, adLockOptimistic


Can't see any difference.

 

[KenHigg]

Fixed it with:

rs1.Open tstrTable, cn, [COLOR=Red]adOpenStatic[/COLOR], adLockOptimistic

duh...
Sorry,
kh

 

[ecniv]

For your info:

Dynamic - allows updates - recordcount property disabled (returns true I think for more than one record or false for 0 records)
Static - snapshot type - acts like Dao and allows recordcount - probably need to move last move first before it (just incase).


Vince

 

[KenHigg]

Thanks ecniv, Hum... I wonder if one could swap from dynamic to static on the fly...

kh

 

[ecniv]

Don't think so...


What situation would there be for doing such a switch?
You could open static and still use the connection to execute an Insert Into... or Update...

Again, not sure how this would affect the recordset.


Vince

 

[KenHigg]

Just from the nature of the setting names, static and dynamic, I assumed one could not edit records in a static situation...

Guess I need to read up a bit more,

Thanks,
ken