MS Access 2003 - VBA for altering a table after a "SELECT * INTO tblTemp FROM tblMain" statement

Question

Hi. I use functions like the following to make temporary tables out of crosstabs queries.

Function SQL_Tester()
Dim sql As String

If DCount("*", "MSysObjects", "[Name]='tblTemp'") Then
   DoCmd.DeleteObject acTable, "tblTemp"
End If

sql = "SELECT * INTO tblTemp from TblMain;"

 Debug.Print (sql)

  Set db = CurrentDb
  db.Execute (sql)

End Function

I do this so that I can then use more vba to take the temporary table to excel, use some of excel functionality (formulas and such) and then return the values to the original table (tblMain). Simple spot i am getting tripped up is that after the Select INTO statement I need to add a brand new additional column to that temporary table and I do not know how to do this:

sql = "Create Table..."

is like the only way i know how to do this and of course this doesn't work to well with the above approach because I can't create a table that has already been created after the fact, and I cannot create it before because the SELECT INTO statement approach will return a "table already exists" message.

Any help? thanks guys!

Answer 1

I can think of the following ways you can achieve this

1. Create, then insert

You can do a CREATE TABLE tblTemp with all the columns you need. Of course, you will have more columns than TblMain contains, so your insert will contain column definitions.

INSERT INTO tblTemp (Col1, Col2, Col3) SELECT Col1, Col2, Col3 from TblMain

2. Insert Into, then add column

You can do your insert into, then add columns using multiple ways

1. In VBA, use the TableDef object to point to tblTemp and then add a column to it
2. Execute DoCmd.RunSQL "ALTER TABLE tblTemp Add Column MyNewColumn (OTTOMH)

Answer 2

There always more than one way to skin a feline. You could use DAO? This has the advantage of being able to set the various properties of the newly created field that get when creating new field via the user interface within Access. Just an idea :-)

This the sub l created and tested, in Access 2007 should be compatable with any version though.

Public Sub AddField()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Dim strTable As String

Set dbs = CurrentDb

strTable = "tblTemp"

'create the TableDef object that
'wish to change
Set tdf = dbs.TableDefs(strTable)
'create new field
Set fld = tdf.CreateField("Name", dbText)
'set property of new created field
fld.AllowZeroLength = True
'append the new field to the tabledef
tdf.Fields.Append fld

End Sub