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


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.

--
Brendan Reynolds
Access MVP

"Jose Perdigao" <JosePerdigao@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BF36B04C-7176-4C83-9615-4B42C6C65BC0@xxxxxxxxxxxxxxxx
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

"Brendan Reynolds" wrote:


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.

--
Brendan Reynolds
Access MVP

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:uQ4Hcd31GHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Jose Perdigao" <JosePerdigao@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:291ED975-089B-4B1B-B571-0AA813D138D6@xxxxxxxxxxxxxxxx
Hi,
This is what I want but in ADP
Have you idea how can I do?
Thanks


"Brendan Reynolds" wrote:

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

--
Brendan Reynolds
Access MVP

"Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:%23Xzn%23xx1GHA.1588@xxxxxxxxxxxxxxxxxxxxxxx
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
Access MVP

"Jose Perdigao" <JosePerdigao@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:70DBE144-4F40-4675-8E0F-B61368980F11@xxxxxxxxxxxxxxxx
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












.

Relevant Pages