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")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataBaseRootSQL & "data\" & cBase

'' 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
   rstMetaData.MoveNext
Wend

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

Show quoteHide quoteAccording 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).

Daniel Crichton wrote:

Show quoteHide quoteThank 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)

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


Show quoteHide quoteOdd. 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.

Daniel Crichton wrote:
Maybe the results would make more sense with ADOX rather than OpenSchema
....

p byers wrote:

Show quoteHide quoteThank 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)

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


Show quoteHide quoteI 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 ;)

Something like

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

should work.

Daniel Crichton wrote:

Show quoteHide quoteThank you Dan - will give that a try

Pete (Northolt UK)

truncated form POST
Re: server side redirect https => http STILL NOT working
type mismatch
ASP (not asp.net)
How to redirect after ALERT.
Sorting like Amazon DVD system
ignore style
dynamic XLSX files from a web page
History.go with asp.
Is ASP programming still supported in latest visual studio?