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 if you totally wanted to automate the procedure to work with >1 field/table?

Which do you think would be best?

• Dynamically construct an ALTER TABLE query.
• Create a new table à la my suggestion.

 

[mdmilner]

It depends on the needs at the time.

In this particular instance, the ability to alter a single field at at time is the best method to do this. Long term, I may end up rebuilding tables by reconstructing them to address a completely different issue.

This particular mini-app is an automated/scripted report building tool.
As a work around from allowing Access to 'guess' during data imports, I'm using text files and import specifications -- and importing most fields as Text (including numeric & date data). The particular function I was tweaking is the one that visually reformats string (Date) data to look like the various different date formats (MM/DD/YYYY etc) depending on what the specific report wants.

Nearly all of these end up exported into Excel. Something I recently (re)discovered is numeric data exported as Text doesn't behave the same way that my "power excel users" expect. For example, highlighting it doesn't give you the current sum (it's text).

Long term - yes. I may setup a function that rebuilds my output into the actual/proper formats. Because field sequence is important (because I control the creation of the input data...all my imported data is already in the correct field sequence) - I can't just ADD/DROP columns. I'd either need to alter individual fields OR rebuild the tables.

At this point I haven't done my research. I'm not sure yet if I can successfully alter text data into Dates without copying the information first elsewhere to preserve/protect it. I may have to build a new table first. I am open to suggestions. While I'm over 3000 lines of code, I'm sure there's a way to streamline some of it.

Mike

 

[SydneyGeek]

Mike, I had a bit of a play -- maybe you can use an Update SQL expression (or query) like this:
DateSerial(Mid([dummydate],InStr(InStr(1,[dummydate],"/",0)+1,[dummydate],"/",0)+1),Left([dummydate],InStr(1,[dummydate],"/",0)-1),Mid([dummydate],InStr(1,[dummydate],"/",0)+1,InStr(InStr(1,[dummydate],"/",0)+1,[dummydate],"/",0)-InStr(1,[dummydate],"/",0)-1))
to convert the date strings to real dates, then ALTER TABLE to convert the Text data type to Date/Time. It worked fine when I stepped through it manually.

Pretty messy expression -- it would be a whole lot less verbose if there was an equivalent of the Split function in Excel...

Denis

 

[mdmilner]

At first I thought - I don't need this, but you're right. DateSerial is the right way to do this. Right now what I'm doing is like this (buried within a function)

            Select Case Len(strVal)
              Case 4      ' MMDD  (Pipe Report)
                strVal = Left(strVal, 2) & "/" & Right(strVal, 2) & "/2004"
              Case 6
                strVal = Mid(strVal, 3, 2) & "/" & Right(strVal, 2) & "/" & Left(strVal, 2)
              Case 8
                strVal = Mid(strVal, 5, 2) & "/" & Right(strVal, 2) & "/" & Mid(strVal, 3, 2)
              Case Else
            End Select

I wanted my code to handle different setups of dates, but this is a technique error on my part. I just double checked and yes, when exported it doesn't think it's a text field. By default it thinks it's a date field in Excel.

I may still use the SQL approach - just dynamicallly generate my SQL based on the size of the field (it's all consistent within the same table)

Mike