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.

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



"Song Su" <(E-Mail Removed)> wrote in message
news:24FC8B19-1AAD-4D86-B013-(E-Mail Removed)...
> 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.

"Song Su" <(E-Mail Removed)> wrote:

>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/

"Tony Toews [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Song Su" <(E-Mail Removed)> wrote:
>
>>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/

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.

On Fri, 1 Jan 2010 21:39:21 -0800, "Song Su" <(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Fri, 1 Jan 2010 21:39:21 -0800, "Song Su" <(E-Mail Removed)> wrote:
>
>>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.
> --
>
> John W. Vinson [MVP]

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)

On Fri, 1 Jan 2010 23:19:29 -0800, "Song Su" <(E-Mail Removed)> wrote:

> 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?
--

John W. Vinson [MVP]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Fri, 1 Jan 2010 23:19:29 -0800, "Song Su" <(E-Mail Removed)> wrote:
>
>> 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?
> --
>
> John W. Vinson [MVP]

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.

On Jan 2, 4:19*pm, "Song Su" <son...@live.com> wrote:
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in messagenews:(E-Mail Removed)...
>
> > On Fri, 1 Jan 2010 23:19:29 -0800, "Song Su" <son...@live.com> wrote:
>
> >> * * * * * *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?
> > --
>
> > * * * * * * John W. Vinson [MVP]
>
> 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.

<(E-Mail Removed)> wrote in message
news:27a0ffc8-78a1-487b-bb07-(E-Mail Removed)...
> On Jan 2, 4:19 pm, "Song Su" <son...@live.com> wrote:
>> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>> > On Fri, 1 Jan 2010 23:19:29 -0800, "Song Su" <son...@live.com> wrote:
>>
>> >> 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?
>> > --
>>
>> > John W. Vinson [MVP]
>>
>> 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.

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.