How to count Tables - ADO, ASP

[Satheeshkumar N]

How to count Tables by program

How to count the No. of tables available in a access database by program...Is it possible. or not.

 

[Mile-O]

Public Function TableCount() As Integer
    On Error Resume Next
    Dim db As DAO.Database
    Set db = CurrentDb
    TableCount = db.TableDefs.Count
    Set db = Nothing
End Function

• You will need to have a reference set to DAO (to avoid the Undefined Type error) if you are using A2000 or A2002.

 

[Satheeshkumar N]

How do I, Count the No. of tables & get teir info available in a ACCESS(2000) file.
In ADO, I'm using in ASP code.

Thanks Friends,

 

[RoyVidar]

There's probably a number of ways. Once you have your connection (here represented with cn), you could do use the OpenSchema method on the connection, and loop thru it.

dim rs as adodb.recordset
dim lngCount as long
set rs=cn.openschema(adSchemaTables, array(empty, empty, empty, "tables"))
do while (not rs.eof)
lngCount=lngCount+1
rs.movenext
loop
rs.close
set rs=nothing
msgbox "number of tables = " & lngCount

To get more info, you could replace the "simple" loop above with something like this:

dim lngCounter as long
...

for lngCounter=0 to rs.fields.count-1
debug.print rs.fields(lngCounter).name,
loop
debug.print
do while (not rs.eof)
lngCount=lngCount+1
for lngCounter=0 to rs.fields.count-1
debug.print rs.fields(lngCounter).value,
loop
debug.print
rs.movenext
loop

Another possibility, is using the tables collection of ADOX.

dim cat as new adox.catalog
dim lngCount as long
dim tbl as adox.table
cat.activeconnection = cn ' your connection oject
debug.print cat.tables.count ' will include queries...
for each tbl in cat.tables
if tbl.type="table" then
lngCount=lngCount+1
debug.print tbl.name
end if
next tbl
msgbox "number of tables = " & lngCount

- typed not tested - how to translate to ASP, that's your job, this is an Access forum

 

[Mile-O]

Inspired by this thread, I came up with a number of different ways to check if a table exists - some DAO,some ADO, and one being neither.

All are tried and tested.

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    For Each tdf In db.TableDefs
        If tdf.Name = TableName Then
            TableExists = True
            Exit For
        End If
    Next
    
Exit_TableExists:
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists
    
End Function

----------------------------------------------------------------

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists

    Const Quote As String = """"
    Const strField As String = "Total"
    Const SQL As String = "SELECT Count(*) As Total FROM MSysObjects " & _
         "WHERE Type = 1 AND Name = "
    
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset(SQL & Quote & TableName & Quote)
    
    If rs.Fields(strField) = 1 Then
        TableExists = True
    End If
        
Exit_TableExists:
    Set rs = Nothing
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists

End Function

----------------------------------------------------------------

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists

    Const Quote As String = """"
    Const strField As String = "Total"
    Const SQL As String = "SELECT Count(*) As Total FROM MSysObjects " & _
         "WHERE Type = 1 AND Name = "
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    rs.Open SQL & Quote & TableName & Quote, _
        CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    If rs.Fields(strField) = 1 Then
        TableExists = True
    End If
        
Exit_TableExists:
    Set rs = Nothing
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists
    
End Function

----------------------------------------------------------------

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists
    
    Dim cn As New ADODB.Connection
    Dim tdf As ADOX.Table
    Dim cat As New ADOX.Catalog
    
    Set cn = CurrentProject.Connection
    Set cat.ActiveConnection = cn
    
    For Each tdf In cat.Tables
        If tdf.Name = TableName Then
            TableExists = True
            Exit For
        End If
    Next
    
Exit_TableExists:
    Set cat = Nothing
    Set cn = Nothing
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists
    
End Function

