Getting table names from DB to recorset

Hi,
Can anyone please tell me how to get my DB table names into a recordset. I'm using the following code to create a recordset:

Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
rs.Open SQL, conn, adOpenKeyset, adLockOptimistic

This works fine and I can get all the info that I need from the DB, but how do I get the relevant table names aswell??

Any suggestions or help would be greatly appreciated!
cheers,
shovels

use "openschema" method.... just like this..cHeErs :)


Set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)


Example:


Private Sub cmdSchema_Click()

Dim adoConnection As ADODB.Connection

Dim adoRsFields As ADODB.Recordset

Dim sConnection As String

Dim sCurrentTable As String

Dim sNewTable As String

Set adoConnection = New ADODB.Connection

sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb"

adoConnection.Open sConnection

Set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)

sCurrentTable = ""

sNewTable = ""

Do Until adoRsFields.EOF

sCurrentTable = adoRsFields!TABLE_NAME

If (sCurrentTable <> sNewTable) Then

sNewTable = adoRsFields!TABLE_NAME

Debug.Print "Current Table: " & adoRsFields!TABLE_NAME

End If

Debug.Print " Field: " & adoRsFields!COLUMN_NAME

adoRsFields.MoveNext

Loop

adoRsFields.Close

Set adoRsFields = Nothing

adoConnection.Close

Set adoConnection = Nothing

End Sub