Solved

Retrieve number of fields and rows from Access Database

Posted on 2008-09-30
6
455 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
  • 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now