Listing Fields - advice needed

[p byers]

Hello Folks

Below is an extract from an Active Server Page that works OK
(apart from one thing that is !!!!)


It lists fields and info for a table in an Access database.

You will see that it cannot distinguish between 'Text'
and 'Memo'
(at the bottom of the script)

Two questions please.

a) How can I distinguish between 'Text' and 'Memo' ??

b) What is the full list of DataItems revealed by
'adSchemaColumns' ??
Could I have used a loop to reveal the name
and value for each item in rstMetaData ??

Thank you
Pete (Northolt UK)

*********************************************************************
*********************************************************************
Script is below
*********************************************************************
*********************************************************************


dataBaseRootSQL = "c:\inetpub\wwwroot\data\"



cBase = Request.QueryString("b")
cTable = Request.QueryString("t")
Response.Buffer = True

Const adSchemaColumns = 4
Const adSchemaTables = 20
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28

' ADO Constants
Const adPersistXML = 1

Dim cnnSQL, rstMetaData, strSQL
Dim strConnection

Set cnnSQL = Server.CreateObject("ADODB.Connection")
Set rstMetaData = Server.CreateObject("ADODB.Recordset")

' Check that the connection was opened succesfully
'' strConnection = Request.QueryString("p")
''Response.Write (Request.QueryString("p") & "<P>xx<P>")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataBaseRootSQL & "data\" & cBase

Response.Write ("<B>The Base is " & cBase & "</B><P>")
Response.Write ("<B>The Table is " & cTable & "</B><P>")
'' On Error Resume Next
cnnSQL.Open strConnection
If (Len(strConnection) = 0) Or (Err.Number <> 0) Then
Response.ContentType = "text/html"
Response.Write "There was an error with your connection string. Please
try again."
Response.End
End If
On Error GoTo 0

' ********** Get Columns **********
' Set criteria for adSchemaColumns; Get the list of columns from the
database
Set rstMetaData = cnnSQL.OpenSchema(adSchemaColumns)


Set rstMetaData = cnnSQL.OpenSchema(adSchemaColumns,Array(Empty, Empty,
"" & cTable & ""))



While Not rstMetaData.EOF
cThisField = rstMetaData("COLUMN_NAME")
cThisType = rstMetaData("DATA_TYPE")
cThisDesc = rstMetaData("DESCRIPTION")
if cThisType = 11 then
cType = "Logical (True/False or Yes/No)"
end if
if cThisType = 7 then
cType = "Date + Time"
end if
if cThisType = 130 then
cType = "String (Text or Memo)"
end if
if cThisType = 5 then
cType = "Number"
end if
if cThisType = 3 then
cType = "Auto Incrementing Number"
end if
'' Response.Write (cThisType & "^^^" & cThisField & "<BR>")
Response.Write ("<TR><TD>" & cThisField & "</TD><TD>" & cType &
"</TD><TD>" & cThisDesc & "</TD></TR>")
rstMetaData.MoveNext
Wend

 

[Daniel Crichton]

p wrote on Fri, 24 Apr 2009 06:23:19 +0100:

Hello Folks

Below is an extract from an Active Server Page that works OK (apart
from one thing that is !!!!)

It lists fields and info for a table in an Access database.

You will see that it cannot distinguish between 'Text'
and 'Memo'
(at the bottom of the script)

Two questions please.

a) How can I distinguish between 'Text' and 'Memo' ??

According to http://www.w3schools.com/ADO/ado_datatypes.asp 130 is neither
Text nor Memo - it's used for Unicode fixed length columns.

201 and 203 should be Access Memo types, and 200 and 202 should be Access
Text types (in each case it will depend on the version of Access, as 2000+
changed all string datatypes to Unicode).

 

[p byers]

p wrote on Fri, 24 Apr 2009 06:23:19 +0100:







According to http://www.w3schools.com/ADO/ado_datatypes.asp 130 is neither
Text nor Memo - it's used for Unicode fixed length columns.

