Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    adding fields in code (97-XP)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: adding fields in code (97-XP)

    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.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: adding fields in code (97-XP)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding fields in code (97-XP)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: adding fields in code (97-XP)

    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?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: adding fields in code (97-XP)

    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???
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: adding fields in code (97-XP)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: adding fields in code (97-XP)

    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

Posting Permissions

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