How can I modify fields types via VBA code?

[Giuseppe Chielli]

Hi to everyone! I'm new in this NG. I'm posting this message just for
learning how can I modify the type of a field in a table via VBA code.
Is that possible? And is anyone so kind to post me some VBA example code?
I'd like to change a numeric field into a text one but I'd like to
create an automatic procedure to do this..Maybe I can do it via SQL
query but I don't know if ALTER TABLE or something similar is supported
in Access.
Thanks a lot in advance to everyone.

Giuseppe

[Allen Browne]

ALTER TABLE is supported in JET 4 (Access 2000 and later), and is the
simplest way to alter a field type or size.

This example converts a field to Text type, 100 characters in size:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"

Execute the query statement under DAO like this:
dbEngine(0)(0).Execute strSql, dbFailOnError
or under ADO like this:
CurrentProject.Connection.Execute strSql

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

"Giuseppe Chielli" <gi******@NOSPAMlibero.it> wrote in message
news:d5**********@balena.cs.interbusiness.it...

Hi to everyone! I'm new in this NG. I'm posting this message just for
learning how can I modify the type of a field in a table via VBA code.
Is that possible? And is anyone so kind to post me some VBA example code?
I'd like to change a numeric field into a text one but I'd like to create
an automatic procedure to do this..Maybe I can do it via SQL query but I
don't know if ALTER TABLE or something similar is supported in Access.
Thanks a lot in advance to everyone.

Giuseppe

[Giuseppe Chielli]

Allen Browne wrote:

ALTER TABLE is supported in JET 4 (Access 2000 and later), and is the
simplest way to alter a field type or size.

This example converts a field to Text type, 100 characters in size:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"

Thanks a lot. It works properly.But I have a new question now: how can I
insert a new field into a table before every other field (so as the
first field)?

Thanks in advance.

Giuseppe

[Allen Browne]

Use DAO to set the OrdinalPosition of the Fields in the TableDef.

Of course, it may not matter, i.e. you can use a query as the source for
whatever needs the fields arranged in a certain way.

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

"Giuseppe Chielli" <gi******@NOSPAMlibero.it> wrote in message
news:d5**********@carabinieri.cs.interbusiness.it. ..

Allen Browne wrote:

ALTER TABLE is supported in JET 4 (Access 2000 and later), and is the
simplest way to alter a field type or size.

This example converts a field to Text type, 100 characters in size:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"

Thanks a lot. It works properly.But I have a new question now: how can I
insert a new field into a table before every other field (so as the first
field)?

Thanks in advance.

Giuseppe

[Giuseppe Chielli]

Allen Browne wrote:

Use DAO to set the OrdinalPosition of the Fields in the TableDef.

And what about using ADO? Can I modify the fields collection?

[Allen Browne]

No, I don't see an ADO/ADOX property for doing this.

Of course, DAO is the library designed for Access.

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

"Giuseppe Chielli" <gi******@NOSPAMlibero.it> wrote in message
news:d5**********@balena.cs.interbusiness.it...

Allen Browne wrote:

Use DAO to set the OrdinalPosition of the Fields in the TableDef.

And what about using ADO? Can I modify the fields collection?

[Giuseppe Chielli]

Allen Browne wrote:

No, I don't see an ADO/ADOX property for doing this.

Of course, DAO is the library designed for Access.

Ok, I see. Thanks a lot from Italy! :)