ADO/ADOX Gurus out there:

I cannot seem to find any way to read the description of an existing field in an existing Access DB. The frustrating part is that I can certainly write one when creating a new field and appending it to a table, but I can never seem to read the description for an existing field.

The following code will work and display the description on the webpage, but only because it reads it from the object it just appended. If I were to close everything, set it to nothing, then open a new object, it cannot be read. One would think that if you can write it, you can read it...am I wrong here?!?

I have tried any number of combinations of objects, properties, and collections from ADO and ADOX. Has anyone actually been able to do this?

Your help is deeply appreciated,
Brian
[email protected]



dim objConnDatabase,objCat,objTable

Set objConnDatabase = Server.CreateObject("ADODB.Connection")
    objConnDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConnDatabase

Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConnDatabase

Set objTable = CreateObject("ADOX.Table")

with objTable
    .Name = "newtable"
    .ParentCatalog = objCat
end with

with objTable
    with .Columns
        .Append "Field_A",adDouble
        .Item("Field_A").Properties("Description") = "This is a field of numbers"
        response.write "<b>The ""<i>Field_A</i>"" field has been added!<br/>"
    end with
end with

response.write "description=" & .Item("Field_A").Properties("Description") & "<br/>"

Does your description end up in Access?? Maybe the changes aren't persisted.

Also, this looks odd:

response.write "description=" & .Item("Field_A").Properties("Description") & "<br/>"


Shouldn't that be within in With block?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar:

You're right about the last line. I cut and pasted from within a With block. The standalone line would look like:

response.write "description=" & objTable.Columns.Item("Field_A").Properties("Descr iption") & "<br/>"

Anywho, I am sure that the changes are persisted. It doesn't really matter if those changes were persisted or not though since I am looking to read the description of an existing field anyway. So say, forget my example and just imagine if I had an existing database with fields and descriptions and I want to read the descriptions and display them on a page. What would you suggest?

Regards,
Brian

I couldn't resist, so I tried it out in VB6. This works:     Then I thought that maybe it works in VB6 and not in ASP so I tried this: That also works from an ASP page....

I don't see that much of a difference compared to your code, other than the way I am creating the table and column object. I tried referring to them with their name instead of their ordinal position, and that worked too.

Can you try this out?

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar:

The Response.Write("Description is " & myColumn.Properties("Description")) line works, but I get the same error I've been getting with the catDB.Tables(5).Columns.Item("Description").Proper ties("Description") command:

Error Type:
ADOX.Columns (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.


If I use the first one, I guess I would need a loop to display the descriptions for every field in a table. Maybe something like:

sys = "common"
i=0

for each objColumn in objCat.Tables(sys).Columns
    response.write "Field" & "(" & i & ") - " & objColumn.Name & ": " & objColumn.Properties("Description") & "<br/>"
    i=i+1
next



I think this will work for me. Thanks a lot!

Brian

I am not surprised. That code only works on my own database. Tables(5) refers to to the 6th table in the database (in my case, it was the first user table, as apparently there are 5 system tables)

Columns.Item("Description") then refers to a column called Description. I could have chosen a better column description to make this example easier to understand by not using Description as both the column name and the description you're trying to read.

Anyway, I think you could do something like the following without looping through all the tables or columns:

catDB.Tables("YourTableName").Columns.Item("YourCo lumnName").Properties("Description")

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I'm having the opposite problem to this one - I can read the description for any field, but can't find a way to modify it.

I'm using ASP/VBscript and Access.

Obviously, when you open an ADOX.Catalog, the fields are read-only. Is there a way to use append (or similar) to modify the description in existing fields?

Thanks in advance!

Ben

Hi Ben,

Take a look at this article: http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=143

It explains how to create a brand new Access database through ADOX, so it may answer your question.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.