createquerydef with all but last column

[supmktg]

I am exporting a table to a csv file using the following code:

strSQL = "Select * from Table1 Where GrpNo = 2"
Set qdf = CurrentDb.CreateQueryDef("qryGrp2", strSQL)
qdf.Close
DoCmd.TransferText acExportDelim, , qdf.name, "C:\Grp2.csv", True

The columns in the table may change, but the last column should always be 'Grpno'. I would like to prevent the 'GrpNo' column from being exported.

Can someone point me in the direction of how to do that?

Thanks,
Sup

 

[Trevor G]

I suspect rather than using all fields you would have to specifiy each one. Or use a create table then you can use Drop Table and specify which Field then export. Look at this link.

http://www.w3schools.com/SQL/sql_alter.asp

 

[supmktg]

Thanks Trevor.

If it helps anyone, I built the Select statement using all of the fields except 'GrpNo' using the following code:

Dim rstFlds As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String

Set rstFlds = CurrentDb.TableDefs("Table1").OpenRecordset

    strSQL = "Select "

    For Each fld In rstFlds.Fields
        If fld.name <> "GrpNo" Then
            strSQL = strSQL & fld.name & ", "
        End If
    Next fld
    
    strSQL = Left(strSQL, (Len(strSQL) - 2))
    
    strSQL = strSQL & " FROM Table1 WHERE GrpNo = 2"

rstFlds.Close
Set rstFlds = Nothing

Sup

 

[Trevor G]

Sup pleased to read you have a working solution.

Hope you have a good weekend.