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.
Course Of The Month
3 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Retrieve number of fields and rows from Access Database
Posted on 2008-09-30
Hi,
How can I retrieve how many rows and fields are present in a ms access table? (in vb6)
Regards,
Dennis
How can I retrieve how many rows and fields are present in a ms access table? (in vb6)
Regards,
Dennis
[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
Active today
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
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
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
It needs to be a DAO connection.
And I hoped for something without looping through records. Is there no direct call function?
Regards,
Dennis
And I hoped for something without looping through records. Is there no direct call function?
Regards,
Dennis
Active today
You can change JohnBPrice's code to DAO by using
set rs = db.OpenRecordSet(strSQL)
instead of rs.Open
set rs = db.OpenRecordSet(strSQL)
instead of rs.Open
Featured Post
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…
Suggested Courses
Course of the Month3 days, 2 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
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.