MS Access 2003 - VBA for altering a table after a "SELECT * INTO tblTemp FROM tblMain" statement
Posted
by Justin
on Stack Overflow
See other posts from Stack Overflow
or by Justin
Published on 2010-03-16T15:26:02Z
Indexed on
2010/03/17
10:01 UTC
Read the original article
Hit count: 527
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!
© Stack Overflow or respective owner