Chaning many field types at once from long integer to decimal

Posted on 2011-05-09
Last Modified: 2012-05-11
Is it possible to change multiple field types in a table at the same time?  Right now I made the mistake to store an input mask series of fields 999.9;0;# as long integer which truncates the decimal values.  Please let me know if I can fix this other than the cumbersome 1 by 1 way.

Question by:Bevos
    LVL 92

    Expert Comment

    by:Patrick Matthews

    One could write VBA code to do it, but unless you have scores of fields to change, I suspect that in the time required to write and test the code you could accomplish the change manaually.


    LVL 44

    Assisted Solution

    If you know the number of the field in the Fields() collection of the tabledef object, you could cycle through the fields and change their type from dbLong (4) to dbDouble(7).  If you need additional help I need to know the structure of the table and which of the fields have to change.
    LVL 44

    Expert Comment

    Bevos:  You realize that all the data has to be re-entered because the decimal portion has been stripped.  Changing the datatype will not recover that portion.

    Author Comment

    Oh there isn't any data yet, just tables.  Why would I use dbDouble rather than Decimal?

    LVL 44

    Expert Comment

    Your call.  Double uses 8 bytes per number - Decimal uses 12.  Press Alt+F11 to get to the VB Editor window and in Help type both Decimal datatype and Double datatype -  have a good read - normally people use Double unless you need over 15 decimal places.
    LVL 26

    Assisted Solution

    In mocking something up on the weekend, I tried to use decimal for currency.
    On the forms, it just wouldn't display the fractions.  It rounded them down, even with it supposed to save 4 decimal places.
    Double worked perfectly in Access 2010


    How many fields and how many tables Bevos?
    Are ALL the fields with input mask 999.9;0;# and long integer bad?

    If they all are they can be looped and changed.
    Haul out the code you got from us for putting field captions in a listbox.
    Input mask and Type are properties of the field.

    Give it a whack, and if you can't make it go, post your efforts up, and we'll refine them
    LVL 26

    Assisted Solution

    'Pass in a tablename

    Public Function ChangeFieldDataTypes(TableName As String)
    On Error GoTo myerror
    Dim db As Database
    Dim rs As Recordset
    Dim myfield As Field

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

    For Each myfield In rs.Fields
        If myfield.Type = dbLong And myfield.Properties("InputMask").value = "999.9;0;#" Then
            MsgBox myfield.Type
            MsgBox myfield.Name & " " & myfield.Properties("InputMask").value
            'myfield.Type = dbDouble
            'myfield.Properties("InputMask").value = ""
            Next x
    'End If
    Next myfield

    Exit Function

    If Err.Number = 3270 Then 'no existing input mask
        Resume Next
    End If

    End Function
    LVL 44

    Assisted Solution

    OK Nick, I have to ask - why are you doing this with a Recordset object rather than the TableDef object?  
    LVL 26

    Assisted Solution

    The simple answer is:  I know how, and it works.

    The more complex answer is they are equivalent.
    This site uses the tabledef method of snagging the fields collection
    and also pointed out the exact synatx I needed for the input mask

    The most complex answer is that I had already created a stub db for the author on an earlier question that snagged Caption properties.
    So this code is an easy adaptation of that code.

    The real answer <grin> is since I rarely do DDE in code, the tabledefs collection is something I've only used twice in 7 years.
    I know the recordset stuff will work, and I won't have to debug the code by working with something unfamilar.

    There isn't a lot of difference between


    set rs = currentdb.opensrecordset(tablename0
    LVL 44

    Assisted Solution

    Thanks for the 'whole' answer.  For me, if I want to get at the data using code, I'll go recordset.  If I want to modify a table using code, I'll go tabledef.  

    LVL 26

    Accepted Solution

    If I have a boatload of DDE to do--especially creation--then tabledef is the way to go.
    You can't, after all, pull up a recordset for a table that doesn't exist yet.
    But it isn't something I have to do very often
    Coding up table creation would probably be more time consuming than just doing it.
    These days, my tables are all on SSEE 2005 anyway.

    There are the two thouroughly equivalent methods of getting at the fields collection of an existing table.
    TableDef or Recordset, either one will get you access to the collection.

    In a previous question, the author wanted a listbox populated with all the captions from the fields in a table.
    This was provided.  The author is very new to Access.
    Creating similar code that reinforces how to work with the fields collection was a good thing.

    Areas where I have used similar code to good effect is in creating Excel exports.
    You pull up the recordset.
    You loop through the fields collection putting each field name in a cell in Row A
    You then have the recordset already open to knock into the sheet
    oSheet.Range("A2").CopyFromRecordset rs
    In that scenario, using the TableDef is redundant because you can get what you need from a recordset you're going to open anyway.

    That's the end I've come from when using the fields collection for its properties.

    Author Closing Comment

    Thank you so much for all of the advice everyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now