Open recordset to new/empty temp table? 1

[NXMold]

I am building a form to import and validate data from an xls form. I want to establish a recordset and begin looping to import data to a temporary table.

If I use rs.open with a SELECT '' AS Material INTO query I get a blank record.

How do I create the temp table with no records?

 

[GingerR]

Why not just have a temp blank table to begin with, then

Set rsTempTable = Currentdb.OpenRecorset("Select * from TempTable")


rsTempTable.AddNew
'add record
rsTempTable.Update

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[NXMold]

But then the table will be full of records, the next time I run this I would have to delete all records? I guess that could work.

 

[GingerR]

Or else you can write code to create a new table each time with all of the fields you want, fill it up with data, then delete it. Seems less painful to just create a table with the structure you want and just have it there waiting. Either way I think the db will bloat in a similar way; either creating and deleting a table each time, or adding and deleting records from a table each time, both things will bloat the db. Just compact regularly.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[NXMold]

I decided to use a persistant temp table and delete all records each time, its a pretty clean method.

Is there any way to combat bloat (seems prevalant with queries too) besides compact?

 

[GingerR]

not in your case, where you are importing data over and over again. you can set the DB to "Compact On Close" (in the options) so that it compacts every time you close it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html