[AccessD] Walk the DB Using ADO

Think I can do this in list.  Here are the three relevant functions you
should be able to get what you need from them.

Private Sub lstTables_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Long
Me.lstFields.Clear
If DBConnect(cnn) Then
    Set rs = New ADODB.Recordset
    rs.Open Me.lstTables.List(Me.lstTables.ListIndex), cnn,
adOpenKeyset, adLockReadOnly, adCmdTableDirect
    For i = 0 To rs.Fields.Count - 1
        Me.lstFields.AddItem rs.Fields(i).Name
    Next i
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End If
End Sub
Function GetDBTables()
On Error GoTo ErrorHandler
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
If DBConnect(cnn) Then
    Set rs = cnn.OpenSchema(adSchemaTables)
    Me.lstTables.Clear
    rs.MoveFirst
    Do Until rs.EOF = True
        If Me.optTables Then
            If rs.Fields("TABLE_TYPE") = "TABLE" Then
Me.lstTables.AddItem rs.Fields("TABLE_NAME")
        Else
            If rs.Fields("TABLE_TYPE") = "VIEW" Then
Me.lstTables.AddItem rs.Fields("TABLE_NAME")
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End If
Exit Function

ErrorHandler:
Err.Clear
End Function
Function DBConnect(ByRef cnn As ADODB.Connection) As Boolean
On Error GoTo ErrorHandler
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
If Dir(Me.txtWorkGroupPath) <> "" Then
    cnn.Properties("Jet OLEDB:System database").Value =
Me.txtWorkGroupPath
    cnn.Open Me.txtDBPath, Me.txtUserName, Me.txtPassword
Else
    cnn.Open Me.txtDBPath
End If
DBConnect = True
Exit Function

ErrorHandler:

Err.Clear
DBConnect = False
End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Tuesday, July 05, 2011 3:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Walk the DB Using ADO

Does it require that I have VB6 installed? I suppose I could do that,
but at
the moment I have only the .NET stuff installed. Sounds like a good
add-in,
though.

A.

On Tue, Jul 5, 2011 at 4:07 PM, Drew Wutka <DWUTKA at marlow.com> wrote:

> Would you like me to send my VB6 Add-on to you off list, which lets me
> create 'data classes'?  It has code to pull the tables and fields from
a
> data source using ADO.
>
> Drew
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.