469,282 Members | 1,704 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

How can I modify fields types via VBA code?

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
Nov 13 '05 #1
6 17625
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

Nov 13 '05 #2
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
Nov 13 '05 #3
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

Nov 13 '05 #4
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?
Nov 13 '05 #5
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?

Nov 13 '05 #6
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! :)
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Chris McKeever | last post: by
4 posts views Thread by John | last post: by
23 posts views Thread by no1zson | last post: by
14 posts views Thread by Thurston Manson | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.