How can I get Atributes from the field of the table via VBA

[Guest]

Hi,
How can I get the propeties of the column via VBA?
I would like to get:
data type; lenght; caption; format. Is it possible?

Thanks
JP

 

[aaron.kempf]

it's stored in 'syscolumns'

hope that helps

 

[aaron.kempf]

caption is an extended property; i think that you should look under
sysproperties for that one

i dont use captions so i can't tell you for sure

-Aaron

 

[Brendan Reynolds]

Public Type ColInfo
DataType As Long
Length As Long
Caption As String
Format As String
End Type

Public Function GetColInfo(ByVal TableName As String, ColName As String) As
ColInfo

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Dim info As ColInfo

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
Set fld = tdf.Fields(ColName)
For Each prp In fld.Properties
Select Case prp.Name
Case "Type"
info.DataType = prp.Value
Case "Size"
info.Length = prp.Value
Case "Caption"
info.Caption = prp.Value
Case "Format"
info.Format = prp.Value
End Select
Next prp

GetColInfo = info

End Function

Public Sub TestGetColInfo()

Dim ci As ColInfo

ci = GetColInfo("Orders", "OrderDate")
Debug.Print "Type: " & ci.DataType
Debug.Print "Length: " & ci.Length
Debug.Print "Caption: " & ci.Caption
Debug.Print "Format: " & ci.Format

End Sub

 

[Brendan Reynolds]

Oops! Sorry, I forgot that I was in the ADP group. I'm afraid my code won't
work in an ADP.

 

[Guest]

Hi,
This is what I want but in ADP
Have you idea how can I do?
Thanks

 

[Douglas J. Steele]

The SQL Server catalog tables should have what you're looking for.
Unfortunately, I don't have Books On LIne handy, so I can't give you precise
details.

 

[Brendan Reynolds]

You can get Type and Length from the syscolumns table ...

SELECT dbo.syscolumns.type, dbo.syscolumns.length
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id =
dbo.sysobjects.id
WHERE (dbo.syscolumns.name = 'CategoryName') AND (dbo.sysobjects.xtype =
'U') AND (dbo.sysobjects.name = 'Categories')

I don't know how (or if) you could get the caption and format, though.

 

[Malcolm Cook]

For type and length why not simply query INFOMATION_SCHEMA.COLUMNS view : http://msdn2.microsoft.com/en-us/library/ms186778.aspx?

And for caption.... well.... tell us more what you mean by that...

If you mean the text of a label on a form, then you want to interrogate the controls on the form. Note that a control has a
controls property, the first of which is the text control holding any label created by the form creation wizard....

 

[Brendan Reynolds]

Thanks Malcolm.

'Caption' was one of the things Jose asked for in the original post, and I
originally overlooked the fact that he needed this in an ADP. I'm not sure
myself what it means in this context.

 

[aaron.kempf]

syscolumns and sysproperties

that's all you need.

syscolumns is where you can tell which field has which datatype; etc
sysproperties is where you can see the caption for a field I think.

i dont know for positive; i dont use captions

 

[dbahooker]

syscolumns and sysproperties

that's all you need.


syscolumns is where you can tell which field has which datatype; etc
sysproperties is where you can see the caption for a field I think.


i dont know for positive; i dont use captions

 

[Guest]

Thanks,
This is what I want but is not complete.
Also, I would like to get information about CAPTION and FORMAT tha we
define when we create the table.

thanks,
JP

 

[Guest]

When we create a table and when we characterize the properties for each
column name, we have CAPTION property. I would like to get this value to give
automatically the name of the column in a dynamically form that I built.

Also, I would like to get the format type that we defined in a table.

Thanks,
JP

 

[Guest]

When I say length, I mean the number of caracthers that we defined in table
properties. For example I have a table where the column name is IDWT, I
defined:
data type: nvarchar
lenght: 30

I run your sp and show:
Name: IDWT is correct
Type: 39, I think is data type in code
length: 60, here I don't understand, why doesn't show 30?

Thanks

 

[aaron.kempf]

it's because it's nvarchar.. nvarchar(30) takes 60 bytes to store;
because each character can hold any character in the double-byte
character set (unicode)

 

[Brendan Reynolds]

I expect the SP is returning the length in bytes rather than characters -
Unicode uses two bytes per character.

Malcolm's suggestion about using information schema views is probably a
better solution than my suggestion. Specifically, check out the COLUMNS view
documented at the following URL ...

http://msdn2.microsoft.com/en-us/library/ms188348.aspx

It has columns for DATA_TYPE and CHARACTER_MAXIMUM_LENGTH. I expect these
columns will probably be a better match to your requirements.

I'm familiar with the caption property in a JET table, but I don't see it in
an ADP?

I'm afraid I don't know about Format.

 

[Sylvain Lafontaine]

To my knowledge, there is no such thing as the Caption property when you
create or design a table under ADP.

With a linked table under MDB, you have one but this information is stored
locally in the MDB file, not on the SQL-Server server.

 

[Guest]

Thanks about the explication of the length. Now is missing only caption.
I will create an axiliary table where I will store all captions that I'm using


About CAPTION, open an ADP and open any table in design view, before indexed
you will see CAPTION. If this property is stored in the SQL server I don't
know. but it exists.

 

[Robert Morley]

I see Caption in an MDB file, but not in any ADP's. What version of Access
are you using? Maybe it's in one version, but not the other.


Rob