• adding fields in code (97-XP)

    Author
    Topic
    #418397

    I am attempting to add a field to a backend database and I can’t figure out why this code does not work.
    Please show me the error of my ways.

    Public Function AddField(strTable As String, strField As String, _
    strType As String, Optional strDatabase As String, _
    Optional intLength As Integer) As Boolean
    On Error GoTo Err_AddField
    Dim dbDatabase As DAO.Database
    Dim tblTable As DAO.TableDef
    Dim fldNewField As DAO.Field

    If strDatabase = “” Then
    Set dbDatabase = CurrentDb
    Else
    If FileExist(strDatabase) Then
    Set dbDatabase = OpenDatabase(strDatabase)
    Else
    MsgBox “Database file does not exist?”
    End If
    End If

    Set tblTable = dbDatabase.CreateTableDef(strTable)

    Select Case strType
    Case “Text”
    ‘ make sure the size has not been left at zero.
    If intLength = 0 Then intLength = 50
    Set fldNewField = tblTable.CreateField(strField, dbText, intLength)
    Case “Memo”
    Set fldNewField = tblTable.CreateField(strField, dbMemo)
    Case “Long”
    Set fldNewField = tblTable.CreateField(strField, dbLong)
    Case “AutoNumber”
    Set fldNewField = tblTable.CreateField(strField, dbLong)
    With fldNewField
    ‘ Appending dbAutoIncrField to Attributes
    ‘ tells Jet that it’s an Autonumber field
    .Attributes = .Attributes Or dbAutoIncrField
    End With
    Case “Integer”
    Set fldNewField = tblTable.CreateField(strField, dbInteger)
    Case “Double”
    Set fldNewField = tblTable.CreateField(strField, dbDouble)
    Case “Single”
    Set fldNewField = tblTable.CreateField(strField, dbSingle)
    Case “Date”
    Set fldNewField = tblTable.CreateField(strField, dbDate)
    Case “Currency”
    Set fldNewField = tblTable.CreateField(strField, dbCurrency)
    Case “Boolean”
    Set fldNewField = tblTable.CreateField(strField, dbBoolean)
    Case “Binary”
    Set fldNewField = tblTable.CreateField(strField, dbBinary)
    Case “OLEObject”
    Set fldNewField = tblTable.CreateField(strField, dbLongBinary)
    Case Else
    ‘ warn the user and skip to the next field
    MsgBox “Unrecognised Data Type for field creation”
    GoTo SkipFieldAppend
    End Select

    tblTable.Fields.Append fldNewField

    AddField = True

    Viewing 0 reply threads
    Author
    Replies
    • #941618

      Are you trying to add a field to an existing table or create a new table and add a single field? CreateTableDef suggests you want to add a new table, but your subject refers to adding fields in code. You also use CreateTableDef to create a link to a table, but of course you can’t add fields to a link, only directly to the backend table.

      • #941619

        OK so I’ve got it all wrong. I am trying to add a field to an existing database in the backend. So how should I be doing this. and how do I add an index? I have managed to add a table to the backend with one field though it does not have an index.

        • #941638

          To add a field to an existing table, you only need to change the line

          Set tblTable = dbDatabase.CreateTableDef(strTable)

          to

          Set tblTable = dbDatabase.TableDefs(strTable)

          If you want to add an index on the field with the same name as the field, you can use these lines (to be inserted after the line that appends the field):

          Dim idxIndex As DAO.Index
          Set idxIndex = tblTable.CreateIndex(strField)
          Set fldNewField = idxIndex.CreateField(strField)
          idxIndex.Fields.Append fldNewField
          tblTable.Indexes.Append idxIndex

          and in the “exit” section of the code

          Set idxIndex = Nothing

          If you want to be able to specify the name for the index, you should add an extra argument to the function.

          • #941818

            Thanks, I can now create fields and indexes but how do I make an index the key and how do I set the index to not allow duplicates?

            • #941838

              It sounds like you could benefit from the DAO help files which are buried in the regular Access Help files (not VBA) as one of the last 4 or 5 categories on the Contents tab. In order to make an index the primary key you set the properties of the index – the two in question are Primary and Unique.

              I should add however that what you are proposing to do in the back-end is highly unusual in an Access environment. In general the recommended strategy is to deploy the front-end on the workstations and the back-end on the server. If you then programaticly add a field and index, the front-end has no knowledge of it – unless to revert to the strategy of relinking tables each time the database is opened. And then if someone has the database and that table open, your addition of fields and indexes will fail. So what problem are you attempting to solve by doing this???

            • #941839

              The client has an already installed backend database on their server. When I send them a new front end they open a form which allows them to link to the backend. On closing this form a routine runs which updates the schema in their backend database to add any new tables, fields and indexes. This works really well in that it relieves the client of the need to make any schema changes.
              The new front end does add new tables and fields and these fields and indexes are seen by the front end. The only problem I have is that I need to change the properties of some indexes to be primary and unique. So adding the follwing lines to my add table routine before I append the new index works a treat.
              With idxIndex
              .Unique = True
              .Primary = True
              End With
              Thanks for pointing me in the right direction.

            • #941841

              I do use the following code to link the table after I create it in the backend –
              ‘ Link to frontend
              If Not TableExists(“strTable”) = True Then
              DoCmd.TransferDatabase acLink, “Microsoft Access”, _
              strDatabase, acTable, strTable, strTable
              End If

    Viewing 0 reply threads
    Reply To: adding fields in code (97-XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: