recordset with temporary table

[Guest]

I have a database for picture data. I am working on a form to get pictures
directly from a digital camera, group and sort the pictures by date, create
folders on the hard drive, rename and copy the files to the folders on the
hard drive and create associated records in the database. I have it working
but it isn't elegant. I am using a temporary table to store information
about the files before I group and sort them. Here is the code to read the
files and populate the temporary table:

'open temporary table tblTemp as a recordset. rs is the recordset for
tblTemp.
Set rs = db.OpenRecordset("tblTemp", dbOpenDynaset)

'get the files
Set files = fs.getfolder(PathToCamera).files

'loop through all the files and add to the temporary table
For Each file In files
rs.AddNew 'prepare the temp table for a new
record
rs(0) = file.Name 'get the file name
rs(1) = file.DateCreated 'get the file date created
rs.Update 'write the record to the
temporary table
Next

'read the temporary table and group and sort by date
strSQL = "SELECT Int([fileDate]) AS [Date] FROM tblTemp GROUP BY
Int([fileDate]) ORDER BY Int([fileDate]);"

Set rsGroupByDate = db.OpenRecordset(strSQL)


:End of code


Is there a way to create the recordset without using the temporary table?

Thanks,

David

 

[Guest]

Is there a way to create the recordset without using the temporary table?

As far as I know, no.

From Help: "A Recordset object represents the records in a base table or
the records that result from running a query."

What is the problem with using a temp table?

You can have the temp table in a different mdb and link the temp table to
your mdb.


BTW, "Date" is a reserved word in Access and shouldn't be used for object
names.
See:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a database for picture data. I am working on a form to get pictures
directly from a digital camera, group and sort the pictures by date, create
folders on the hard drive, rename and copy the files to the folders on the
hard drive and create associated records in the database. I have it working
but it isn't elegant. I am using a temporary table to store information
about the files before I group and sort them. Here is the code to read the
files and populate the temporary table:

'open temporary table tblTemp as a recordset. rs is the recordset for
tblTemp.
Set rs = db.OpenRecordset("tblTemp", dbOpenDynaset)

'get the files
Set files = fs.getfolder(PathToCamera).files

'loop through all the files and add to the temporary table
For Each file In files
rs.AddNew 'prepare the temp table for a new
record
rs(0) = file.Name 'get the file name
rs(1) = file.DateCreated 'get the file date created
rs.Update 'write the record to the
temporary table
Next

'read the temporary table and group and sort by date
strSQL = "SELECT Int([fileDate]) AS [Date] FROM tblTemp GROUP BY
Int([fileDate]) ORDER BY Int([fileDate]);"

Set rsGroupByDate = db.OpenRecordset(strSQL)


:End of code


Is there a way to create the recordset without using the temporary table?

Thanks,

David