Save recordset as a table

[DaOtH]

Hi,
I am running below code, with it i try to save the recordset as a table in the database. Creating and populating the recordset works ok, the 'saving' it to a table unfortunately not.
(error = cannot find input table rsTmp (3078))

Sub tmp()
Dim rsTmp As Recordset

Set rsTmp = New Recordset

With rsTmp
.Fields.Append "Uur", adChar, 2
.Fields.Append "UurNum", adInteger
.Fields.Append "SoortItem", adChar, 10
.Open
.AddNew
!Uur = "01"
!UurNum = 1
!SoortItem = "Bier"
.Update
.Close
End With

Qry = "Select rsTmp.Uur, rsTmp.UurNum, rsTmp.SoortItem _
INTO [TEMP] FROM rsTmp;"
DoCmd.RunSQL Qry

str_sql = "DROP TABLE TEMP;"
CurrentDb.Execute str_sql
End Sub
"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[cmmrfrds]

I use ADO instead of DAO but the same principles apply. First, a recordset is not a table. The query is looking for an Access table. You need to append your table to the Access tables collection, after appending the fields that go into the table. Check help for the syntax.

 

[DaOtH]

Already got it, needed to create a temporary table, i succeeded already with populating a recordset but needed it as a table.


Sub Maak_UrenStats_Temp()
Dim dbCurrent As Database
Dim TableName As TableDef

Set dbCurrent = CurrentDb()
Set tableName = dbCurrent.CreateTableDef()

' Create empty table
tableName.Name = "UrenStats"
tableName.Fields.Append tableName.CreateField("Uur", _
dbText, 2)
tableName.Fields.Append tableName.CreateField("UurNum", _
dbByte)
tableName.Fields.Append tableName.CreateField("SoortItem", _
dbText, 7)
With dbCurrent.TableDefs
.Append tableName
.Refresh
End With

' Populate table

Set rsUrenStats = dbCurrent.OpenRecordset("UrenStats&quot
With rsUrenStats
For uur = 0 To 23
.AddNew
If uur < 10 Then !uur = &quot;0&quot; & uur Else !uur = uur
!UurNum = uur
!SoortItem = &quot;Bier&quot;
.Update
Next uur
.Close
End With
End Sub &quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963