Create a tmp table from recordset

[Song Su]

How to create a temp table based on each record in the recordset?

I want to use the temp table to do something else, then, need temp tablefrom
next record from the recordset.

Thanks.

 

[Larry Linson]

The design approach you describe is, almost certainly, not a good one.

If we understood what information you have and what you are trying to
accomplish, not how you hope to accoplish it, we might be in a position to
really be of help.

Otherwise, I fear, we'd be encouraging you to implement something that would
cause you trouble later.

Larry Linson
Microsoft Office Access MVP

 

[Tony Toews [MVP]]

How to create a temp table based on each record in the recordset?

I want to use the temp table to do something else, then, need temp tablefrom
next record from the recordset.

This is quite unusual. Please explain in more detail what else you
want to do with the temp table.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Song Su]

This is quite unusual. Please explain in more detail what else you
want to do with the temp table.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

I have instructor table and student table. I want to create student roster
in Excel file and email to each instructor. From instructor table, I want to
create temp table with 1 record. Use this temp table to link students table
to create roster. Loop this process for all the instructors. I hope it's
clear. Thanks.

 

[John W. Vinson]

I have instructor table and student table. I want to create student roster
in Excel file and email to each instructor. From instructor table, I want to
create temp table with 1 record. Use this temp table to link students table
to create roster. Loop this process for all the instructors. I hope it's
clear. Thanks.

You're apparently assuming that you must have a *table* in order to export to
Excel. That assumption is incorrect; it's perfectly possible and normal to
export a *query* to Excel. See the VBA help for TransferSpreadsheet.

 

[Song Su]

You're apparently assuming that you must have a *table* in order to export
to
Excel. That assumption is incorrect; it's perfectly possible and normal to
export a *query* to Excel. See the VBA help for TransferSpreadsheet.

Hi, John:

It's not simple export to Excel. I need to put course information on top of
Excel rows and copy student info below. Here are some of my code:

strYear = rs!YYYY ' Year
strSem = rs!SEMESTER ' Semester
strSubj = rs!Subj ' Course Subject
strNo = rs!No ' Course No
strSect = rs!Sect ' Section
strEmail = rs!Email ' email address for the instructor

' Create an file name by combining all the above except email so it
is unique for each subject.
strSubject = strYear & " " & strSem & " " & strSubj & " " & strNo &
" - " & strSect
strFileName = strSubject & ".xlsx"

With objXL
.Visible = False 'Hide Excel

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets("Sheet1") 'The step and the
next is important when working with more than one worksheet

Err.Clear

On Error GoTo 0


With objSht
Set rs1 = db.OpenRecordset("tblTmpStudent", dbOpenSnapshot)


'Copy data from the two record sets
On Error Resume Next
.Range("C8").CopyFromRecordset rs1 'Copy students

.Range("D1").Value = rs!YYYY & " " & rs!SEMESTER 'Copy
header info
.Range("D2").Value = rs!Sect & " " & rs!Subj & " " & rs!No
.Range("D3").Value = rs!Instructor
.Range("D4").Value = rs!Begin

End With

End With
objXL.DisplayAlerts = False

objWkb.SaveAs strPath & "\" & strFileName
objSht.Close
objWkb.Close
Set objSht = Nothing
Set objWkb = Nothing

objXL.DisplayAlerts = True


Call SendEMail(strEmail, "", strSubject, "", "S:\Dirk\" & strFileName,
True)

 

[John W. Vinson]

With objSht
Set rs1 = db.OpenRecordset("tblTmpStudent", dbOpenSnapshot)

Why can't you open a recordset based on a select query for this student,
without the overhead, wasted time, bloat and inefficiency of creating a
separate table?

 

[Song Su]

Why can't you open a recordset based on a select query for this student,
without the overhead, wasted time, bloat and inefficiency of creating a
separate table?

Hi, Vinson:

My instructor table has many records, same as my student table. Each
instructor (course section) has many students enrolled. In order to create
roster, one course section should link to many enrolled students. That's why
I need to create a temp one record instructor table, link to student table
to generate student temp table to be used to copy to Excel starting cell C8.
Instructor temp table info will be copied to Excel starting D1. Send out
roster as email attachment and loop to next instructor.

 

[pietlinden]

Hi, Vinson:

My instructor table has many records, same as my student table. Each
instructor (course section) has many students enrolled. In order to create
roster, one course section should link to many enrolled students. That's why
I need to create a temp one record instructor table, link to student table
to generate student temp table to be used to copy to Excel starting cell C8.
Instructor temp table info will be copied to Excel starting D1. Send out
roster as email attachment and loop to next instructor.

No you don't. If you have all the requisite tables, you can create a
query to return only the records you need. You can then use either
TransferSpreadsheet or CopyFromRecordset to move the records to
Excel. The only reason I can see your needing a temporary table is if
you are doing something that is impossible with a recordset, and so
far you haven't shown me that what you are attempting is impossible.
I only say that because temporary tables can get messy fast.

 

[Song Su]

No you don't. If you have all the requisite tables, you can create a
query to return only the records you need. You can then use either
TransferSpreadsheet or CopyFromRecordset to move the records to
Excel. The only reason I can see your needing a temporary table is if
you are doing something that is impossible with a recordset, and so
far you haven't shown me that what you are attempting is impossible.
I only say that because temporary tables can get messy fast.

Ok. So help me with creating Excel roster.

I have course table with unique section with 2000+ records.
I have student table with unique student ID with section field. It has 7000+
records.

How to create Excel roster (each section for each file) so I can email to
each instructor.

Course information (year, semester, section #, instructor name, begin date)
is copied to Excel roster cell D1, D2, D3, D4

Student list (student name and student ID) will copied from C8 and down on
the same file above.

Thanks.