Problem with SELECT INTO query

[npearson713]

Hello, I've got a problem with a query I'm trying to run. This is the abbreviated code:

Private Sub Command0_Click()
Dim val As String
Dim rec As DAO.Recordset
Dim db As Database
Dim rs As Variant
Dim rs1 As Variant
Dim vCode5 As String ' string to contain list of stores
Dim strSQL As String

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT Order_BatchDate FROM tmpCurrentOrderID;")

While Not rec.EOF
val = rec("Order_BatchDate")
rec.MoveNext
Wend

rec.Close

'clear out TempTable
db.Execute "DELETE tmpLOACode5.* FROM tmpLOACode5;"

DoCmd.SetWarnings False
strSQL = "SELECT LOA_Shipping.*, LOA_Orders.Order_HonoredPerson, LOA_Orders.Order_PlantingState, LOA_FuneralHome.FuneralHome_REF1 INTO tmpLOACode5 "
strSQL = strSQL & "FROM (LOA_Shipping INNER JOIN LOA_Orders ON LOA_Shipping.Order_ID=LOA_Orders.Order_ID) INNER JOIN LOA_FuneralHome ON LOA_Orders.FuneralHome_ID=LOA_FuneralHome.FuneralHome_ID "
strSQL = strSQL & "WHERE LOA_Orders.Order_BatchDate ='" & val & "' AND LOA_Shipping.LOA_Shipping_Code135='2' "
DoCmd.SetWarnings True

MsgBox strSQL

Set db = CurrentDb

db.Execute strSQL

[...]

The problem I'm getting is Runtime Error '3010', Table tmpLOACode5 already exists. It highlights the last line I've listed, db.Execute strSQL. The strange thing is that I know the table exists, as I'm intentionally trying to update it! Any ideas?

-Nate

 

[npearson713]

Answered my own question!

Thought I had tried this, but I guess not... I wound up using a DoCmd.OpenQuery on a make table query and everything worked out great. Here's the code now:

Private Sub Command0_Click()
Dim val As String
Dim rec As DAO.Recordset
Dim db As Database
Dim rs As Variant
Dim rs1 As Variant
Dim vCode5 As String ' string to contain list of stores
Dim strSQL As String

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT Order_BatchDate FROM tmpCurrentOrderID;")

While Not rec.EOF
val = rec("Order_BatchDate")
rec.MoveNext
Wend

rec.Close

'clear out TempTable
db.Execute "DELETE * FROM tmpLOACode5"

Set db = CurrentDb

'first of all insert list of projects into table TempTable
DoCmd.SetWarnings False
DoCmd.OpenQuery ("TEST_LOACode5SelectIntoQry")
DoCmd.SetWarnings True

[...]

-Nate