Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Unanswered: Automating a Datatype change

    When I export or do a make table query from Oracle to Access, the new table has some of the datatypes changed. Many of my numberic fields are decimal instead of Long Interger. I then need to go and manually change them back. If I forget or miss one, it crashes another application we use.

    Has anyone come up with a solution to automatically go through the table structure and change the datatype from one format to another? This would be a great help.

    I am using Oracle 9i and Access 2002.

    Thanks for you time

    Sherriff

  2. #2
    Join Date
    Feb 2006
    Location
    Houston, TX USA
    Posts
    16
    cint(OracleField) as intAccessField

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Another way might be:

    Here are a couple of function you can use...

    Code:
    Public Function ChangeFieldType(strTableName As String, strFieldName As String, strFieldType As String) As Long
      ' -1 = Success
       ' 0= Failure
       ' Call sChangeField("tblName","fldName","TEXT(100)")
       
       ' AutoNumber          {Designated as a AutoNumber type - Can Not Be Set}
       ' Byte                {Designated as a Number type}
       ' Integer             {Designated as a Number type}
       ' Long                {Designated as a Number type}
       ' Single              {Designated as a Number type}
       ' Double              {Designated as a Number type}
       ' Float               {Designated as a Number type - Field Size is Listed as Double}
       ' GUID                {Designated as a Number type - Listed as 'Replication ID'}
       ' Decimal             {Designated as a Number type - Can Not Be Set}
       ' Text or Text(size)  {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Char                {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Memo                {Designated as a Memo type - Max size is unlimited}
       ' Number              {Designated as a Number type}
       '                     {Default is Double}
       ' Currency            {Designated as a Currency type}
       ' YesNo               {Designated as a Yes/No type}
       ' Date                {Designated as a Date/Time type}
       ' TimeStamp           {Designated as a Date/Time type}
       ' Time                {Designated as a Date/Time type}
       ' Binary              {Designated as a Binary type}
       ' VarBinary           {Designated as a Binary type}
       ' OLEObject           {Designated as a OLE Object type}
       ' LongBinary          {Designated as a OLE Object type}
       On Error GoTo Error_ChangeFieldType
       Dim db As Database
       Dim strSQL As String
       strSQL = "ALTER TABLE [" & strTableName & "] ALTER COLUMN [" & strFieldName & "] " & UCase(strFieldType)
       Set db = CurrentDb
       db.Execute strSQL
       ChangeFieldType = -1
    Exit_ChangeFieldType:
       db.Close
       Set db = Nothing
       Exit Function
       
    Error_ChangeFieldType:
       ChangeFieldType = 0
       Err = 0
       Resume Exit_ChangeFieldType
    End Function
    and to do a little more...

    Code:
    Public Function SetTableFieldProperty(DBName As String, TableName As String, TableFieldName As String, _
                                         strPropertyName As String, intType As Integer, _
                                         varValue As Variant, Optional LogSuccess As _
                                         String, Optional LogFailure As String, Optional _
                                         ReturnError As String) As Boolean
       ' -----------------------------------------------------------------------------
       ' Description:
       ' ===========
       ' Purpose:
       ' Set or change a Table Field Property, creating the property if necessary.
       '
       ' Parameters:
       ' ==========
       ' DBName           = Path and name of the Database which contains
       '                    the table of the field property we want to
       '                    modify (change or add). If a empty string is
       '                    passed then the Database which calls this function
       '                    is assumed to contain the specified Table.
       ' TableName        = The of the Table which contains the field
       '                    property we want to change.
       ' TableFieldName   = The table Field whose property should be set.
       ' strPropertyName  = The name of the property to set.
       ' intType          = The type of property (needed for creating)
       '                    {See: PROPERTIES FOR TABLE  FIELD posted below}
       ' varValue         = The value to set this property to.
       ' LogSuccess (opt) = String to display in the a Log File if this
       '                    Function is Successfull. (See the LogToFile Function)
       ' LogSuccess (opt) = String to display in the a Log File if this
       '                    Function is Unsuccessfull. (See the LogToFile Function)
       '
       ' Syntax:
       ' ======
       ' MsgBox SetTableFieldProperty("", "Mailing List", "FirstName", "Decription", _
                                     10, "The Users First Name")
       '
       ' RETURNS:
       ' =======
       '  0 = Failure - most likely due to an Error of some kind.
       ' -1 = Successfull
       ' -2 = The Existing Table (old table) specified can't be
       '      found in the specified database.
       ' -3 = The New Table name specified already exists within
       '      the specified Database.
       ' -----------------------------------------------------------------------------
       '
       '    PROPERTIES FOR TABLE  FIELD
       ' Property Name    &   Value    Type
       '=====================================
       ' Description         Type: 10 (Text)
       ' Value               Type: 10 (Text)
       ' Attributes          Type: 4  (Long)
       ' CollatingOrder      Type: 3  (Integer)
       ' Type                Type: 3  (Integer)
       ' Name                Type: 12 (Memo)
       ' OrdinalPosition     Type: 3  (Integer)
       ' Size                Type: 4  (Long)
       ' Format              Type: 10 (Text)
       ' InputMask           Type: 10 (Text)
       ' Indexed             Type: 3  (Integer)
       ' SourceField         Type: 12 (Memo)
       ' SourceTable         Type: 12 (Memo)
       ' ValidateOnSet       Type: 1  (Boolean)
       ' DataUpdatable       Type: 1  (Boolean)
       ' ForeignName         Type: 12 (Memo)
       ' DefaultValue        Type: 12 (Memo)
       ' ValidationRule      Type: 12 (Memo)
       ' ValidationText      Type: 12 (Memo)
       ' Required            Type: 1  (Boolean)
       ' AllowZeroLength     Type: 1  (Boolean)
       ' FieldSize           Type: 4  (Long)
       ' OriginalValue       Type: 10 (Text)
       ' VisibleValue        Type: 10 (Text)
       ' Caption             Type: 12 (Memo)
       ' ColumnWidth         Type: 3  (Integer)
       ' ColumnOrder         Type: 3  (Integer)
       ' ColumnHidden        Type: 1  (Boolean)
       ' DisplayControl      Type: 3  (Integer)  In The LookUp Property Section
       '                              (Set to 109 for TextBox)
       '                              (Set to 110 for ListBox)
       '                              (Set to 111 for ComboBox)
       ' IMEMode             Type: 2  (Byte)
       ' IMESentenceMode     Type: 2  (Byte)
       ' UnicodeCompression  Type: 1  (Boolean)
       ' GUID                Type: 9  (Binary)
       ' -----------------------------------------------------------------------
       If DoesTableExist(TableName) = False Then Exit Function
       On Error GoTo ErrHandler
       Dim db As Database
       Dim tDef As TableDef
       Dim obj As Field
       
       If DBName <> "" Then
          If DoesFileExist(DBName) = False Then Exit Function
          Set db = OpenDatabase(DBName)
       Else
          Set db = CurrentDb
       End If
       Set tDef = db.TableDefs(TableName)
       
       For Each obj In tDef.Fields
         If obj.Name = TableFieldName Then
            If TableFieldHasProperty(obj, strPropertyName) Then
               obj.Properties(strPropertyName) = varValue
            Else
               obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
            End If
            SetTableFieldProperty = True
         End If
      Next obj
    ExitHandler:
       db.Close
       Set db = Nothing
       Set tDef = Nothing
       Set obj = Nothing
       Exit Function
    ErrHandler:
       ReturnError = ReturnError & obj.Name & "." & strPropertyName & _
       " not set to " & varValue & ". Error " & Err.Number & " - " & _
       Err.Description & vbCrLf
       Resume ExitHandler
    End Function
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Aug 2003
    Posts
    14
    Thanks very muhc for the help. Will give this a try.

    Sherriff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •