Table and Column references in VB

[lenin]

Hi,
I want to find out the Table and Column names in an Access database, referenced via a DSN. If the table is a certain name then I want to write out the columns, however I find when I do this that there are additional "column" names provided which are not part of my definition. Also I have no views / queries in the database so this isn't the source also.

e.g. repl_test1 table
ID AutoNumber
Number Number
On_Hold Yes/No
Date_Start Date/Time

with the following source:


Private Sub Form_Load()

Dim lc_connection As ADODB.Connection
Dim lrs_tables As ADODB.Recordset
Dim lrs_columns As ADODB.Recordset

Dim ls_Connection As String

Set lc_connection = New ADODB.Connection
lc_connection.Open "DSN=TONY2"

Set lrs_tables = lc_connection.OpenSchema(adSchemaTables)

Do Until lrs_tables.EOF
If lrs_tables!table_name = "repl_test1" Then
Set lrs_columns = lc_connection.OpenSchema(adSchemaColumns)
Do Until lrs_columns.EOF
Debug.Print "Column Name: " & lrs_columns!COLUMN_NAME
lrs_columns.MoveNext
Loop
End If
lrs_tables.MoveNext
Loop

End Sub


Produces:
Column Name: Data
Column Name: ID
Column Name: ID
Column Name: Number
Column Name: on_hold
Column Name: date_start


As you can see two extra columns. If someone could answer:

1. Where do these come from, and
2. how do I get rid of them

Any help appreciated.

All right, since no else seems to have any idea, I'll throw
in my $0.030303 worth. I cannot replicate your problem, so
I'm taking a stab in the dark, more to try to help figure
out where to go next, than anything else.

1. The term DATA is probably a table signifier. As
opposed to a query or a view. You can test this by running
the code on several different tables. If "DATA" keeps
coming up first, you can ignore it.

2. The term ID is coming up twice. My guess is that the
first time it is identifying a keyed index field. change
the datatype from autonumber to text, remove the index, and
try it again. If the 2nd iteration of ID goes away, that's
it.

3. It is possible that the Data term actually references
the index fields. You might not get a DATA field if there
are no indexes.

Good Luck
DerFarm

[lenin]

Thanks DerFarm,
seems like a reasonable analysis. I'll have a go, and let you know.

lenin.