How to populate the tables in Access?

[matrik02]

I would like to know how to populate the tables in Access? I have the sample code below but it use DAO.. I want to use ADODB connection, so What reference I can use to populate the access tables into my combo box?

    Dim db As Database
    Dim wrk As Workspace
    Dim tdf As TableDef
    For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" And tdf.Name <> "pass" And tdf.Name <> "KERJA" Then _
       cboTable.AddItem tdf.Name
    Next tdf

[amrita]

SELECT Name 
FROM MSysObjects 
WHERE (Name Not Like "MSys*") 
AND (Type In (1,4,6)) 
ORDER BY Name

(Type 1 is tables in the MDB file, 4 are tables linked through ODBC, and 6
are other linked tables)

Found Solution At:

http://www.microsoft.com/communities...&cr=&sloc=&p=1

[Hack]

The reference would be the ActiveX Data Objects Library.

I suspect you already have that in your project, correct?

[matrik02]

Yes, I already have it. Microsoft Active X Data Object 2.7 Library.

But when I uncheck this reference Microsoft DAO 3.6 Object Library, This cannot work "Dim db As Database" used it. Is there any reference I can replace DAO so that I can populate the tables into my combo box.. I don't want to use DAO anymore to populate the tables into my combo box.

    Dim db As Database
    Dim wrk As Workspace
    Dim tdf As TableDef
    For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" And tdf.Name <> "pass" And tdf.Name <> "KERJA" Then _
       cboTable.AddItem tdf.Name
    Next tdf

[amrita]

Check some good tutorials on ADO with VB.

Then connect to your Access Db with ADO connection and exceute the query provided by me in a previous post to this thread.

You can use the record set object to populate the combo.

[Hack]

But when I uncheck this reference Microsoft DAO 3.6 Object Library, This cannot work "Dim db As Database" used it.

Of course it won't work. You removed the reference which declared Database as a valid variable type.

None of that code, as written, will work.

It has to be rewritten using ADO.

[si_the_geek]

If this is only to work with .mdb files, amrita's advice is the easiest solution - just open a recordset (which you have done before) using that SQL statement.

If it is to work with other database systems too (perhaps SQL Server and Oracle), I would recommend using the connections .OpenSchema method as the source for the recordset. You can see an example of this in the code of my "reserved words checker", link in my signature.