count rows in DB

[Swimfish]

heyya i'm pretty new to VB. Doing a small project and would like to know if anyone could help. Is there a way that i can limit the entry into a table (row) ? Say, i only want 10 rows to be occupied, after that a message will prompt user that the allocation is full.

and how do i do a row count in a table and display the total in the front end?

thanks!!

[axion_sa]

SELECT COUNT(*) FROM TableName

[Swimfish]

i know the count(*) code.. but how do i put the count result into a textbox?

[axion_sa]

Public Function getRecordCount(ByVal strTable As String) As Long
  Dim tConn As ADODB.Connection
  Dim rsaGetCount As ADODB.Recordset
  Dim strSQL As String
 
  ' Default the return value.
  getRecordCount = 0
 
  Set tConn = New ADODB.Connection
  tConn.Open [Connection string to your database]
 
  strSQL = "SELECT COUNT(*) AS Cnt FROM " & strTable
  Set rsaGetCount = New ADODB.Recordset
  rsaGetCount.Open strSQL, tConn
 
  getRecordCount = rsaGetCount("Cnt")
 
  ' Clean up.
  On Error Resume Next
  rsaGetCount.Close
  Set rsaGetCount = Nothing
 
  tConn.Close
  Set tConn = Nothing
End Function

And to get the value into a textbox:

txtRecords.Text = getRecordCount("YourTableName")

[khalik_ash]

Originally posted by axion_sa

Public Function getRecordCount(ByVal strTable As String) As Long
  Dim tConn As ADODB.Connection
  Dim rsaGetCount As ADODB.Recordset
  Dim strSQL As String
 
  ' Default the return value.
  getRecordCount = 0
 
  Set tConn = New ADODB.Connection
  tConn.Open [Connection string to your database]
 
  strSQL = "SELECT COUNT(*) AS Cnt FROM " & strTable
  Set rsaGetCount = New ADODB.Recordset
  rsaGetCount.Open strSQL, tConn
 
  getRecordCount = rsaGetCount("Cnt")
 
  ' Clean up.
  On Error Resume Next
  rsaGetCount.Close
  Set rsaGetCount = Nothing
 
  tConn.Close
  Set tConn = Nothing
End Function

And to get the value into a textbox:

txtRecords.Text = getRecordCount("YourTableName")

u have to specify the lock type....

rsaGetCount.Open strSQL, tConn,adOpenKeyset, adLockOptimistic

[axion_sa]

No, you don't - SQL is being used to get record count.

[Swimfish]

i did it like this. seems to work so far. do i have any errors??
(why do i need to put the getRecordCount = 0 ) at the beginning?)

Dim objConn As New Connection
Dim objRS As Recordset
Dim strSQL As String

getRecordCount = 0

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Camp.mdb"

Set objRS = New ADODB.Recordset
objRS.CursorType = adOpenKeyset
objRS.LockType = adLockOptimistic

strSQL = "SELECT COUNT(*) AS Cnt FROM Member" & strTable
objRS.Open strSQL, objConn

getRecordCount = objRS("Cnt")

objConn.Close

calculate.Text = getRecordCount

[axion_sa]

Originally posted by Swimfish
i did it like this. seems to work so far. do i have any errors??
(why do i need to put the getRecordCount = 0 ) at the beginning?)

You don't have to put the getRecordCount = 0, just a habit of mine. You also don't have to set the CursorType or LockType, the defaults will be fine, doesn't matter either way.
If you've got a global connection to the database somewhere, pass that to the function as well - it's more efficient.