Using a macro to change Field Properties

[JornyO]

How do I change Field Properties using a macro? My macro imports a comma
delimited text file using an Import Spec, and the fields are a mix of
Date/Time, Text and Number in my new table. All the text fields are
automatically set to 255 characters when I import the file. I want to change
some of these to 1, 2 and 3 characters automatically as a step in my macro.
Can I do this? I don't know VB/VBA well at all, so please try to avoid
responses that involve writing scripts! Thanks.

 

[Joseph R. Pottschmidt]

Dear JornyO:

What you are asking is something that you can accomplish within your
Import Spec file, You just need to define your properties of your spec
file in advances as to how many characters and everything as also create
a table with those specs and import them as you would normally. That
should take care of your date problem.

Joe P.


-----Original Message-----
From: JornyO [mailto:[email protected]]
Posted At: Thursday, June 29, 2006 8:54 AM
Posted To: microsoft.public.access.macros
Conversation: Using a macro to change Field Properties
Subject: Using a macro to change Field Properties

How do I change Field Properties using a macro? My macro imports a
comma
delimited text file using an Import Spec, and the fields are a mix of
Date/Time, Text and Number in my new table. All the text fields are
automatically set to 255 characters when I import the file. I want to
change
some of these to 1, 2 and 3 characters automatically as a step in my
macro.
Can I do this? I don't know VB/VBA well at all, so please try to avoid
responses that involve writing scripts! Thanks.

 

[JornyO]

Hi Joseph,

Thanks for your response. I've always thought that this would be the best
way to do it because it would save me a step or two in the macro. But how do
I specify Field Properties like "Field Size" for a Text field or "Decimal
Places" for a Number. It's easy to set the Data Type to Text, and for Number
types you can specify "Long Integer", "Integer" etc... in the Import Spec
file. But I have never been able to find any way to set the other field
properties. If you have an idea as to how to specify a Field Size for a Text
field, I'm all ears! Thanks again.

 

[JornyO]

As an addition, because part of the data processing I do in my macro involves
adding fields whose properties need to be changed, it would still be helpful
to know how to change "Field Properties" like the "Field Size" for a Text
field or a Number field. Thanks again for your help.

Hi Joseph,

Thanks for your response. I've always thought that this would be the best
way to do it because it would save me a step or two in the macro. But how do
I specify Field Properties like "Field Size" for a Text field or "Decimal
Places" for a Number. It's easy to set the Data Type to Text, and for Number
types you can specify "Long Integer", "Integer" etc... in the Import Spec
file. But I have never been able to find any way to set the other field
properties. If you have an idea as to how to specify a Field Size for a Text
field, I'm all ears! Thanks again.

Dear JornyO:

What you are asking is something that you can accomplish within your
Import Spec file, You just need to define your properties of your spec
file in advances as to how many characters and everything as also create
a table with those specs and import them as you would normally. That
should take care of your date problem.

Joe P.


-----Original Message-----
From: JornyO [mailto:[email protected]]
Posted At: Thursday, June 29, 2006 8:54 AM
Posted To: microsoft.public.access.macros
Conversation: Using a macro to change Field Properties
Subject: Using a macro to change Field Properties

How do I change Field Properties using a macro? My macro imports a
comma
delimited text file using an Import Spec, and the fields are a mix of
Date/Time, Text and Number in my new table. All the text fields are
automatically set to 255 characters when I import the file. I want to
change
some of these to 1, 2 and 3 characters automatically as a step in my
macro.
Can I do this? I don't know VB/VBA well at all, so please try to avoid
responses that involve writing scripts! Thanks

 

[Steve Schapel]

Jorny,

Sounds like you are importing to a new table. Why don't you make a
table that has the properties that you desire, and import your data into it?

It is sometimes advisable to do data imports in two steps. First to a
temporary table, and then use an Append Query to move it from there in
any modified format required into your pre-established table.

 

[Nilda]

How do I change Field Properties using a macro?

Use a RunSQL action in the macro with ALTER TABLE SQL code, i.e.,

ALTER TABLE Comp ALTER COLUMN [TaxID] TEXT(10);

I hope it helps!