I want to fill a ComboBox with tables from an Access DB 
Author Message
 I want to fill a ComboBox with tables from an Access DB

I want to read existing tables and SQL from a Access database in VB!!

STEP 1:

I try to call a 'Private Function' like this one and it runs!
///
Private Function Call_Connection(DBPath As String) As Boolean

   On Error GoTo gErr

   Dim db As Connection
   Set db = New Connection

   db.CursorLocation = adUseClient
   db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" + DBPath

   Call_Connection = True

   Exit Function
gErr:
   Call_Connection = False
End Function
///

STEP 2:

When I come back with the 'Call_Connection = True' result, I want to fill
a ComboBox named 'ComboTables' - but I don't know how??

STEP 3:

The next step is to click a tablename in the filled ComboBox
('ComboTables') to open it and list its tablefields in a Listbox!

Please help, I'm a beginner with ADO programming in VB6!

--
Posted via CNET Help.com
http://www.*-*-*.com/



Sat, 20 Apr 2002 03:00:00 GMT  
 I want to fill a ComboBox with tables from an Access DB

Quote:

> I want to read existing tables and SQL from a Access database in VB!!

> STEP 1:

> I try to call a 'Private Function' like this one and it runs!
<snip>
> STEP 2:

> When I come back with the 'Call_Connection = True' result, I want to fill
> a ComboBox named 'ComboTables' - but I don't know how??

> STEP 3:

> The next step is to click a tablename in the filled ComboBox
> ('ComboTables') to open it and list its tablefields in a Listbox!

I have used the code you posted, and added more code.  Please move your
declaration from the Function Call_Connection to the module's General
declarations area:

   Private db As Connection

Next, use this code after you have opened the Connection - it fills your
ComboBox with Table names.  This code retrieves Schema information for
the db;  we use "If/Then" to screen out the system tables of the Access
mdb.

   Dim rstSchema As ADODB.Recordset

   Set rstSchema = db.OpenSchema(adSchemaTables)
   Do Until rstSchema.EOF
        If rstSchema!TABLE_TYPE = "TABLE" Then
            If Left(rstSchema!TABLE_NAME, 4) <> "MSys" Then            
                ComboTables.AddItem rstSchema!TABLE_NAME
            End If
        End If
        rstSchema.MoveNext  
   Loop

   rstSchema.Close

Last, you can use this code to display the field names for the table in
a ListBox (named List1 in the code).  Put this code into the ComboTables
_Click event, so when user selects a Table in ComboTables, the list of
fields is immediately displayed:

    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim fld As ADODB.Field

    List1.Clear

    strSQL = "SELECT * FROM " & ComboTables
    rst.Open strSQL, db, adOpenStatic, adLockReadOnly    
    For Each fld In rst.Fields
        List1.AddItem fld.Name
    Next fld

    rst.Close

Also, please add this to your form's _Unload event:

   db.Close
   Set db = Nothing

--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"

"What's so funny 'bout peace, love & understanding?"
     - Nick Lowe



Sun, 21 Apr 2002 03:00:00 GMT  
 I want to fill a ComboBox with tables from an Access DB
You need to use the OpenSchema command to bring back the list of
tables as a recordset.  Once you've done that you can then load them
into your combo box by looping through the recordset.
In your code, you will have to move the Connection Object out of your
function and into a more public position as currently it is valid only
WITHIN your function.  It ceases to exist outside the function.

Code similar to this should help ...... (db is the database)

    Dim rsT As ADODB.Recordset
    Dim db As Connection

    Set db = New Connection

    db.CursorLocation = adUseClient
    db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" + DBPath

    Set rsT = New ADODB.Recordset
    '
    '  Use the Database Schema to find out about the tables...
    '
'    MsgBox "Opening Schema"
    Set rsT = db.OpenSchema(adSchemaTables)
    rsT.Filter = "Table_Type='Table'"
   if rst.eof = false and rst.bof = false then
        rst.movefirst
        do until rst.eof
                cboTables.additem rst![Table_Name]
                rst.movenext
        loop
   end if
'
'  clean up - we don't need the recordset any longer
'
   rst.close
   set rst=nothing

Quote:

>I want to read existing tables and SQL from a Access database in VB!!

>STEP 1:

>I try to call a 'Private Function' like this one and it runs!
>///
>Private Function Call_Connection(DBPath As String) As Boolean

>   On Error GoTo gErr

>   Dim db As Connection
>   Set db = New Connection

>   db.CursorLocation = adUseClient
>   db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" + DBPath

>   Call_Connection = True

>   Exit Function
>gErr:
>   Call_Connection = False
>End Function
>///

>STEP 2:

>When I come back with the 'Call_Connection = True' result, I want to fill
>a ComboBox named 'ComboTables' - but I don't know how??

>STEP 3:

>The next step is to click a tablename in the filled ComboBox
>('ComboTables') to open it and list its tablefields in a Listbox!

>Please help, I'm a beginner with ADO programming in VB6!

>--
>Posted via CNET Help.com
>http://www.help.com/



Wed, 24 Apr 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Filling a ComboBox with data from DB

2. Best way to fill a ComboBox from SQL Table

3. need to fill a combobox from a table

4. need to fill a combobox from a table

5. need to fill a combobox from a table

6. Filling a Secondary Combobox based on information from a primary combobox

7. VB ComboBox filled with Access Query Results

8. Copy Access/Oracle table from one DB to another DB.

9. MS Access DB - Copy Table A from Database A to Table A Database B

10. How to reference one field of Access DB to fill cbo

11. Filling a List Box with an Access DB?

12. How to copying from one DB table to another DB table

 

 
Powered by phpBB® Forum Software