----------------------------------------------------------------

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists

    Const FieldExp As String = "Name"
    Const TableExp As String = "MSysObjects"
    
    Dim strCriteria As String
    
    strCriteria = "Name = """ & TableName & """ AND Type = 1"
    
    If DCount(FieldExp, TableExp, strCriteria) = 1 Then
        TableExists = True
    End If
    
Exit_TableExists:
    strCriteria = vbNullString
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists
    
End Function

----------------------------------------------------------------

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists
    
    Const TableNames As String = "Table_Name"

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    Set rs = cn.OpenSchema(adSchemaTables)

    With rs
        Do While Not .EOF
            If .Fields(TableNames) = TableName Then
                TableExists = True
                Exit Do
            End If
            .MoveNext
        Loop
    End With
    
Exit_TableExists:
    Set rs = Nothing
    Set cn = Nothing
    Exit Function
    
Err_TableExists:
    TableExists = False
    Resume Exit_TableExists
    
End Function

 

[RoyVidar]

And all of them checks for only one table

Here's another version of testing for only one table, which is equal to SJ McAbney's last one, except it doesn't loop thru the recordset (more efficient - btw you are aware that when no constraints are passed, it loops all tables and queries), just checks wheter or not a record exists in the schema for the specified table, but all of these are a bit off scope, with regards to the OP's question:

public function FindTableAdo(byval vstrTable as string) as boolean
  dim rs as adodb.recordset
  set rs=cn.openschema( _
         adschematables,array(empty,empty,vstrTable,"Table"))
  ' cn again being your connection
  FindTableAdo=not rs.eof
  rs.close
  set rs=nothing
end function

Gee - I see I've put "Tables" as constraint for the previous openschema, whilst it should have been "Table" - ouch

 

[Mile-O]

adschematables,array(empty,empty,vstrTable,"Table"))

I just copied it into Access but can't get it to work. Object required error.

I've also put: Set rs = New ADODB.Recordset at the start.

What's the Array() function doing in this instance? What do its arguments represent?

As usual, Access Help isn't too helpful.

 

[RoyVidar]

That's two lines, see the underscore at the set rs...

set rs=cn.openschema( _
         adschematables,array(empty,empty,vstrTable,"Table"))

where cn is the connection (currentproject.connection, a connection based on some external db...), which I'm assuming is set somewhere else (public?) - so if thats the conflict, instantiate as you do yours, thru currentproject.connection.openschema(adscemata.... or assign/instantiate currentproject.connection to cn

First arguement/parameter of the OpenSchema method is the querytype (adschematables, adschemacolumns...), second is the criteria, which, from the help files are:

"Criteria Optional, an array of query constraints for each QueryType options as listed in ShcemaEnum"

So, taking a look at the SchemaEnum, for adSchemaTables, which contains:
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

We can then use those when opening the schema. Note the preceding parameters must then be passed as Empty.

"array(empty,empty,vstrTable,"Table")" - means no constraints on TABLE_CATALOG and TABLE_SCHEMA, but for
TABLE_NAME - the constraint is contained in vstrTable and for
TABLE_TYPE - the constraint is "Table" (excluding VIEW, ACCESS TABLE, SYSTEM TABLE, LINK, PASS-THROUGH (query - my comment)... )

Here's one link
http://support.microsoft.com/default.aspx?scid=kb;en-us;186246

When using rs.open... the recordset must be instantiated first, when assigning a recordset to rs thru OpenSchema or Execute (on the connection), there's no need to pre instantiate the recordset.

 

[Mile-O]

Understood. Schemas are still a new thing to me. I'm well on my way now.

 

[Mile-O]

Into my library goes this:

Public Function TableExists(ByVal TableName As String) As Boolean

    On Error GoTo Err_TableExists
    
    Const Constraint As String = "Table"

    Dim rs As ADODB.Recordset
    Set rs = CurrentProject.Connection.OpenSchema( _
        adSchemaTables, Array(Empty, Empty, TableName, Constraint))
    
    TableExists = Not rs.EOF
  
Exit_TableExists:
  Set rs = Nothing
  Exit Function
  
Err_TableExists:
  TableExists = False
  Resume Exit_TableExists
  
End Function

Thanks again.

 

[RoyVidar]

Thank you SJ McAbney, I'm honored to be able to give something back!

I'm only at the start of understanding the schemathingies myself, so I really haven't more than a couple of centimeters lead (you are going metric soon, aren't you )

Just to add another comment, I'm a bit paranoid on things used in production, and the set rs=nothing in the exit clause may under some circumstances perhaps give some anomalities if the recordset is open (ADO differs a bit from DAO).

if (not (rs is nothing)) then
  if (rs.state=adstateopen) then
    rs.close
  end if
  set rs=nothing
end if

And I've finally figured out how to use the code tags

 

[Mile-O]

(you are going metric soon, aren't you )

I was born metric.

Just to add another comment, I'm a bit paranoid on things used in production, and the set rs=nothing in the exit clause may under some circumstances perhaps give some anomalities if the recordset is open (ADO differs a bit from DAO).

True.

 

[Lister]

I am going to add this question to this thread as I think it could be handled by the about functions.
The link table manager window is very small and some of the link “names” can get quite long. You can’t seem to “view” all the string.
So if I could loop through all tables in a database and return Name and Link address that would be great as a tool.
But what or where is the link string stored in regard to the table name?

So the loop would return
Linked Table Names
And the file string address of the link

So if someone was able to point me in the right direction that would be great.

Thanks