Re: Need help creating temporary table from filter

to make a table, just run the SQL statement, as

CurrentDb.Execute strSQLMakeTable, dbFailOnError

btw, if you're creating the table repeatedly, then you must be deleting the
table as well, correct? it might be more efficient to simply create the
table once, and then use code to run a Delete query and Append query to
empty the table of records and add a new set of records, as needed. keep in
mind that either option will cause the database to bloat, so you should
compact/repair it regularly. also, consider *why* you're putting records
into a separate table as a subset of existing data; if you can accomplish
what you need to do by basing your process on the SELECT query, then dumping
the data into a separate table is probably a waste of time.

hth


"Darhl Thomason" <darhlt@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:e1x0OHBMGHA.2012@xxxxxxxxxxxxxxxxxxxxxxx
I want to build a temporary table based on a filter in my code. I used
the
query designer to build the query and when I run it from there, it builds
the table fine.

The problem is getting it in to my code. I want to build the table when I
click a button so I can use that for another process. Right now I'm just
trying to get the table to build. I've read the help files in Access but
I
know I am missing something. The code I'm using is below, I just need
some
direction in actually getting it to create the table. I tried using the
DoCmd.OpenTable, and think that's what I want to do, but just can't get it
to work.

Thanks!

Darhl

Private Sub cmdMakeTable_Click()
strSQLMakeTable = "SELECT tblStoreData.Number, tblStoreData.City,
tblStoreData.OpenDate, " & _
"tblDistrict.DomName, tblDistrict.DomEmail, tblFC.*,
tblOwners.* " & _
"INTO tblTemp " & _
"FROM tblRegion INNER JOIN (tblOwners " & _
"INNER JOIN (tblFC " & _
"INNER JOIN ((tblDistrict " & _
"INNER JOIN tblStoreData " & _
"ON tblDistrict.DistrictID = tblStoreData.DistrictID)
"
& _
"INNER JOIN tblDMA ON tblStoreData.DMAID =
tblDMA.DMAID)
" & _
"ON tblFC.FCID = tblStoreData.FCID) " & _
"ON tblOwners.OwnerID = tblStoreData.OwnerID) " & _
"ON (tblRegion.RegionID = tblDistrict.RegionID) " & _
"AND (tblRegion.RegionID = tblStoreData.RegionID) " &
_
"WHERE " & strFilter
' DoCmd.OpenTable "tblTemp", acViewDesign, acAdd = strSQLMakeTable
End Sub




.