I can't use ADOX.Catalog to get views or users.

yangfanatp2p · December 3rd, 2003, 12:14 AM

I find that ADOX.Catalog.Tables return me all the tables and views in the MS SQL pubs database.
But,I can't retrieve only views, even more, I can't access its Count property.

Thanks a lot!

sal · December 3rd, 2003, 02:19 PM

Are you using Access? There is a table that access references and this table has a TYPE that determines if the table is a system table, view, query, etc. I do not remember what the table is.

Sal

sal · December 3rd, 2003, 02:26 PM

This code is not complete, but you get the point.

Code:

    
Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim intCount As Integer
    Dim tbl As New ADOX.Table
    Dim intColCount As Integer

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source= C:\New.mdb;"

    Set cat.ActiveConnection = cnn

    intCount = cat.Tables.Count
    Debug.Print cat.Tables.Count

    Do While intCount > 0
        intCount = intCount - 1
        Select Case True
            Case cat.Tables(intCount).Type = "TABLE"
                Debug.Print cat.Tables(intCount).Type & " " & cat.Tables(intCount).Name

                        Set tbl = cat.Tables(intCount)
                        intColCount = cat.Tables(intCount).Columns.Count

                        Debug.Print intColCount & " Fields"
                        Do While intColCount > 0
                            intColCount = intColCount - 1
                            Debug.Print "       " & intColCount & "    " & tbl.Columns(intColCount).Name
                            Debug.Print "                              " & tbl.Columns(intColCount).Type
                        Loop

            Case cat.Tables(intCount).Type = "VIEW"
                'Debug.Print cat.Tables(intCount).Type & " " & cat.Tables(intCount).Name
        End Select
    Loop

Sal

yangfanatp2p · December 3rd, 2003, 08:41 PM

Thanks Sal!
The code works well on the MS SQL Server too.

sal · December 4th, 2003, 09:43 AM

for SQL Server use SQLDMO. That is the same code that Enterprise Manager uses to manage the DB. It is stronger than ADOX.

Sal

yangfanatp2p · December 4th, 2003, 08:35 PM

Ahhhhhh yeah, I see! Thanks a bunch guys! I really appreciate it.

sal · December 5th, 2003, 10:58 AM

do a search in msdn for sqldom or sql-dmo. I know that there is a bunch of useful info on this. Also in BOL.

Sal

yangfanatp2p · December 6th, 2003, 12:43 AM

In fact, i just want to write a program to generate some code automatically.
i usually use a class module to represent a table/entity in the database.
These classes' properties will used to act as corresponding fields.
Can you tell me more useful mode to program with database?

Any help are always appreciated.

sal · December 6th, 2003, 01:31 AM

I do not understand what you mean by that.

Sal

yangfanatp2p · December 6th, 2003, 05:35 AM

sorry, english is my second language.

i mean program like this is efficient or not.

Employee table:
Id integer
Name string 20
Birthday date
************ boolean
Address string 50

Â¡Â®Employee class:

Option Explicit
Private mId As Integer
Private mName As String
Private mBirthday As Date
Private mSex As Boolean
Private mAddress As String
Private mIsDirty As Boolean

Public Property Get Id() As Integer
  Id = mId
End Property

Public Property Get Name() As String
  Name = mName
End Property

Public Property Let Name(ByVal vNewValue As String)
  If mName <> vNewValue Then
    mName = vNewValue
    mIsDirty = True
  End If
End Property

Public Property Get Birthday() As Date
  Birthday = mBirthday
End Property

Public Property Let Birthday(ByVal vNewValue As Date)
  If mBirthday <> vNewValue Then
    mBirthday = vNewValue
    mIsDirty = True
  End If
End Property

Public Property Get ************() As Boolean
  ************ = mSex
End Property

Public Property Let ************(ByVal vNewValue As Boolean)
  If mSex <> vNewValue Then
    mSex = vNewValue
    mIsDirty = True
  End If
End Property

Public Property Get Address() As String
  Address = mAddress
End Property

Public Property Let Address(ByVal vNewValue As String)
  If mAddress <> vNewValue Then
    mAddress = vNewValue
    mIsDirty = True
  End If
End Property

Public Property Get IsDirty() As Variant
  IsDirty = mIsDirty
End Property

Public Function Save()
  If mIsDirty Then
    '..save to db
  End If
End Function

Public Function Delete()
  '..delete from db
End Function