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 =3D _
CurrentProject.Connection.OpenSchema(adSchemaColumns, Array(Empty,
Empty, Table, Column))
With ColumnInformation
If Not .EOF Then
For Iterator =3D 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: ?=D0
IS_COMPUTED: False
On May 11, 10:16=A0am, stevew <m...@[EMAIL PROTECTED]
> wrote:
> 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...@[EMAIL PROTECTED]
> wrote:
>
>
>
> > On Sat, 10 May 2008 19:48:52 -0700 (PDT), stevew <m...@[EMAIL PROTECTED]
>
> > 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("SomeField").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 =A0 -- i.e., get Access field properties -- but don't know
th=
e
> > >SQL syntax. Can anyone give me a sample?
>
> > >Thank you in advance.
> > >Steve
|
