ADO equivalent of DAO 'Database' object

Hi,

In the (bad) old days you could easily return the names of tables in a database through the MS Jet Workspace object.

I used to do this if I wanted the user to be able to browse for a specific table in an MS Access database...


**********************************************************
 '-- For MS Access connection
 Private wrkJet As Workspace

 Private myDB as Database
 Private myTbl As TableDef

 '-- Connect
 Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
 Set myDB = wrkJet.OpenDatabase(Database_Path, True)

 '-- Retrieve table names and show to user
 For Each myTbl In myDB.TableDefs
    ' Load to user interface here
 Next

**********************************************************

Although now I successfully use ADO to connect to SQL Server to retrieve record sets, I can not immediately see the equivalent for returning table names from an SQL Server database using the ADO connection & command objects.

Can anyone provide an example please?


Cheers!

G_EvansAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GeoffKellConnect With a Mentor Commented:
Use the OpenSchema method of the Connection object.

In example assume mdbConnection is your connection and rs is a recordset object

Regards
GK


    Set rs = mdbConnection.OpenSchema(adSchemaTables)

    Do Until rs.EOF
        If rs.Fields("TABLE_TYPE").Value = "TABLE" Or rs.Fields("TABLE_TYPE").Value = "VIEW" Then
            Debug.Print rs.Fields("TABLE_NAME").Value
        End If
        rs.MoveNext
    Loop
0
 
VincentLawlorCommented:
Use ADOX to retrieve this information

Vin
0
 
TimCotteeHead of Software ServicesCommented:
Geoff's suggestion of OpenSchema is the better way to go. ADOX adds additional components to be distributed for little benefit if this is all you want to do.
0
 
G_EvansAuthor Commented:
Cheers - as I say couldn't find an example of that in the online help - Was giving me a right headache!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.