Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 9th, 2005, 12:17 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
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
biomedbz@hotmail.com



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/>"
Reply With Quote
  #2 (permalink)  
Old June 9th, 2005, 01:11 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #3 (permalink)  
Old June 9th, 2005, 01:41 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
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
Reply With Quote
  #4 (permalink)  
Old June 9th, 2005, 03:03 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #5 (permalink)  
Old June 9th, 2005, 06:33 PM
Registered User
 
Join Date: Jan 2005
Location: , CA, USA.
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
Reply With Quote
  #6 (permalink)  
Old June 11th, 2005, 05:59 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #7 (permalink)  
Old October 3rd, 2005, 08:37 PM
Registered User
 
Join Date: Oct 2005
Location: , , .
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

Reply With Quote
  #8 (permalink)  
Old October 5th, 2005, 06:41 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.