By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

How can I modify fields types via VBA code?

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.