recordset with temporary table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Is there a way to create the recordset without using the temporary table?
Click to expand...

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.)


4110 said:
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
Click to expand...
 
Back
Top