Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify Field Attributes within SQL code (A97/A2k)

    I am trying to update a table to add a new field. I know how to do this manually, but I need to add this field to three tables, and fill in the default data. I need to follow this process for each backend database that I own (around 25). So, I had hoped to create some code that I could import into each backend database, and then simply "push the button". Using SQL, I have the following statement that works, but does not do everything that I would like.

    dbs.Execute "ALTER TABLE Market_Shr ADD COLUMN ASP_Curr Text(5) NOT NULL;"

    This adds the field. But I would also like to do the following:
    1. Change the "Display Control" to 'List Box'
    2. Change "Row Source Type" to 'Table/Query'
    3. Change "Row Source" to 'Exchange_Rate_codes' (an existing table in my database)
    4. Change the "Bound Column" to '1'
    5. Change the "Default Value" to 'USD'
    6. Move this field between the 4th & 5th field

    Any Ideas??

    TIA, Jerry

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

    Re: Modify Field Attributes within SQL code (A97/A2k)

    Try this. You need a reference to the Microsoft DAO 3.n Object Library (3.5 for Access 97, 3.6 for Access 2000 and higher):

    Sub SetSpecificProperties()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Market_Shr")
    Set fld = tdf.Fields("ASP_Curr")
    SetFieldProperty fld, "DisplayControl", dbInteger, acListBox
    SetFieldProperty fld, "RowSourceType", dbText, "Table/Query"
    SetFieldProperty fld, "RowSource", dbText, "'Exchange_Rate_codes"
    SetFieldProperty fld, "BoundColumn", dbInteger, 1
    fld.OrdinalPosition = 3
    fld.DefaultValue = Chr(34) & "USD" & Chr(34)
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

    Sub SetFieldProperty( _
    fld As DAO.Field, _
    strName As String, _
    intType As DAO.DataTypeEnum, _
    varValue As Variant)
    On Error GoTo ErrHandler
    fld.Properties(strName) = varValue
    Exit Sub
    ErrHandler:
    If Err = 3270 Then
    ' Property does not exist
    fld.Properties.Append fld.CreateProperty(strName, intType, varValue)
    Resume Next
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Note: I'm not sure how exactly the OrdinalPosition property works in practice.

  3. #3
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Field Attributes within SQL code (A97/A2k)

    I am getting an error message "Automation type not supported in visual basic." The code that I used (before) to add the field even give me this error message. Let me start over. I have created a new form with separate buttons to execute each piece of SQL. This is the code that I had before:
    Private Sub btn_RunStep1_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute "ALTER TABLE Market_Shr ADD COLUMN ASP_Curr Text(5) NOT NULL;"
    dbs.Close
    End Sub

    After adding our code to run after clicking a separate button, I get the error message after clicking on the "btn_RunStep1" button. I checked the References, and the DAO library is checked. So, what am I missing? With the code that you provided, it seems as if it should be so straightforward.

    TIA, Jerry

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

    Re: Modify Field Attributes within SQL code (A97/A2k)

    Code like that works without problems for me. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Field Attributes within SQL code (A97/A2k)

    Here is a stripped down copy of my DB. The form is what I am trying to create.

    TIA, Jerry

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

    Re: Modify Field Attributes within SQL code (A97/A2k)

    The code runs without errors on my PC. I notice that I did introduce a superfluous apostrophe ' in the RowSource property of the field, but that will only be noticeable when you try to use the dropdown list in the field.

  7. #7
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Field Attributes within SQL code (A97/A2k)

    OK, if it works for you, then the DB setup items must be the issue. When I select Tools-References, three items are selected (i this order):
    1. Visual Basic for Applications
    2. Microsoft Access 8.0 Object Library
    3. Misrosoft DAO 3.51 Object Library

    Is this what you have selected?

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

    Re: Modify Field Attributes within SQL code (A97/A2k)

    I'm using Access 2002, so I get
    1. Visual Basic for Applications
    2. Microsoft Access 10.0 Object Library
    3. Microsoft DAO 3.6 Object Library
    plus two more that are superfluous and can be removed without any effect. The exact version shouldn't matter here. Can you try the same database on another PC?

  9. #9
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Field Attributes within SQL code (A97/A2k)

    I opened Access 2000 and converted the database that I sent to you. This code works fine then. So, the problem is with Access 97 and the code that you gave me. At this time, I am stuck with Access 97. Some of the users who access the database only have A97 (some have A2k, and some also have A2003). This requires that the backend databases ALL be in A97. Unless you have an idea about where to begin, I'll play with it some more.

    Thanks for the help.

  10. #10
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Field Attributes within SQL code (A97/A2k)

    I found the problem. A97 does not like the type "DataTypeEnum" (from the Sub declaration). As soon as I changed it to "String", it worked fine.

    THANKS!!

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

    Re: Modify Field Attributes within SQL code (A97/A2k)

    I'm glad you found it. It's been almost two years since I worked with Access 97...

Posting Permissions

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