Results 1 to 8 of 8
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    code to change field size is not keeping change

    I have the below code to change the length of an existing field (in an existing table); it seems to work fine, until it does not.


    That is to say:
    - it creates a new field of the proper length,
    - copies the data from the old field to the new,
    - deletes the old field,
    - renames the new field to the old field's name,
    and then...
    - the new field length is back at being the same as the old one (?!?)

    I'm certain that it will be obvious; but then again, it's not obvious to me !
    with much appreciation in advance,
    mark

    Code:
    Function ChangeFieldSize(ByVal vFilePath As Variant, TblName As String, FldName As String, NewSize As Byte)
        Dim Td As TableDef
        Dim Db As Database
        Dim DbPath As Variant
        Dim FldPos As Integer
        Dim rs As Recordset
        Dim X As Integer
    
        'get back end path of linked table
        Set Db = OpenDatabase(vFilePath)   
    
        'get table
        Set Td = Db.TableDefs(TblName)
    
        'change field size
        If Td.Fields(FldName).Size <> NewSize Then
            With Td
    
                On Error Resume Next
                If NewSize > 0 And NewSize < 256 Then    'text field
                    .Fields.Append .CreateField("TempFld", dbText, NewSize)
                Else    '0 is memo field
                    .Fields.Append .CreateField("TempFld", dbMemo)
                End If
    
                Set rs = Db.OpenRecordset(TblName)
                While Not rs.EOF
                    rs.Edit
                    rs!TempFld = rs.Fields(FldName)
                    rs.Update
                    rs.MoveNext
                Wend
                rs.Close
    
                'delete old field
                .Fields.Delete FldName
    
                'rename new field to original
                .Fields("TempFld").Name = FldName
    
    
    '*****  
    'at this point the length of the field is back at the original length, the change to the new length has been dropped (?)       
    MsgBox .Fields(FldName).Size
    '***** 
    
            End With
    
            If Err <> 0 Then GoTo Done
    
        End If
    
        ChangeFieldSize = True  'defaults to false if it fails to get here
    
    Done:
        If Not Db Is Nothing Then Db.Close
    End Function

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try to use DDL (ALTER TABLE myTable ALTER COLUMN myColumn Text(NewSize))? See this link for more info:

    https://docs.microsoft.com/en-us/off...oft-access-sql

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    getting side tracked on other stuff; will try in a day or two (i hope..)
    and will let you know how it works out

    ...in the mean time: THNX!

  4. #4
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    To help you figure out the problem, while debugging I suggest that you comment out the following:

    On Error Resume Next

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    <THINK> I found the issue:
    the table is a linked back-end, which in itself is not an issue; the issue is that the field being modified is in a relationship.
    break the relationship, the field will update

    - inventory the relationship
    - delete the relationship
    - update the field
    - restore the relasionship

    ....now I will say that I haven't tried this yet; ntl, I 'think' this ought to take care of the problem -yes?

  6. #6
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    I don't know, that sounds to me like sort of a lot of invasive work just to have code change a field size. But if that's what you think you need then give it a try. Also perhaps see if it increases the size of the database a little bit (but hopefully not too much) each time...

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sounds like a design issue to me.

    Maybe you are linking tables using a text type field. (Bad IMHO)

    The "one" table should have a PK field type of Autonumber and the "Many" side would have a FK field type of Number - Long. You cannot change the size of number type fields.
    Then you could change the "Size" of any Text type field without problems (even though it should not be necessary)

    See Microsoft Access Tables: Primary Key Tips and Techniques

  8. #8
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    you are correct
    the PK is not what it ought to be ...autonumber
    nor the FKs (of which there are multiple)

    and it would be a good thing to fix

    ...being a bit lazy now,
    do you know of an already written code that would go thru the tables... and correct ?
    (else i could write it, it's just going to be tedious, i'm sure
    ... and then i'll put it on the back burner
    .... and then
    you know the drill)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-14-2018, 06:05 PM
  2. Replies: 13
    Last Post: 08-04-2017, 03:18 PM
  3. Replies: 1
    Last Post: 06-25-2016, 02:00 PM
  4. how to change text field size from 20 to 26
    By Azariah in forum Database Design
    Replies: 4
    Last Post: 07-02-2015, 09:15 AM
  5. Replies: 3
    Last Post: 07-20-2012, 11:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums