You could try this code. After you examine the Immediate Window and
decide what information you want, you can modify the function to
return it. You may want CHARACTER_MAXIMUM_LENGTH.
Public Function GetColumnInformation(ByVal Table$, ByVal Column$)
Dim ColumnInformation As ADODB.Recordset
Dim Iterator&
Set ColumnInformation = _
CurrentProject.Connection.OpenSchema(adSchemaColum ns, Array(Empty,
Empty, Table, Column))
With ColumnInformation
If Not .EOF Then
For Iterator = 0 To .Fields.Count - 1
Debug.Print .Fields(Iterator).Name & ": "
& .Fields(Iterator).Value
Next Iterator
End If
End With
End Function
Sub test()
Debug.Print GetColumnInformation("Schools", "Name")
End Sub
This is the result:
TABLE_CATALOG: ESOII
TABLE_SCHEMA: dbo
TABLE_NAME: Schools
COLUMN_NAME: Name
COLUMN_GUID:
COLUMN_PROPID:
ORDINAL_POSITION: 2
COLUMN_HASDEFAULT: False
COLUMN_DEFAULT:
COLUMN_FLAGS: 4
IS_NULLABLE: False
DATA_TYPE: 129
TYPE_GUID:
CHARACTER_MAXIMUM_LENGTH: 50
CHARACTER_OCTET_LENGTH: 50
NUMERIC_PRECISION:
NUMERIC_SCALE:
DATETIME_PRECISION:
CHARACTER_SET_CATALOG: master
CHARACTER_SET_SCHEMA: dbo
CHARACTER_SET_NAME: iso_1
COLLATION_CATALOG: master
COLLATION_SCHEMA: dbo
COLLATION_NAME: SQL_Latin1_General_CP1_CI_AS
DOMAIN_CATALOG:
DOMAIN_SCHEMA:
DOMAIN_NAME:
DESCRIPTION:
COLUMN_LCID: 1033
COLUMN_COMPFLAGS: 196609
COLUMN_SORTID: 52
COLUMN_TDSCOLLATION: ?Ð
IS_COMPUTED: False
On May 11, 10:16*am, stevew <m...@homeware.comwrote:
Tom,
Thank you for responding. Trouble is, the main program has no direct
connection to the DB and is required to send SQL calls to the
component which does. So, for a field called 'Name' in a table called
'Titles" I need to develop a statement such as "SELECT Name.Size FROM
Titles", which, unfortunately, doesn't cut it with Access. But in the
dark recesses of memory I seem to recall that this is doable but
cannot come up with the syntax.
Steve
On May 10, 11:02 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Sat, 10 May 2008 19:48:52 -0700 (PDT), stevew <m...@homeware.com>
wrote:
A table (better: tabledef) has a fields collection, and each field has
a Properties collection, and one of the properties is Size.
?Currentdb.Tabledefs("SomeTable").Fields("SomeFiel d").Properties("Size")
-80
-Tom.
>I'm using SQL within Visual Basic in conjunction with Access
>databases. Say I am about to insert a record with a long string for a
>particlar field. It would be useful to prevent errors to first
>interrogate the DB to learn the maximum permissible length of that
>field, and only then proceed to write the record. I believe I've seen
>that done * -- i.e., get Access field properties -- but don't know the
>SQL syntax. Can anyone give me a sample?
>Thank you in advance.
>Steve