To count records in a mdb file

[Doctor Luz]

VC++ 6.0
MFC

I need to know the number of records in a table of a .mdb Access 97 file.

The code I have uses CRecordset (CRecordset* m_pSet)

Now I'm using a loop to count records

int n=0;
m_pSet->MoveFirst();
while (m_pSet->IsEOF()==FALSE){
 n++;
m_pSet->MoveNext();
}

With a big number of records this method is not very efficient.

¿Do anybody know other method more efficient to retrieve the number of records?

Thank you

[NigelQ]

It looks as though you are using DAO to access the database here.

Switching to ADO would have significant benefits, including the ability to perform a GetRecordCount on a recordset.

An alternative might be to query the database directly:

SELECT Count(TableID) FROM tblMyTable WHERE xxxx;

...which returns the number of rows/records matching your query.

Hope this helps,

- Nigel

[Vaderman]

Unfortunately there isn't a way to retrieve the max number of records directly (I don't know personally.). You have to continue with using MoveNext() until EOF, only then can you use GetRecordCount().

Can I suggest that you use SQL to acheive this?

SELECT COUNT(*) FROM <tablename>

Regards

John

[Doctor Luz]

It looks as though you are using DAO to access the database here.

Switching to ADO would have significant benefits, including the ability to perform a GetRecordCount on a recordset.

An alternative might be to query the database directly:

SELECT Count(TableID) FROM tblMyTable WHERE xxxx;

...which returns the number of rows/records matching your query.

Hope this helps,

- Nigel

The ADO option would imply lot of changes. I think is inaccessible now.

And I can't figure out how to query the database directly and obtain a response with tne number of records.

Thank you

[Doctor Luz]

Unfortunately there isn't a way to retrieve the max number of records directly (I don't know personally.). You have to continue with using MoveNext() until EOF, only then can you use GetRecordCount().

Can I suggest that you use SQL to acheive this?

SELECT COUNT(*) FROM <tablename>

Regards

John

Yes, but where is stored the number of records?

I can call ExecuteSQL() but the return is void

Thank you

[GCDEF]

Here's a function I wrote to do it. You can modify it as necessary.

int GetCount(CRecordset* pSet, const char* lpszUnique)
{
	if(pSet->IsOpen())
	{
		if(pSet->IsEOF())
		{
			CRecordsetStatus rStat;
			pSet->GetStatus(rStat);
			if(rStat.m_bRecordCountFinal == 0)
				return 0;
		}
	}
	AfxGetApp()->BeginWaitCursor();

	
	SQLHSTMT      hstmt;		
	SQLRETURN     retcode;
	SQLINTEGER iIndCount;
	long lValue;

	retcode = ::SQLAllocStmt(pSet->m_pDatabase->m_hdbc, &hstmt);
	SQLRETURN retVal = SQLBindCol(hstmt, 1, SQL_C_SLONG, &lValue, 0, &iIndCount);
	CString strSQL;
	if(lpszUnique != NULL)
		strSQL.Format("SELECT COUNT(DISTINCT [%s]) FROM %s", lpszUnique, pSet->GetTableName());
	else
		strSQL.Format("SELECT COUNT(*) FROM %s", pSet->GetTableName());
	if(!pSet->m_strFilter.IsEmpty())
	{
		strSQL += " WHERE ";
		strSQL += pSet->m_strFilter;
	}

	retVal = SQLExecDirect(hstmt, (SQLTCHAR*)(const char*)strSQL, SQL_NTS);
	if(retVal == SQL_ERROR)
	{
		return -1;
	}
	while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
	   retcode = SQLFetch(hstmt);

	SQLFreeStmt(hstmt, SQL_CLOSE);
	SQLFreeStmt(hstmt, SQL_DROP);

	return lValue;
}

[Doctor Luz]

Here's a function I wrote to do it. You can modify it as necessary.

Your method is really fast, I have improved the speed totally.

Thank you very much.

[GCDEF]

Your method is really fast, I have improved the speed totally.

Thank you very much.

Wow, you got it implemented quickly.

Happy to help.