SQL statement to report a tables"s attribute types

Is there an SQL statement that will search a table's attribute types
and report if there are any text, Blob, or memo fields?

Are there any other Access data types that can hold text information?

Thanks

Re: SQL statement to report a tables"s attribute types

To answer your specific questions:

1. There isn't a simple way to use a SQL statement to get the column info.

2. Hyperlink might be considered as text. (Internally in Access, it's
actually a memo with an attribute set.)

Here's an example of how to list the fields in a table using code, along
with their properties (including description):
http://allenbrowne.com/func-06.html

You can also use ADOX to list the columns in a table:
http://allenbrowne.com/func-ADOX.html#ShowAllTables

And here's a reference of the various field type names and constants used by
the various libraries:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"leegold58" wrote in message
news:1192098555.764793.166890@y42g2000hsy.googlegroups.com.. .
> Is there an SQL statement that will search a table's attribute types
> and report if there are any text, Blob, or memo fields?
>
> Are there any other Access data types that can hold text information?
>
> Thanks

Re: SQL statement to report a tables"s attribute types

leegold58 wrote in news:1192098555.764793.166890
@y42g2000hsy.googlegroups.com:

> Is there an SQL statement that will search a table's attribute types
> and report if there are any text, Blob, or memo fields?
>
> Are there any other Access data types that can hold text information?
>
> Thanks

These functions might be helpful to you:

----------------
Public Function TextColumns$(ByVal TableName$)
' requires a reference to ado
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, TableName))
r.Filter = "DATA_TYPE = " & adBSTR _
& " OR DATA_TYPE = " & adChar _
& " OR DATA_TYPE = " & adGUID _
& " OR DATA_TYPE = " & adVarChar _
& " OR DATA_TYPE = " & adVarWChar _
& " OR DATA_TYPE = " & adWChar
While Not r.EOF
TextColumns = TextColumns & r.Fields("COLUMN_NAME").Value
r.MoveNext
If Not r.EOF Then TextColumns = TextColumns & ", "
Wend
End Function

Sub test()
' employees table in Northwind
Debug.Print TextColumns("Employees")
End Sub

' shows

Address, City, Country, Extension, FirstName, HomePhone, LastName, Notes,
PostalCode, Region, Title, TitleOfCourtesy
-----------------

-----------------
Public Function Columns$(ByVal TableName$)
' requires a reference to ado
Columns = CurrentProject.Connection.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, TableName)).GetString(adClipString)
End Function

Public Sub TestColumns()
' employees table in Northwind
Debug.Print Columns("Employees")
End Sub

' shows

Employees Address 8 0 106 -1 130 60 120
Street or post-office box.
Employees BirthDate 6 0 122 -1 7
0
Employees City 9 0 106 -1 130 15 30
Employees Country 12 0 106 -1 130 15 30
Employees EmployeeID 1 0 90 0 3
10 Number automatically
assigned to new employee.
Employees Extension 14 0 106 -1 130 4 8
Internal telephone extension number.
Employees FirstName 3 0 74 0 130 10
20
Employees HireDate 7 0 122 -1 7
0
Employees HomePhone 13 0 106 -1 130 24
48 Phone number includes
country code or area code.
Employees LastName 2 0 74 0 130 20
40
Employees Notes 16 0 234 -1 130 0 0
General information about employee's background.
Employees Photo 15 0 234 -1 128 0 0
Picture of employee.
Employees PostalCode 11 0 106 -1 130 10
20
Employees Region 10 0 106 -1 130 15 30
State or province.
Employees ReportsTo 17 0 122 -1 3
10 Employee's supervisor.
Employees Title 4 0 106 -1 130 30 60
Employee's title.
Employees TitleOfCourtesy 5 0 106 -1 130
25 50 Title used in
salutations.

-----------------


--
lyle fairfield

Re: SQL statement to report a tables"s attribute types

Hi,

Thanks for the help.

But I was looking for SQL to get the info. purely SQL not VB.

Is there a way?

Re: SQL statement to report a tables"s attribute types

leegold58 wrote:

> But I was looking for SQL to get the info. purely SQL not VB.
>
> Is there a way?

As far as I know, not with Jet backend (mdb-file).

--
Roy-Vidar