Change access table description

I have an Access DB where the table names are not helpful (e.g."ph"), but
the DESCRIPTION is friendly (e.g. "Public Health").
The descriptions have been put in by directly editing the Access interface
and right-clicking the table, selecting "Properties", and entering text into
the "Description" box.

I want to be able to do this programmatically:
A table description (on localhost) can be found using

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Provider="Microsoft.Jet.OLEDB.4.0"
Conn.Open (Server.Mappath(database_pathname))

Set TablesSchema = Conn.OpenSchema(adSchemaTables)
Do While Not TablesSchema.EOF
Response.Write TablesSchema("TABLE_NAME") & ", " &
TablesSchema("DESCRIPTION")
TablesSchema.MoveNext
Loop

But how do you set the DESCRIPTION to a new value? Using
if TablesSchema("TABLE_NAME")="ph" then TablesSchema("DESCRIPTION")="Public
Health - new"
i get
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype

I've tried using ADOX, but it doesn't seem to be able to access the
"DESCRIPTION".

Thanks in advance
Giles

Re: Change access table description

Giles wrote:
> I have an Access DB where the table names are not helpful (e.g."ph"),
> but the DESCRIPTION is friendly (e.g. "Public Health").
> The descriptions have been put in by directly editing the Access
> interface and right-clicking the table, selecting "Properties", and
> entering text into the "Description" box.
>
> I want to be able to do this programmatically:
> A table description (on localhost) can be found using
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Provider="Microsoft.Jet.OLEDB.4.0"
> Conn.Open (Server.Mappath(database_pathname))
>
> Set TablesSchema = Conn.OpenSchema(adSchemaTables)
> Do While Not TablesSchema.EOF
> Response.Write TablesSchema("TABLE_NAME") & ", " &
> TablesSchema("DESCRIPTION")
> TablesSchema.MoveNext
> Loop
>
> But how do you set the DESCRIPTION to a new value? Using
> if TablesSchema("TABLE_NAME")="ph" then
> TablesSchema("DESCRIPTION")="Public Health - new"
> i get
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation
> of the provider, or of the selected locktype
>
> I've tried using ADOX, but it doesn't seem to be able to access the
> "DESCRIPTION".
>
Unfortunately, that is an Access-defined property that is not exposed by the
Jet OLEDB provider. You could use DAO to get at that property, but DAO is
single-threaded and should be avoided in ASP or other server-based
applications. An automating Access to get at the property is definitely not
recommended: http://support.microsoft.com/default.aspx?scid=kb;en-us;2577 57

I suggest creating a table in your database with two columns: TableName and
TableDescription.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"