Can I change the field format in a table with SQL or VB?

[cdavisdccb]

I've created a form to allow certain users to add a new field/column to an existing table.
The following code is in the VB portion of the form.

Dim strCMPNM As String
DoCmd.RunSQL "ALTER TABLE [tblProduct]" & _
"ADD [" & strCMPNM & "] NUMERIC NULL"

Problem now is that the field format should be set to percentage. Is there a way to do this using VB or SQL?

I'd like the table to store the data in percent that way I dont have to modify all of my reports to convert it.

Any help will be appriciated.

Thanks,
cd

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"

 

[mp9]

Just to prevent any "barking up the wrong tree", I'm 99% sure you can't do this through standalone SQL. VBA is the way to go. Off the top of my head, I'd say look up TableDefs in Access help to get some examples of how to modify table definitions through code.

http://www22.brinkster.com/accessory/

 

[cdavisdccb]

I've reposted this thread on the Access Forms forum.
I've scoured the Access help feature specifically the TableDefinitions and could not find a suitable answer.

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"

 

[vbajock]

You have to use ADOX. Since you are modifying rather than creating a new table, it might get pretty complicated.