Access 2002

[ryand09]

I'm upgrading from Access 97 to Access 2002. I got a connection set up as below, but the recordcount keep coming back as -1 even though the query is returning 12 records. Any reason why?

Thanks in advance


Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intCount As Integer
Dim strSQL As String

strSQL = "SELECT t.Fare_key_data_code FROM tbl_key_MK AS t;"

'Connect to Current Project
Set conn = CurrentProject.Connection
'Set rst active connection to connection defined in Conn
rst.ActiveConnection = conn

rst.Open strSQL

With rst

intCount = .RecordCount

End With

rst.Close
conn.Close

Set rst = Nothing
Set conn = Nothing

[ryand09]

Aploogies

The problem is actually with attached Tables. The CurrentProject.Connection doesn't work. The SQL below

strSQL = "SELECT g.ID FROM G AS g WHERE (((g.Lname) Like 'R*'));"

is from an attached table, but the recordset returns an .eof=true. If I create a query Selecting * from the table below everything is fine. Do I have to supply the full connection string e.g. conn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test_2002.mdb" to get data or is there another way?

[rjhodges]

I don't have a lot of experience with ADO, but when using DAO the RecordCount can be made to be correct by issuing a rst.MoveLast command after the recordset is opened.

Looking in my copy of "Access Database Design & Programming", I found the following:
"The RecordCount property returns -1 when ADO cannot determine the number." and goes on to state that the recordset must be fully populated for the RecordCount to be accurate. They also mention this my put a drain on resources on large tables.

I hope this helps.

Ron

[ryand09]

Ron

FYI

If you open the recordset as rst.Open strSQL, , adOpenStatic you don't have to .MoveLast to count the records.

Regards.

[rjhodges]

ryand09: Thanks for the info! I'll put that in my "things to remember for next time" folder. Having been a programmer of one sort or another for over 40 years, I've learned this: THERE'S ALWAYS SOMETHING MORE TO LEARN!

Ron