Finding Field Type and Length in a SQL Database

[Brian]

I have a SQL Server 2005 back-dnd with an Access 2007 front-end. Tables in
SQL are linked into Access. I want to be able to, with a VBA function, be
able to find the field data type and if a string, length assuming the
function is given the table name and field name.

Reason - All of my forms are unbound text fields...I copy data from the
table to the form and when the user clicks "save" the data is written back
down to the table. Problem is that if the data is too long, Access gives an
error indicating such, but doesn't tell which field or what the maximum
length is. Since I have error checking built into each form, I thought I'd
add this type of checking as well.

Can anyone help?

 

[Tom van Stiphout]

On Mon, 6 Oct 2008 05:55:01 -0700, Brian

Use DAO to check the Size and Type property of the Field object in the
Tabledef object. Something like:
?currentdb.TableDefs("dbo_Employees").Fields("LastName").Size
=> 20

-Tom.
Microsoft Access MVP

 

[Brian]

Thanks for your help. This gives me precisely what I need for text strings.
But do you know how to determine the field type...text, date/time, integer,
money, etc?

 

[Tom van Stiphout]

On Mon, 6 Oct 2008 08:31:00 -0700, Brian

That's why I wrote: "...Size and Type ..."

-Tom.