Solved

Retrieve number of fields and rows from Access Database

Posted on 2008-09-30
6
460 Views
Last Modified: 2012-05-05
Hi,

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

Regards,
Dennis
0
Comment
Question by:Schuttend
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22612476
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
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 50 total points
ID: 22613829
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
 

Author Comment

by:Schuttend
ID: 22641791
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22648428
You can change JohnBPrice's code to DAO by using

set rs = db.OpenRecordSet(strSQL)

instead of rs.Open
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 22672930
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
 

Author Closing Comment

by:Schuttend
ID: 31501871
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question