QueryDef with ALTER TABLE ADD column statement

Question

Hello,

I am trying to use Query Def for the first time to create a query that will then alter a table by adding columns. This functionality will allow users of a Ms Access database to modify a temporary table by adding columns to it via a form. The form will have a list of options to select. Once the users clicks on a button, the code will add these options one at a time as columns to the table. I'll create a loop for this to happen. The table will also be dropped and recreated with the standard parameters. So the steps are:

1 User selects options

2 User clicks alter table button

3 Table is recreated with standard parameters

4 Table has column added with first of user's selections by having a query that is programmatically edited

5 Loop until all selections have been added as columns to table

6 Form displays table for user to enter data

7 Submit button will extract user's data and enter into a standardized table

8 End

I need help with number 4. This is what I came up

Private Sub Command0_Click()

    Dim qdfNew As QueryDef
    Set qdfNew = .CreateQueryDef("qtyAlterTable", "ALTER TABLE tempRoomFeature ADD qryRoomFilter.Room integer")

End Sub

tempRoomFeature is the name of the table in question

qryRoomFilter is the name of the query providing the first of the user's selctions

Room is the name of the field produced by query qryRoomFilter

Thanks for your help.

Answer 1

Hi,

Just a guess but how about using something like:

..., "ALTER TABLE tempRoomFeature ADD " & DLookup("FieldName","qryRoomFilter") & " INTEGER")

Basically, we use DLookup() to get the name of the field produced by qryRoomFilter.

Hope it helps...

Answer 2

Thanks for the help, combining your suggestion with more head scratching and further research using the following

http://access-excel.tips/access-vba-create-query-createquerydef/

It now works. Below is the code

Private Sub Command0_Click()

    ' declare objects and outcome of code as a string
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdfNew As DAO.QueryDef
    Dim newSQL As String
   
    ' delete previous query in order to deal with error about existing query with matching name
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "qryAlterTable"
    On Error GoTo 0
   
    ' create and set string outcome for query
    newSQL = "ALTER TABLE tempRoomFeature ADD " & DLookup("[Room]", "RoomFilter") & " INTEGER"
    Set qdfNew = db.CreateQueryDef("qryAlterTable", newSQL)
   
End Sub

Answer 3

Hi,

Congratulations! Glad to hear you got it to work. Good luck with your project.

Answer 4

Why continuously delete/create?  Why not modify the existing instance? Something along the lines of

Private Sub Command0_Click()
    On Error GoTo Error_Handler
    Dim sSQL                  As String
    Const sQueryName = "qryAlterTable"

    ' create and set string outcome for query
    sSQL = "ALTER TABLE tempRoomFeature ADD " & DLookup("[Room]", "RoomFilter") & " INTEGER"
    CurrentDb.QueryDefs(sQueryName).SQL = sSQL

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    If Err.Number = 3265 Then
        CurrentDb.CreateQueryDef sQueryName, sSQL
        Application.RefreshDatabaseWindow
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Command0_Click" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Sub

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net