table field size vba

[mdmilner]

What I'm looking to do seems simple, but I must be missing some obvious syntax. I just would like to change the fieldsize of a text field (increase size) of an existing table.

In the past I've worked around this by creating new tables and appending values, but help seems to indicate that it should be possible to manipulate it using the Size property of the field.

Any thoughts?

Just occurred to me to add some details. O2K on W2K
References to DAO 3.6, VBA & Access 9.0 Object library already set.

tdf.fields(fld.name).Size = 11

Generates a 3219 error

Public Sub modtables()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb()

For Each tdf In dbs.TableDefs
  If tdf.Name = "paper" Then
    For Each fld In tdf.Fields
      If fld.Name = "wbdate" Then
        fld.Size = 11
      End If
    Next
  End If 'Next tdf
Next
End Sub

Mike

 

[Norie]

Mike

What is the error message?

Using the same sort of code that you've posted I've tried in the past to go through every text field in every table in a database and find out the longest item.

I then tried to set the field size appropriately, using code, to hopefully decrease the size of the database, and Access didn't like it.

I can't remember what error was thrown up and couldn't think how to do what I wanted with code so just manually changed the field sizes in Table design mode.

[Edit]I'll try and find the code I was using but I fear I may have lost it.

 

[SydneyGeek]

Mike, I went for a dig and found some code suggesting that you have to reference the field Properties -- somethnig like this:

Public Sub modtables() 
Dim dbs As DAO.Database 
Dim tdf As DAO.TableDef 
Dim fld As DAO.Field 

Set dbs = CurrentDb() 

For Each tdf In dbs.TableDefs 
  If tdf.Name = "paper" Then 
    For Each fld In tdf.Fields 
      If fld.Name = "wbdate" Then 
        fld.Properties("Size") = 11 
      End If 
    Next 
  End If 'Next tdf 
Next 
End Sub

See how that goes...

Denis

 

[mdmilner]

Error code 3219 just seems to indicate it's a Run-Time error.
Unfortunately, this type of referencing didn't work for me.

fld.Properties("Size") = 11

You'd think this would be easy.

Explaining a little bit what I'm doing. I'm importing raw text files into tables using import specifications to define the fields. I've noticed that asking Access to pick appropriate types (this one is actually a date), even though it looks like 091504 (Sept 15, 2004) it generates errors.

So, what I've taken to doing with both Number and Date fields is to import them as Text first, and then manipulate them into other types for formats by manipulating the strings. In this particular instance, Access defaults to field length=6. To reformat it, I need 10 characters. Instead of manually doing this, I wanted to resize the field inside my error handling as needed.

Mike

 

[mdmilner]

One other thought I had is similar to how you renumber an Autonumber field. Delete it, then add a new field and set it to autonumber.

I was tempted to fiddle around with appending a new field, and copying the values over, but, I do not want to alter the sequence of the fieldnames if I can help it. If it's the only way, I'll do it. Right now I'm exporting tables - I could always set up queries to export instead.

Mike

 

[mdmilner]

Ok think I found a answer, here in fact once I began searching for the right thing. An alter table query (tested this) will change the size, but I haven't quite nailed down the syntax if it's possible to specify the exact size.

http://216.92.17.166/board2/viewtop...r+column&sid=5b07ecf04eab5c43d6cd274f13d3936b

ALTER TABLE paper ALTER COLUMN wbdate text(50);

This converts my 10 character text field into a 50 character text field when executed.

Mike

 

[SydneyGeek]

Mike,

thanks for reporting back with the results. As you mentioned, it shouldn't be too difficult to do in code -- but it is Access, and some things in here are needlessly complex I wouldn't have thought of the SQL approach.

Cheers
Denis

 

[mdmilner]

Yep, me either. I'd have thought that the table field properties could have been directly altered. Best I can tell, they can't be, ever. Now I just need to roll this into my error handling. When I was looking this up, it was talking about changing field data types too.

Mike

 

[Norie]

I'd have thought that the table field properties could have been directly altered. Best I can tell, they can't be, ever

That's pretty much what I thought as well.

What about creating a brand new table definition based on the old one but with the field sizes/data types changed as required?

Then append all the records from the old table into the new one.

I've never actually tried that and it might be quite complicated but perhaps it's worth a look at.

 

[mdmilner]

Can be done - isn't any need when an ALTER TABLE SQL command will accomplish it.

Mike