store query results into table

[gammaman]

I have a table which I create in vba. I want to then run a sql and store the results into the table. Here is my code, it looks right to me but nothing gets inserted into the table.

Set db = CurrentDb()    

On Error Resume Next
DoCmd.RunSQL ("DROP TABLE [countPrescriptionPlan]")
DoCmd.RunSQL ("Create TABLE [countPrescriptionPlan]")

Set tb2 = db.TableDefs("countPrescriptionPlan")


countColumnsPres = Array("CountofEmplid", "CountofDepBen", "Emplid", "Dep Ben")
 

For Each col In countColumnsPres
   
   
   Set FieldName = tb2.CreateField(col, dbText, 100)
   tb2.Fields.Append FieldName
   Next col

DoCmd.RunSQL ("selectPresPlan")
DoCmd.RunSQL ("countPresPlan")

insString = "INSERT INTO countPrescriptionPlan (CountofEmplid,CountofDepBen,Emplid,Dep Ben)" _
                  & "Select CountOfEMPLID, CountOFDEPENDENT_BENEF, EMPLID, DEPENDENT_BENEF" _
                  & "From countPresPlan"

DoCmd.RunSQL insString

[pbaldy]

Two thoughts right off. First, why delete and recreate the table if it's always the same? Much more efficient to empty it. Second, the inadvisable space in Dep Ben would require it to be bracketed. The SQL is invalid as it is. Third, you need spaces when you have line continuations. Use this to see how the SQL is coming out and test:

http://www.baldyweb.com/ImmediateWindow.htm

Okay, that was three thoughts.

[Perceptus]

I would remove the on error resume next. Also you set your "set db=currentdb", but there is no setting it to nothing at the end.

[pbaldy]

Good point. That line isn't error handling, it's error ignoring. Here are the basics:

http://www.baldyweb.com/ErrorTrap.htm