Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: What type does the DAO.createfield recieve? - conversion problem

    Hello,

    I've been working on a form designed to add columns to tables. I've got a code that uses DAO to create the field. I've succeeded so far to replace the default field name to a variable that is recieved from the user.

    The one thing I have a problem with is changing the field type by the user. I tried feeding the type from a string variable or a double variable or even a varient but a error keeps popping up ( a conversion error - the CreateField won't accept these types).

    The line I'm having trouble with is this one:

    Set fldNew = tblTest.CreateField(strField, dbLong)

    So I guess the basic question is this:

    What type is dbLong (its a variable telling the createfield to make a long field, but what type is the variable itself, so I'll know how to replace it?)?



    The complete code is this, if it's useful to anyone:

    Private Sub Command0_Click()

    Dim strField As String
    Dim curDatabase As Object
    Dim tblTest As Object
    Dim fldNew As Object
    Dim strTargetable As String
    Dim strType As Variant

    strTargetable = "books"
    Set curDatabase = CurrentDb
    Set tblTest = curDatabase.TableDefs(strTargetable)

    strField = Me.txtFieldname.Value
    'strType = "db" & UCase(Left(Me.txtFieldtype.Value, 1))
    '&Right(Me.txtFieldtype.Value, Len(Me.txtFieldtype.Value) - 1)


    Set fldNew = tblTest.CreateField(strField, dbLong)
    'Set fldNew = tblTest.CreateField(strField, strType) - this is what I tried

    tblTest.Fields.Append fldNew
    MsgBox "A field named" & strField & " was added to " & strTargetable
    MsgBox strType
    End Sub

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Adnaket View Post
    Hello,

    I've been working on a form designed to add columns to tables. I've got a code that uses DAO to create the field. I've succeeded so far to replace the default field name to a variable that is recieved from the user.

    The one thing I have a problem with is changing the field type by the user. I tried feeding the type from a string variable or a double variable or even a varient but a error keeps popping up ( a conversion error - the CreateField won't accept these types).

    The line I'm having trouble with is this one:

    Set fldNew = tblTest.CreateField(strField, dbLong)

    So I guess the basic question is this:

    What type is dbLong (its a variable telling the createfield to make a long field, but what type is the variable itself, so I'll know how to replace it?)?



    The complete code is this, if it's useful to anyone:

    Private Sub Command0_Click()

    Dim strField As String
    Dim curDatabase As Object
    Dim tblTest As Object
    Dim fldNew As Object
    Dim strTargetable As String
    Dim strType As Variant

    strTargetable = "books"
    Set curDatabase = CurrentDb
    Set tblTest = curDatabase.TableDefs(strTargetable)

    strField = Me.txtFieldname.Value
    'strType = "db" & UCase(Left(Me.txtFieldtype.Value, 1))
    '&Right(Me.txtFieldtype.Value, Len(Me.txtFieldtype.Value) - 1)


    Set fldNew = tblTest.CreateField(strField, dbLong)
    'Set fldNew = tblTest.CreateField(strField, strType) - this is what I tried

    tblTest.Fields.Append fldNew
    MsgBox "A field named" & strField & " was added to " & strTargetable
    MsgBox strType
    End Sub
    Maybe this example will help:

    Code:
    Function ModifyTableDAO()
    
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        'Initialize
        Set db = CurrentDb()
    
        Set tdf = db.TableDefs("tblDaoContractor")
        
        'Add a field to the table.
        tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
        
        'Clean up
        Set fld = Nothing
        Set tdf = Nothing
        Set db = Nothing
    End Function
    So your code would be something like this:

    Code:
    Private Sub Command0_Click()
    
    Dim strField As String
    Dim curDatabase As DAO.Database
    Dim tblTest As DAO.TableDef
    Dim fldNew As DAO.Field
    Dim strTargetable As String
    Dim strType As Variant
    
    strTargetable = "books"
    Set curDatabase = CurrentDb
    Set tblTest = curDatabase.TableDefs(strTargetable)
    
         strField = Me.txtFieldname.Value
    
    
        tblTest.Fields.Append tblTest.CreateField(strField ,  dbLong) 
    
    MsgBox "A field named" & strField & " was added to " & strTargetable
    
    ' clean up
        
        Set tblTest = Nothing
        Set curDatabase = Nothing
    
    End Sub
    *** Untested - Air Code ***
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Apr 2010
    Posts
    20
    Quote Originally Posted by HiTechCoach View Post
    Maybe this example will help:

    So your code would be something like this:

    Code:
    Private Sub Command0_Click()
    
    Dim strField As String
    Dim curDatabase As DAO.Database
    Dim tblTest As DAO.TableDef
    Dim fldNew As DAO.Field
    Dim strTargetable As String
    Dim strType As Variant
    
    strTargetable = "books"
    Set curDatabase = CurrentDb
    Set tblTest = curDatabase.TableDefs(strTargetable)
    
         strField = Me.txtFieldname.Value
    
    
        tblTest.Fields.Append tblTest.CreateField(strField ,  dbLong) 
    
    MsgBox "A field named" & strField & " was added to " & strTargetable
    
    ' clean up
        
        Set tblTest = Nothing
        Set curDatabase = Nothing
    
    End Sub
    *** Untested - Air Code ***
    Thanks for the reply - But I didn't understand what your code is supposed to do (I'm a Newbie in using DAO) - could you explain ?

    As for what I wanted - I miraclessly found it, and it turns out that the 'Type' in CreateField is Long.

    I added this code:

    Code:
    Select Case strType
     Case "1", "dbBoolean", "Boolean"
      varType = 1
     Case "2", "dbByte", "Byte"
      varType = 2
     Case "3", "dbInteger", "Integer"
      varType = 3
     Case "4", "dbLong", "Long", "Numeric", "dbNumeric"
      varType = 4
     Case "5", "dbCurrency", "Currency"
      varType = 5
     Case "6", "dbSingle", "Single"
      varType = 6
     Case "7", "dbDouble", "Double"
      varType = 7
     Case "8", "dbDate", "Date", "Time", "Date/Time"
      varType = 8
     Case "9", "dbBinary", "Binary"
      varType = 9
     Case "10", "dbText", "Text", "String"
      varType = 10
     Case "11", "dbLongBinary", "OLE"
      varType = 11
     Case "12", "dbMemo", "Memo"
      varType = 12
     Case Else
      varType = 10
    End Select
    (adopted from here: Microsoft: Access Other topics - Updating multiple tables)

Posting Permissions

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