Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify a query in code (Access 97/XP)

    Hey All!

    I have a rather strange one that I'm working on. Can a query be modified in code? Now I don't mean create the whole query, or work with the SQL. What I'm trying to do is similar to the way you can append fields to a table and then modify their properties. Here is the example from Access 97's help:

    Sub NewField()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create new Field object.
    Set fld = tdf.CreateField("SSN#")
    ' Set Type and Size properties of Field object.
    fld.Type = dbText
    fld.Size = 11
    ' Append field.
    tdf.Fields.Append fld
    Set dbs = Nothing
    End Sub

    What I'd like to do is exactly this, but except for a table, do it for a query. But not just a query, an Append query. So I need to be able to add the "field" to the design grid, then set its properties (ie. Append To, etc).

    Has anyone done anything like this successfully? In the mood for a challenge?

    TIA!

    Cecilia
    I can do it, but unsuccessfully *g*

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Modify a query in code (Access 97/XP)

    I don't think you can do this type of thing to a query, but I could be wrong, have been before.
    What I would do is to use the CreateQueryDef to create the query from a SQL you have built in code, it's not hard.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Modify a query in code (Access 97/XP)

    The only way I know of to do this is by creating a new query (in code) or by replacing the SQL of the existing query. In either case, you have to build the SQL for the whole query, not just somehow append a field to the query object. Sorry, but queries aren't the same kind of objects that tables are, so you can't do it the way you want to.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify a query in code (Access 97/XP)

    Thanks, Charlotte.

    The help in Access 97 appeared to indicate that you could. The help in XP has a chart that clearly shows you can't. Big time bummer for me.

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

    Re: Modify a query in code (Access 97/XP)

    According to the DAO help in Access 97, the CreateField method doesn't apply to a QueryDef object, and Append isn't a method of the Fields collection of a QueryDef, as shown by the attached screenshot.
    Attached Images Attached Images
    • File Type: png x.png (4.6 KB, 0 views)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify a query in code (Access 97/XP)

    Thanks, Hans, I missed that one. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

Posting Permissions

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