Obtain Field Properties

[Guest]

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!!

 

[Allen Browne]

Try Size.

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

 

[Marshall Barton]

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.

I think you're looking for:

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

 

[Guest]

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!

 

[Marshall Barton]

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

I think you're looking for:

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

 

[Kevin K. Sullivan]

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,

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!

:

vtj wrote:




I think you're looking for:

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

 

[Guest]

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
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,

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!

:

vtj wrote:


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.


I think you're looking for:

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

 

[Marshall Barton]

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.
--
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
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)

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.

vtj wrote:
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.