why does this only returns the first field

Hi,
if got a little testing-database with about 20 recordsets into it. i want them to be read out but the code below gives only the first recordset in the fieldproperty. what have i done wrong?

thanks
akademos

private Sub Form_Load()
Dim sql as string

sql = "select * from roman"
set theRecordset = new ADODB.Recordset
theConn.ConnectionString = "Provider = SQLOLEDB.1;Initial Catalog = PerryRhodanEnt;User ID =sa"
theConn.Open

theRecordset.Open sql, theConn, adOpenKeyset, adLockOptimistic, adCmdText
for Each Field In theRecordset.Fields
If Not (IsNull(Field.Value)) then
MsgBox Field.Value
End If
next
End Sub

You need to loop through the records in your recordset such as

DO while not theRecordset.eof
my code here
theRecordset.movenext
loop

Here is a sample of printing DattaBase structure.

private Sub cmdPrint_Click()
Dim adoConnection as ADODB.Connection
Dim adoRsFields as ADODB.Recordset
Dim sConnection as string
Dim sCurrentTable as string
Dim sNewTable as string
on error resume next

' get the name and location of the Data Base
CommonDialog1.DialogTitle = "Select the DataBase to map"
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" then Exit Sub

set adoConnection = new ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=" & CommonDialog1.FileName
adoConnection.Open sConnection
set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)
sCurrentTable = ""
sNewTable = ""

' get the location of the output print file
CommonDialog1.DialogTitle = "Define the output file"
CommonDialog1.CancelError = true
CommonDialog1.ShowSave ' Where to output the file
If CommonDialog1.FileName = "" then Exit Sub
Open CommonDialog1.FileName for Output as #1
print #1, " "
print #1, Now & " " & sConnection
print #1, ""

Do Until adoRsFields.EOF
sCurrentTable = adoRsFields!TABLE_NAME
If (sCurrentTable <> sNewTable) then
sNewTable = adoRsFields!TABLE_NAME
print #1, ""
print #1, "Table: " & adoRsFields!TABLE_NAME
End If
If Left(UCase(adoRsFields!TABLE_NAME), 7) <> "MSYSMOD" then
print #1, Tab(8); " Field: " & adoRsFields!COLUMN_NAME

' the following statements will print Field characteristics

' Printer.print Tab(20); "TABLE_CATALOG: " & adoRsFields!TABLE_CATALOG
' Printer.print Tab(20); "TABLE_SCHEMA : " & adoRsFields!TABLE_SCHEMA
' Printer.print Tab(20); "TABLE_NAME : " & adoRsFields!TABLE_NAME
' Printer.print Tab(20); "COLUMN_NAME : " & adoRsFields!COLUMN_NAME
' Printer.print Tab(20); "COLUMN_GUID : " & adoRsFields!COLUMN_GUID
' Printer.print Tab(20); "COLUMN_PROPID: " & adoRsFields!COLUMN_PROPID
' Printer.print Tab(20); "ORDINAL_POSITION: " & adoRsFields!ORDINAL_POSITION
' Printer.print Tab(20); "COLUMN_HASDEFAULT: " & adoRsFields!COLUMN_HASDEFAULT
' Printer.print Tab(20); "COLUMN_DEFAULT: " & adoRsFields!COLUMN_DEFAULT
' Printer.print Tab(20); "COLUMN_FLAGS: " & adoRsFields!COLUMN_FLAGS
' Printer.print Tab(20); "IS_NULLABLE: " & adoRsFields!IS_NULLABLE
' Printer.print Tab(20); "DATA_TYPE: " & adoRsFields!DATA_TYPE
' Printer.print Tab(20); "TYPE_GUID: " & adoRsFields!TYPE_GUID
' Printer.print Tab(20); "CHARACTER_MAXIMUM_LENGTH: " & adoRsFields!CHARACTER_MAXIMUM_LENGTH
' Printer.print Tab(20); "CHARACTER_OCTET_LENGTH: " & adoRsFields!CHARACTER_OCTET_LENGTH
' Printer.print Tab(20); "NUMERIC_PRECISION: " & adoRsFields!NUMERIC_PRECISION
' Printer.print Tab(20); "NUMERIC_SCALE: " & adoRsFields!NUMERIC_SCALE
' Printer.print Tab(20); "DATETIME_PRECISION: " & adoRsFields!DATETIME_PRECISION
' Printer.print Tab(20); "CHARACTER_SET_CATALOG: " & adoRsFields!CHARACTER_SET_CATALOG
' Printer.print Tab(20); "CHARACTER_SET_SCHEMA: " & adoRsFields!CHARACTER_SET_SCHEMA
' Printer.print Tab(20); "CHARACTER_SET_NAME: " & adoRsFields!CHARACTER_SET_NAME
' Printer.print Tab(20); "COLLATION_Catalog: " & adoRsFields!COLLATION_CATALOG
' Printer.print Tab(20); "COLLATION_SCHEMA: " & adoRsFields!COLLATION_SCHEMA
' Printer.print Tab(20); "COLLATION_NAME: " & adoRsFields!COLLATION_NAME
' Printer.print Tab(20); "DOMAIN_CATALOG: " & adoRsFields!DOMAIN_CATALOG
' Printer.print Tab(20); "DOMAIN_SCHEMA: " & adoRsFields!DOMAIN_SCHEMA
' Printer.print Tab(20); "DOMAIN_NAME: " & adoRsFields!DOMAIN_NAME
' Printer.print Tab(20); "DESCRIPTION: " & adoRsFields!Description
End If
adoRsFields.MoveNext
Loop
Close #1
adoRsFields.Close
set adoRsFields = nothing
adoConnection.Close
set adoConnection = nothing
End Sub





John G