Need help creating temporary table from filter

[Darhl Thomason]

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

 

[tina]

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]

Thanks Tina, that helps a ton! You're right, I would also be running code
to repeatedly build this table. So you're saying I should just do an append
to populate the table, then delete the records and not delete the table,
right?

The reason I'm doing this is to take a subset of my data (defined by my
filter) and create form letters that are emailed out. I've already got the
code that creates and sends the email, my plan was to build a table with the
data I need for the form letter and parse through the records creating an
email for each record in the tblTemp.

Any suggestions for a better way to do this would be great!

Thanks again,

Darhl

 

[Marshall Barton]

Even deleting the records in the "temp" table will lead to
bloat. This can be avoided by using a temporary database to
hold the temp table. Here's a good way to do that:
http://www.granite.ab.ca/access/temptables.htm

OTOH, if the table always has the same set of fields, you
can avoid the need for the code to create the temp table by
precreating a database with the table already set up. Then
when you want to use the temp table, delete (Kill) the temp
db and then copy (FileCopy) the precreated db and use that.
Linking to the temp table would be the same as Tony's
article discusses.

 

[tina]

comments inline.

Thanks Tina, that helps a ton! You're right, I would also be running code
to repeatedly build this table. So you're saying I should just do an append
to populate the table, then delete the records and not delete the table,
right?
correct.


The reason I'm doing this is to take a subset of my data (defined by my
filter) and create form letters that are emailed out. I've already got the
code that creates and sends the email, my plan was to build a table with the
data I need for the form letter and parse through the records creating an
email for each record in the tblTemp.

a SELECT query returns a specific dataset, which is what you get when you
work with a table. have you tried using the SELECT query in your "form
letter email" process, *instead of * tblTemp?

hth

 

[Darhl Thomason]

Would that be more efficient than putting it in a temp table?

I'm currently reading up on For Each/Next loops to parse through the dataset
(I'll use that because it should work instead of an extra table or a new
recordset). Is that the best way? I'm also thinking of Do While not
dataset.EOF to parse through.

Thanks again,

Darhl

a SELECT query returns a specific dataset, which is what you get when you
work with a table. have you tried using the SELECT query in your "form
letter email" process, *instead of * tblTemp?

</snip>

 

[Darhl Thomason]

Thanks Marsh,

I like your idea about copying in a pre-created temp db then populate the
fields, then delete the temp db when done. I may choose to use that. I'm
talking with Tina right now about doing it with a recordset instead of
plopping the data into a new table.

Any comments?

Darhl

Even deleting the records in the "temp" table will lead to
bloat. This can be avoided by using a temporary database to
hold the temp table. Here's a good way to do that:
http://www.granite.ab.ca/access/temptables.htm

OTOH, if the table always has the same set of fields, you
can avoid the need for the code to create the temp table by
precreating a database with the table already set up. Then
when you want to use the temp table, delete (Kill) the temp
db and then copy (FileCopy) the precreated db and use that.
Linking to the temp table would be the same as Tony's
article discusses.
--
Marsh
MVP [MS Access]

Thanks Tina, that helps a ton! You're right, I would also be running code
to repeatedly build this table. So you're saying I should just do an
append
to populate the table, then delete the records and not delete the table,
right?

The reason I'm doing this is to take a subset of my data (defined by my
filter) and create form letters that are emailed out. I've already got
the
code that creates and sends the email, my plan was to build a table with
the
data I need for the form letter and parse through the records creating an
email for each record in the tblTemp.

Any suggestions for a better way to do this would be great!

 

[Marshall Barton]

I got so wrapped up in the temp table issue, I didn't even
notice the reason for all this. If you're using Word Mail
Merge to generate the letters, I believe that you can use
the Select query as the source just as easily as a table.
At least give it a try before launching off into all this
temp table stuff.

 

[tina]

using a query to pull a subset of existing table data usually makes a lot
more sense than either 1) repeatedly creating/deleting a temp table, inside
or outside the database, or 2) adding/deleting records to an existing
"holding" table.

if your process didn't use For Each/Next loops on tblTemp, why should you
need to do that when you substitute a SELECT query? as i said before, and as
Marsh said elsewhere in this thread, you can probably use the query *exactly
the same way as you were using tblTemp*. "At least give it a try before
launching off into all this
temp table stuff." <from Marsh's post>

hth

 

[Darhl Thomason]

Marsh,

I was not going to do it with Word Mail Merge, I am planning on doing it
directly from Access using the Outlook 11 object references. I have already
put together the code to create and send the messages, I'm just trying to
figure out a way to do a group of messages at a time instead of one at a
time. The one at a time works great, so I know that code is fine.

Thanks again for your help and suggestions!

Darhl

I got so wrapped up in the temp table issue, I didn't even
notice the reason for all this. If you're using Word Mail
Merge to generate the letters, I believe that you can use
the Select query as the source just as easily as a table.
At least give it a try before launching off into all this
temp table stuff.
--
Marsh
MVP [MS Access]

I like your idea about copying in a pre-created temp db then populate the
fields, then delete the temp db when done. I may choose to use that. I'm
talking with Tina right now about doing it with a recordset instead of
plopping the data into a new table.


"Marshall Barton" wrote

 

[Darhl Thomason]

OK, so I've played with both and I agree that doing it with a new select
query is the way to go.

So, now that I have the data for the email messages, I'm planning on using
For Each/Next to parse through the records to create/send the messages.

Thanks a lot for your advice! It's highly appreciated.

Darhl

 

[tina]

you're very welcome

OK, so I've played with both and I agree that doing it with a new select
query is the way to go.

So, now that I have the data for the email messages, I'm planning on using
For Each/Next to parse through the records to create/send the messages.

Thanks a lot for your advice! It's highly appreciated.

Darhl