Another VB & Access DB problem

[Rupali]

I am trying to add a field to an Access table through VB.
I need to find out first whether that field exists in that
table or not. and if that field does not exist in the
table then only will I alter the table. I am using this
code..
Set rs1 = con.OpenSchema(adSchemaTables)
Do While Not rs1.EOF
If (rs1!Table_name) = table_name Then
Set rs3 = con.OpenSchema(adSchemaColumns)
Do While Not rs3.EOF
If rs3!Column_Name = field_name Then

but rs3 recordset is getting column from each and every
table in the database. I need to get the columns of the
particular table only.
Please give the solution for my problem.
Thanks

 

[Graham Mandeno]

Hi Rupali

The second argument of OpenSchema is an array of query restraints for the
given schema you are querying. For adSchemaColumns, this array contains
four members. They are:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

You can specify your table_name argument to limit the columns to only one
table, line this:

Set rs3 = con.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, table_name, Empty))

By doing this, you have made the entire outer loop (involving rs1)
redundant, because you are going directly to the required table.

In fact, you can lose the inner loop also, by specifying the column_name as
well:

Set rs1 = con.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, table_name, field_name))
If rs1.EOF Then
Debug.Print field_name; " does not exist in "; table_name
Else
Debug.Print rs3!column_name
End If

 

[TC]

Could you just try to add the field, & trap the error that happens if it
already exists?

HTH,
TC