why does this only returns the first field

[Akademos]

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

[Z LoveLife]

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

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

[John G Duffy]

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