VBA Recordcount

Hi Guru's,

I did a search on this subject but no one has encountered the error I am having. This is the code

------------------------------------------------------------------
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & "G:\NAWKLANT\FONTYS\FONTYS.MDB" & ";Persist Security Info=False"
conn.Open

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
rst.Open "SELECT * FROM Studenten"

Number = rst.RecordCount

rst.MoveLast
rst.MoveFirst

txtRecCount.Text = Number
--------------------------------------------------------------------

I get an error at the rst.open part saying that this connection can not be used for this action, or this action is not valid in this context.

Hope someone can tell me what I am doing wrong and excuse my newbieness.

Thanks in advance,

Jasper

I think your problem is that you haven't assigned a connection to RST.

Will you try RST.ACTIVECONNECTION = CONN before your RST.OPEN.

HTH

Whaaaaaaaa, it worked. Thank you very much!

Jasper

You would be a lot better off doing

Select count(*) from tablename. This will return the total number of records. I don't know what ado is doing behind the covers and I am sure I don't want to know.

Regards,
David Ritchie
CodeLab Technology Group

Hi Cambece,

Why would I change something that's already working properly?

Jasper

The Select Count(*) will save you having to open the recordset, and then going to the last record, and trying to get the number of records twice.

I have found that depending on the cursor I use (adUseClient or adUseServer), that sometimes the .RECORDCOUNT returned is -1, where this property may not be supported I guess.

I think in Access it is faster to use COUNT(*) instead of COUNT(fld name). Don't know about SQL Server though.

HTH

I hope I did not offend you. That was not my intention, but rather to educate you in the proper way of developing a piece of code that counts the number of records in a table.

The select count method will make your code better for the following reasons:

Performance (You will not open a cursor)
Portability (It will work even if you switch versions of ADO)
Reliablity (You can get unpredicatable results from you method)

The reason you would change something that already works is to make it better.

Regards,
David Ritchie
CodeLab Technology Group

Hi Cambece and jp140768,

Thank you both for clearing this up for me, and Cambece I am not offended at all. I am going to try your piece of code. So I can keep the rest of the code the same and just ad Count in the rst.open part? Oh, and I don't think I mentioned I work within VBA :D .


Many thanks,

Jasper