Changing Fieldsize properties

[Guest]

I have a field that is setup as an integer, but the limit of 32767 has been
hit.
I can manually change the field size in Access, but is it possible to create
an application that can change the field from an integer to a long integer?
Does anyone have some sample code that can be shared?

 

[Jeff Boyce]

Any chance any of your affected Integer fields are related (primary/foreign)
in other tables? Any chance you have code or query parameters
explicitly-typed to Integer (not LongInt)? Even if you had a code-based way
to change the field size, how will you handle the dependencies?

By the way, as I understand it, there is no "overhead" if your field types
are LongInt vs. Int -- Access stores only the value, and doesn't reserve a
field size.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

The field in question is only used in one library which will be easy to
change the data type for all variables. There are no references in the
database related to this field. The reason for changing the value is that the
limit of 32767 for the Integer field creates an SQL error which does not
allow for an INSERT of the new record. By changing the data type, this insert
will be allowed as a LONG INTEGER. Are there any tools that can do this type
of field size change, or any code that can be used to work around the problem?

 

[John Nurick]

By the way, as I understand it, there is no "overhead" if your field types
are LongInt vs. Int -- Access stores only the value, and doesn't reserve a
field size.

Hi Jeff,

This is true for text, memo and OLE types, but not AFAIK for numbers and
booleans - or am I missing something?

 

[Jeff Boyce]

Thanks, John. I knew something was niggling at the back of my brain...

In either case, it sounds like the LongInt is now necessary.

(I'm still wondering why, if there's only one place, why our original poster
can't just change the table definition?!)

Regards

Jeff Boyce
<Office/Access MVP>