201 and 203 should be Access Memo types, and 200 and 202 should be Access
Text types (in each case it will depend on the version of Access, as 2000+
changed all string datatypes to Unicode).

Thank you for the reply.

When I open a 'Test' table using Access 97 (the Database was created using
'97')

(The table was created using SQL in an Active Server Page)

and look at the Design view, the columns are shown as 'Text' and others as
'Memo'

The result from the script in the Original Post is 130

The mystery deepens!!

Pete (Northolt UK)

 

[Daniel Crichton]

p wrote on Fri, 24 Apr 2009 15:18:49 +0100:

Daniel Crichton wrote:

Thank you for the reply.

When I open a 'Test' table using Access 97 (the Database was created
using '97')

(The table was created using SQL in an Active Server Page)

and look at the Design view, the columns are shown as 'Text' and others
as 'Memo'

The result from the script in the Original Post is 130

Odd. In Acess 97 "Text" is a variable length string (varchar), and "Memo" is
a BLOB, so they should have had their own distinct ADO datatype values
according to that page. I just tested myself on Access 2002, and an Access
97 mdb created from Access 2002, and they both are data_type 130.

I did notice though that rstMetaData("CHARACTER_MAXIMUM_LENGTH") did show
different values. It was 50 for my Text column which was defined as 50
characters, and 0 for the Memo column. Perhaps you could use that to
determine which is which, as a Text column of 0 length would be useless so
would be unlikely to be found in most databases.

 

[Bob Barrows]

Odd. In Acess 97 "Text" is a variable length string (varchar), and
"Memo" is a BLOB, so they should have had their own distinct ADO
datatype values according to that page. I just tested myself on
Access 2002, and an Access 97 mdb created from Access 2002, and they
both are data_type 130.

I did notice though that rstMetaData("CHARACTER_MAXIMUM_LENGTH") did
show different values. It was 50 for my Text column which was defined
as 50 characters, and 0 for the Memo column. Perhaps you could use
that to determine which is which, as a Text column of 0 length would
be useless so would be unlikely to be found in most databases.

Maybe the results would make more sense with ADOX rather than OpenSchema
....

 

[p byers]

Hello Folks

Below is an extract from an Active Server Page that works OK
(apart from one thing that is !!!!)

It lists fields and info for a table in an Access database.

You will see that it cannot distinguish between 'Text'
and 'Memo'
(at the bottom of the script)

Two questions please.

a) How can I distinguish between 'Text' and 'Memo' ??

b) What is the full list of DataItems revealed by
'adSchemaColumns' ??
Could I have used a loop to reveal the name
and value for each item in rstMetaData ??

Thank you
Pete (Northolt UK)

Thank you Daniel - did not know about
"rstMetaData("CHARACTER_MAXIMUM_LENGTH")"
that will answer my question.
I had some idea that there might be an answer along these lines - hence the
reason for Question b)
in the orig inal post

Can a loop be constructed to reveal all the DataItems ?

Thanks Bob - will do some exploration about ADOX

Pete (Northolt UK)

 

[Daniel Crichton]

p wrote on Sat, 25 Apr 2009 06:37:44 +0100:

p byers wrote:

Thank you Daniel - did not know about
"rstMetaData("CHARACTER_MAXIMUM_LENGTH")"

I only found out about it by running your code in the VB6 IDE and looking at
the local variables window which lets you drill down into the recordset
properties and values

that will answer my question.
I had some idea that there might be an answer along these lines - hence
the reason for Question b)
in the orig inal post

Can a loop be constructed to reveal all the DataItems ?

Something like

For i = 0 to rstMetaData.Fields.Count - 1
Response.Write Server.HTMLEncode(rstMetaData(i).Name & " : " &
rstMetaData(i).Value) & "<br>"
Next

should work.

 

[p byers]

Something like

For i = 0 to rstMetaData.Fields.Count - 1
Response.Write Server.HTMLEncode(rstMetaData(i).Name & " : " &
rstMetaData(i).Value) & "<br>"
Next

should work.

Thank you Dan - will give that a try

Pete (Northolt UK)