Learning a Field's Properties Using SQL

**Tom van Stiphout** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

On Sat, 10 May 2008 19:48:52 -0700 (PDT), stevew <mktg@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.Tabl edefs("SomeTabl e").Fields("Som eField").Proper ties("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

**stevew** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

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.Tabl edefs("SomeTabl e").Fields("Som eField").Proper ties("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

**lyle fairfield** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

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_MAXIM UM_LENGTH.

Public Function GetColumnInform ation(ByVal Table$, ByVal Column$)
Dim ColumnInformati on As ADODB.Recordset
Dim Iterator&
Set ColumnInformati on = _
CurrentProject. Connection.Open Schema(adSchema Columns, Array(Empty,
Empty, Table, Column))
With ColumnInformati on
If Not .EOF Then
For Iterator = 0 To .Fields.Count - 1
Debug.Print .Fields(Iterato r).Name & ": "
& .Fields(Iterato r).Value
Next Iterator
End If
End With
End Function

Sub test()
Debug.Print GetColumnInform ation("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_POSITIO N: 2
COLUMN_HASDEFAU LT: False
COLUMN_DEFAULT:
COLUMN_FLAGS: 4
IS_NULLABLE: False
DATA_TYPE: 129
TYPE_GUID:
CHARACTER_MAXIM UM_LENGTH: 50
CHARACTER_OCTET _LENGTH: 50
NUMERIC_PRECISI ON:
NUMERIC_SCALE:
DATETIME_PRECIS ION:
CHARACTER_SET_C ATALOG: master
CHARACTER_SET_S CHEMA: dbo
CHARACTER_SET_N AME: iso_1
COLLATION_CATAL OG: master
COLLATION_SCHEM A: dbo
COLLATION_NAME: SQL_Latin1_Gene ral_CP1_CI_AS
DOMAIN_CATALOG:
DOMAIN_SCHEMA:
DOMAIN_NAME:
DESCRIPTION:
COLUMN_LCID: 1033
COLUMN_COMPFLAG S: 196609
COLUMN_SORTID: 52
COLUMN_TDSCOLLA TION: ?Ð
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.Tabl edefs("SomeTabl e").Fields("Som eField").Proper ties("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

**rkc** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

stevew wrote:

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?

Open a recordset and examine the properties of the Field object in
question. Doesn't matter if it's an ADODB.Recordset or DAO.Recordset.
Doesn't even matter if it's an empty recordset as long as the field in
question is included in the query string.

**lyle fairfield** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

Enough with these simple, smart answers, RKC. You're making the rest
of us look bad. Well, you're making me look bad.

So you think that this simple one liner is better than all that crap I
posted, eh?

Debug.Print CurrentProject. Connection.Exec ute("SELECT [Name] FROM
Schools WHERE 1 = 2").Fields(0).D efinedSize

' shows 50

Hmmmm ... me too!

On May 11, 11:27 am, rkc <r...@rkcny.yab ba.dabba.do.com wrote:

stevew wrote:

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?

>
Open a recordset and examine the properties of the Field object in
question. Doesn't matter if it's an ADODB.Recordset or DAO.Recordset.
Doesn't even matter if it's an empty recordset as long as the field in
question is included in the query string.

**rkc** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

lyle fairfield wrote:

Enough with these simple, smart answers, RKC. You're making the rest
of us look bad. Well, you're making me look bad.
>
So you think that this simple one liner is better than all that crap I
posted, eh?
>
Debug.Print CurrentProject. Connection.Exec ute("SELECT [Name] FROM
Schools WHERE 1 = 2").Fields(0).D efinedSize
>
' shows 50
>
Hmmmm ... me too!

You're the Master. I am but a simple student.

**stevew** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

Thanks to both for both solutions. The longer treatment just as useful
as an added routine in the DB's class where, with one extra argument,
the client program can ask for any property.

I don't believe I've been here before. Very responsive forum.

Thanks again
Steve

**Larry Linson** · Jun 27 '08, 07:24 PM

Re: Learning a Field's Properties Using SQL

"lyle fairfield" <lyle.fairfield @gmail.comwrote

Enough with these simple, smart answers, RKC.
You're making the rest of us look bad. Well,
you're making me look bad.

Forgive the Grasshopper, Master. (After he re-rakes pattern in the sand in
the courtyard).

Larry

Learning a Field's Properties Using SQL

Learning a Field's Properties Using SQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment