Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
| Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 3rd, 2003, 12:14 AM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default I can't use ADOX.Catalog to get views or users.

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!
 
Old December 3rd, 2003, 02:19 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 3rd, 2003, 02:26 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 3rd, 2003, 08:41 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 4th, 2003, 09:43 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
 
Old December 4th, 2003, 08:35 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 5th, 2003, 10:58 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 6th, 2003, 12:43 AM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old December 6th, 2003, 01:31 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I do not understand what you mean by that.



Sal
 
Old December 6th, 2003, 05:35 AM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending a Table to a Catalog using ADOX ShoeBucket Access VBA 3 June 15th, 2007 01:58 PM
Catalog for the products FT BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 0 May 19th, 2007 05:41 AM
ADOX speedlearner BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 June 23rd, 2006 04:15 PM
converting Access 2000 views to Sql views matta Classic ASP Professional 1 January 26th, 2005 03:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.