473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Learning a Field's Properties Using SQL

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
Jun 27 '08 #1
8 1930
On Sat, 10 May 2008 19:48:52 -0700 (PDT), stevew <mk**@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
Jun 27 '08 #2
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
Jun 27 '08 #3
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
Jun 27 '08 #4
rkc
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.
Jun 27 '08 #5
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.Execute("SELECT [Name] FROM
Schools WHERE 1 = 2").Fields(0).DefinedSize

' shows 50

Hmmmm ... me too!
On May 11, 11:27*am, rkc <r...@rkcny.yabba.dabba.do.comwrote:
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.
Jun 27 '08 #6
rkc
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.Execute("SELECT [Name] FROM
Schools WHERE 1 = 2").Fields(0).DefinedSize

' shows 50

Hmmmm ... me too!
You're the Master. I am but a simple student.
Jun 27 '08 #7
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

Jun 27 '08 #8
"lyle fairfield" <ly************@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
Jun 27 '08 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
0
by: sienayr | last post by:
Greetings, I have searched hi and lo through the groups and haven't found my problem specifically. I have listed below what I have tried based on what I have found in the groups. Please let me...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
7
by: Donald Grove | last post by:
Is it possible to retrieve field properties from a table in access2000 using code? I have tried: " dim dbs as dao.database dim tbl as dao.tabledef dim fld as dao.field dim prop as...
25
by: Shawn Ferguson | last post by:
I'm in the process of learning .NET but I have an issue. How do you know what CAN exist. What I mean is how does a person know to use the string.todate or something like that? There are so many...
6
by: jean.ulrich | last post by:
Hi I have a form that is not related with a table neither a query (unbound form) On this form I have a text field where the user can put a text or a number As I dont want to create a table...
1
by: David Van D | last post by:
Hi there, A few weeks until I begin my journey towards a degree in Computer Science at Canterbury University in New Zealand, Anyway the course tutors are going to be teaching us JAVA wth bluej...
17
by: The Frog | last post by:
Hello everyone, I am working on an application that can build database objects in MS Access from text files. I suppose you could call it a backup and restore type routine. Accessing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.