How to change field size of a table field in VBA

[tekvb1977]

I have a table that I make. But I like to change the field size of a particular field to Double after my table is made. I also like to change the format to Fixed and Decimal Places to 1. How Could I do this in VBA.

Thanks in advance for your help.

J

 

[lameid]

I'm not sure without looking (I'm assuming there is a size or datatype property of the field object, but then again I'm thinking DAO <Tabledef>.Fields("FieldName").Size = ?? >)... but why wouldn't you just have the table set up ahead of time? It just sounds like you are risking losing data by having the wrong datatype to start with.

 

[Zion7]

very good point from lameid, but it sounds you want to go larger. Still, proceed with caution.

latest technique, ADOX. offers a lot of control over properties.(as opposed to DAO).

 

[Ed2020]

Isn't ADOX slower when using Jet tables...?

What properties does ADOX cover that DAO doesn't?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Zion7]

Didn't mean, more control, meant latest technology as opposed to DAO. (I can appreciate the confusion).
So far, I've found them comparable in methods and properties.
Speed wise Ed, I don't know?

just felt it more advantageous, to catch up on the latest
methodolgy from Microsoft. As you know, DAO may be soon "obsolete".

 

[Ed2020]

Zion7,

Agreed - it's certainly worthwhile knowing the latest technologies, however I've been hearing that DAO (and Access) are going to be obsolete for a number of years now. Until there's a replacement technology then I can't see it happening for the foreseeable future.

To be fair I'm not so sure about the relative speeds of DAO and ADOX for working with table design. Certainly the last time I checked ADO and DAO for working with data DAO was significanly faster. If I remember correctly there is a MS white-paper confirming this.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Zion7]

I'll definately take your word for it (it sounds like you're been privy to discussions on it), DAO is faster when manipulating data.

Just for the record, I didn't mean to detract from what lameid said, it was a very viable suggestion. I'm under the impression that, both methods for "DDL", will equally get the job done...

...It sounds like J, is unfamiliar with both.
In lieu of this, wouldn't you recommend ADOX?

 

[Ed2020]

Zion7,

To be honest I'm not sure what I'd recommend. Probably DAO as I almost exclusively use DAO everywhere else to amend data in Jet tables.

It probably doesn't make much difference either way though, so I'll shut up now.

Ed Metcalfe.

Please do not feed the trolls.....

 

[jedraw]

I believe you can do part of what you want using SQL.

Here's a sample

Sub tektipsAlterTblTest()

'DoCmd.RunSQL ("DROP TABLE tektips_Table; ")

'Create a table with defined columns and datatypes
DoCmd.RunSQL ("CREATE TABLE tektips_Table " _
            & " ( col1 text(20), " _
            & "   col2 integer)")

'modify col2  to DOUBLE datatype
DoCmd.RunSQL ("ALTER TABLE  tektips_Table " _
           & " ALTER COLUMN col2 double   ")

End Sub

I don't think you can add the decimal place/formatting using SQL. I think you can change the format on the form or report when you display the data value.

Hope this helps.

 

[TheAceMan1]

How are ya tekvb1977 . . .

[blue] . . . but why wouldn't you just have the table set up ahead of time?[/blue]

I couldn't agree more.

[blue]Why know the datatype you want and make it something else at design time?[/blue]

BTW . . . what happened to [blue]tekvb1977[/blue]?

See Ya! . . . . . .

 

[tekvb1977]

Thanks for all your help. I think I got the idea. The only reason I want to change the field size because I am making a table and everytime I make it, access defaults to the default field size. I guess I could append data to the table after creating and setting the field size to what I want. I was just wondering if there is a quick way in VBA (either DAO or ADO) to change the field sizes at design time.

 

[Zion7]

I don't believe in ADO or DAO, you can CHANGE, a field size. You can create a new field with desired attributes, then delete the old.
But personally, jedraw's code was a great idea!

 

[lameid]

For DAO look up the type property in help

Object model reference in below Psuedo Code

Tabledefs("TableName").Fields("FieldName").Type = <DataTypeConstant>