Obtain Field Properties

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vtj - 09 Jan 2006 16:47 GMT

I am trying to find out how big (long) each field in a data base is defined
to be. i have used the following: cntr1 =
DBEngine.Workspaces(0).Databases(0).TableDefs(0). with attributes, record
count, properties, and fields as the next word. Attributes returns '2'.
record count returns '10' (there is only one record in the table), properties
shows as an error for Invalid Argument, fields shows as an error for Invalid
Argument.

I have worked with the VBA enough to know that I'll bet the field legth
exists and I am just not getting the right question to the system.

Thanks for your help!!

Reply to this Message

Allen Browne - 09 Jan 2006 17:01 GMT

Try Size.

Example of how to parse field info in a table:
http://allenbrowne.com/func-06.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am trying to find out how big (long) each field in a data base is defined
> to be. i have used the following: cntr1 =
[quoted text clipped - 10 lines]
>
> Thanks for your help!!

Reply to this Message

Marshall Barton - 09 Jan 2006 17:28 GMT

>I am trying to find out how big (long) each field in a data base is defined
>to be. i have used the following: cntr1 =
[quoted text clipped - 6 lines]
>I have worked with the VBA enough to know that I'll bet the field legth
>exists and I am just not getting the right question to the system.

I think you're looking for:

Set db = CurrentDb()
Set tdf = db.TableDefs!tablename
Set fld = tdf.Fields!fieldname
Set prp = fld.Size

Signature

Marsh
MVP [MS Access]

Reply to this Message

vtj - 09 Jan 2006 18:40 GMT

Using either of the methods suggested, I get a type mismatch error when I set
fld line. For reference my field is called item1.
Function TableInfo()

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = CurrentDb()
Set tdf = db.TableDefs![Table1]
Set fld = tdf.Fields![item1]
'Set prp = fld.Size

I do not understand how a type can be a mismatch.

Thanks!

> >I am trying to find out how big (long) each field in a data base is defined
> >to be. i have used the following: cntr1 =
[quoted text clipped - 13 lines]
> Set fld = tdf.Fields!fieldname
> Set prp = fld.Size

Reply to this Message

Marshall Barton - 09 Jan 2006 19:47 GMT

The way I wrote that, you would need :

Dim lngBytes As Long
Dim prp As Property
Set prp = fld.Size
lngBytes = prp.Value

which, as I look at it is the long way around. Let's just
replace the Set prp line with

Dim lngBytes As Long
lngBytes = fld.Size

Signature

Marsh
MVP [MS Access]

>Using either of the methods suggested, I get a type mismatch error when I set
>fld line. For reference my field is called item1.
[quoted text clipped - 27 lines]
>> Set fld = tdf.Fields!fieldname
>> Set prp = fld.Size

Reply to this Message

Kevin K. Sullivan - 09 Jan 2006 20:40 GMT

It looks like you have both DAO and ADODB referenced, but ADODB is
higher in the list. Since both libraries have a Field object, fld is
getting implicitly defined as ADODB.Field when tdf.Fields![item1]
returns a DAO.Field object. Either:

A)Change the declaration to
Dim fld as DAO.Field

B)List DAO in your references higher than ADODB (if you use it)

C)Drop your reference to ADODB (if you don't use it)

HTH,

Kevin

> Using either of the methods suggested, I get a type mismatch error when I set
> fld line. For reference my field is called item1.
[quoted text clipped - 29 lines]
>>Set fld = tdf.Fields!fieldname
>>Set prp = fld.Size

Reply to this Message

vtj - 10 Jan 2006 17:00 GMT

The note to include DAO in Dim statement fixed problem. Thank You Very Much!!!

> It looks like you have both DAO and ADODB referenced, but ADODB is
> higher in the list. Since both libraries have a Field object, fld is
[quoted text clipped - 44 lines]
> >>Set fld = tdf.Fields!fieldname
> >>Set prp = fld.Size

Reply to this Message

Marshall Barton - 10 Jan 2006 17:52 GMT

I have no idea how you were able to deduce the problem,
maybe the type mismatch was the clue?

However you did it, it was a great catch Kevin.

Signature

Marsh
MVP [MS Access]

>It looks like you have both DAO and ADODB referenced, but ADODB is
>higher in the list. Since both libraries have a Field object, fld is
[quoted text clipped - 32 lines]
>>>>I have worked with the VBA enough to know that I'll bet the field legth
>>>>exists and I am just not getting the right question to the system.

Reply to this Message