Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
|
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 9th, 2005, 12:17 PM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default read field description and display on webpage

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/>"
 
Old June 9th, 2005, 01:11 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old June 9th, 2005, 01:41 PM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default

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
 
Old June 9th, 2005, 03:03 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I couldn't resist, so I tried it out in VB6. This works:
Code:
    Dim catDB  As ADOX.Catalog
    Set catDB = CreateObject("ADOX.Catalog")
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Test.mdb"

    Dim myColumn As ADOX.Column

    Set myColumn = catDB.Tables(5).Columns(0)
    Dim myProperty As Property
    MsgBox ("Description is " & myColumn.Properties("Description"))
    Then I thought that maybe it works in VB6 and not in ASP so I tried this:
Code:
Dim catDB  'As ADOX.Catalog
Code:
Set catDB = Server.CreateObject("ADOX.Catalog")
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.mdb"

Dim myColumn ' As ADOX.Column
Set myColumn = catDB.Tables(5).Columns(0)

Response.Write("Description is " & myColumn.Properties("Description"))

Response.Write ("Description is " & catDB.Tables(5).Columns.Item("Description").Properties("Description"))
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.
 
Old June 9th, 2005, 06:33 PM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wpiass Send a message via MSN to wpiass Send a message via Yahoo to wpiass
Default

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
 
Old June 11th, 2005, 05:59 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
quote:but I get the same error I've been getting with the catDB.Tables(5).Columns.Item("Description").Proper ties("Description") command:
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.
 
Old October 3rd, 2005, 08:37 PM
Registered User
 
Join Date: Oct 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old October 5th, 2005, 06:41 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
display count results and image in webpage carswelljr SQL Language 2 April 19th, 2007 09:44 AM
Display icon on a webpage rroohhiitt C# 3 February 24th, 2006 08:12 PM
display a pdf in a webpage crmpicco Classic ASP Basics 3 February 8th, 2005 09:59 AM
How to display description of default values Braja Nayak VS.NET 2002/2003 0 September 8th, 2003 04:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.