Hi

Here's a brain-teaser for you all.

I want to create a copy of a table which I do using the code below.

SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = 100

Great, all works well.... but I want to exclude a column when the temp table is being built. I know I could specify all the columns I want leaving out the undesired column (rather than using SELECT *) but there are lots of them and, whilst in development, the table columns are liable to change. So I'd like to copy all coumns that are in force at any given time except one in particular?

I thought it would be something like :-


SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = '100' AND Columnname NOT LIKE 'ColumnB';

But this doesn't work.

Any ideas please?

Many Thanks

What about a different approach?

Copy all of the fields, and then delete the one you don't need.

Hi Uncle,

Thanks very much for the reply Gizmo!.... appreciated!

I wondered about the field idea too? The trouble is, I later populate more records into the temporary table using:-

INSERT INTO My_Temp_Table SELECT * FROM My_Table WHERE ColumnA = xxx

I think this will fail if there is a missing column in the target... so what I was hoping to do was use the same "exclude" in the WHERE statements for both the "SELECT * INTO" and the "INSERT INTO"...

If it's possible, then it makes maintenance of code so much easier when adding, dropping columns during the development. I know you can exclude records using NOT LIKE and I saw a "Columnname NOT LIKE" example but I think I misunderstood it's purpose.... not for the first time.. or the last I think! :)

Thanks again and rgds

The following procedure will add or delete a field:


Sub AppendDeleteField(tdfTemp As TableDef, _
strCommand As String, _
strName As String, _
Optional varType, _
Optional varSize)
' This procedure adds or deletes fields from
' a Table Def, as specified. Think it may
' have come from the MSKB, but am not sure.
'
' ARGUMENTS:
' tdfTemp: A table def.
' strCommand: "APPEND" or "DELETE"
' strName: Name of field, as a string.
' varType: Optional--type of field
' varSize: Optional--field size as integer

With tdfTemp

' Check first to see if the TableDef object is
' updatable. If it isn't, control is passed back to
' the calling procedure.

If .Updatable = False Then
MsgBox "TableDef not Updatable! " & _
"Unable to complete task."
Exit Sub
End If

' Depending on the passed data, append or delete a
' field to the Fields collection of the specified
' TableDef object.
If strCommand = "APPEND" Then
.Fields.Append .CreateField(strName, _
varType, varSize)
Else
If strCommand = "DELETE" Then .Fields.Delete strName
End If

End With

End Sub


You should be able to incorporate a call to it into your code.

HTH - Bob

Actually, I eventually decided to do this long-handed using ADODB recordsets and declaring all the fields omitting the offending column since the "SQL SELEECT ... WHERE... EXCLUDING mycolumn" seems to be a non-starter. Shame, it would be agood way of reducing maintenance?

BTW thanks for that add/delete fields code... I can definately use that.

Thanks for your time on this gents... much appreciated...

It just occurred to me that you could adapt the strategy used in this free tool. (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
Basically this tool allows you to select columns from a table and have them appear as rows in another table.

I imagine you could adapt the strategy used in this example to extract the columns you want from one table and place them in another, this would give you a flexible solution with less strain on the gray matter once you have set the solution up.

Thanks Gizmo! I shall invest some time in that... could well be the answer... rgds