Retrieve number of fields and rows from Access Database

Hi,

How can I retrieve how many rows and fields are present in a ms access table? (in vb6)

Regards,
Dennis
SchuttendAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
JohnBPriceConnect With a Mentor Commented:
You don't actually need ADOX to get just row and field count, do
    rs.open "Select count(*) from table", blah blah blah
to get the row count as rs.fields(0).value,
do
    rs.open "select * from table where 1=2", blah blah blah
and then
the count is  
    rs.fields.count
0
 
GrahamSkanRetiredCommented:
This is one way. It uses the Microsoft Active Data Objects and the Microsoft ADO Ext for DDL and Security libraries, so you need to set references to them.
Private Sub Command1_Click()
    Dim strConnection As String
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim rs As New ADODB.Recordset
    Dim r As Long
    Dim strDatabasePath As String
    strDatabasePath = "C:\MyFolder\MyDatabase.mdb"
    Const TableName = "MyTable"
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath & ";Persist Security Info=False"
    cat.ActiveConnection = strConnection
    MsgBox "Field Count = " & cat.Tables(TableName).Columns.Count
    cn.Open strConnection
    rs.Open TableName, cn, adOpenDynamic, adLockPessimistic, adCmdTable
    Do Until rs.EOF
        r = r + 1
        rs.MoveNext
    Loop
    MsgBox "Record count =" & r
    rs.Close
    cn.Close
End Sub

Open in new window

0
 
SchuttendAuthor Commented:
It needs to be a DAO connection.
And I hoped for something without looping through records. Is there no direct call function?

Regards,
Dennis
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Instantly
 
GrahamSkanRetiredCommented:
You can change JohnBPrice's code to DAO by using

set rs = db.OpenRecordSet(strSQL)

instead of rs.Open
0
 
JohnBPriceCommented:
GrahamSkan has the DAO version, and it doesn't loop through the records as long as you have a key or index on any column.
0
 
SchuttendAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.