Query Access

E

Ed Wyche

I am using VB.net 2008. I have a access database with 2 tables in it
tblTeacher and tblStudent. I create a recordset (rsTeacher) one of the
fields (TeacherName) in tblTeacher using distinct. Then I loop through the
rsTeacher and then create another recordset (rsSchedule) and pull the Period
and StudentID then I want to use that StudentID to query the tblStudent with
the StudentID to get the StudentLastName, StudentFirstName and etc. then
order by period, studentlastname, studentfirst.

I usually cheat and use the query function in Access to do them but it
doesn't seem like this works.

strSQL1 = "SELECT tblStudent.StudentLastName, tblStudent.StudentFirstName, "
& _
'"tblStudent.Username, tblStudent.StudentID,
tblStudent.HomeFolder, " & _
'"tblStudent.PicturePath, tblTeacher.Period,
tblTeacher.TeacherName, rsTeacher_Distinct.TeacherName " & _
'"FROM (rsTeacher_Distinct INNER JOIN tblTeacher ON " & _
'"rsTeacher_Distinct.TeacherName=tblTeacher.TeacherName)
INNER JOIN tblStudent " & _
'"ON tblTeacher.StudentID=tblStudent.StudentID WHERE
(((tblTeacher.Period)=" & _
'Chr(34) & Trim(Str(intPeriod)) & Chr(34) & ") AND
((rsTeacher_Distinct.TeacherName)=" & _
'Chr(34) & (rsTeacher_Distinct.Fields("Teachername").Value)
& Chr(34) & _
'")) ORDER BY tblTeacher.Period, tblStudent.StudentLastName,
tblStudent.StudentFirstName;"

If someone can tell me where I am going wrong that would be great.

Thanks
Ed
 
S

Scott M.

Ed Wyche said:
I am using VB.net 2008. I have a access database with 2 tables in it
tblTeacher and tblStudent. I create a recordset (rsTeacher) one of the
fields (TeacherName) in tblTeacher using distinct. Then I loop through
the
rsTeacher and then create another recordset (rsSchedule) and pull the
Period
and StudentID then I want to use that StudentID to query the tblStudent
with
the StudentID to get the StudentLastName, StudentFirstName and etc. then
order by period, studentlastname, studentfirst.

I usually cheat and use the query function in Access to do them but it
doesn't seem like this works.

strSQL1 = "SELECT tblStudent.StudentLastName, tblStudent.StudentFirstName,
"
& _
'"tblStudent.Username, tblStudent.StudentID,
tblStudent.HomeFolder, " & _
'"tblStudent.PicturePath, tblTeacher.Period,
tblTeacher.TeacherName, rsTeacher_Distinct.TeacherName " & _
'"FROM (rsTeacher_Distinct INNER JOIN tblTeacher ON " & _
'"rsTeacher_Distinct.TeacherName=tblTeacher.TeacherName)
INNER JOIN tblStudent " & _
'"ON tblTeacher.StudentID=tblStudent.StudentID WHERE
(((tblTeacher.Period)=" & _
'Chr(34) & Trim(Str(intPeriod)) & Chr(34) & ") AND
((rsTeacher_Distinct.TeacherName)=" & _
'Chr(34) & (rsTeacher_Distinct.Fields("Teachername").Value)
& Chr(34) & _
'")) ORDER BY tblTeacher.Period,
tblStudent.StudentLastName,
tblStudent.StudentFirstName;"

If someone can tell me where I am going wrong that would be great.

Thanks
Ed
Click to expand...

First, why are you using Recordsets? In ADO .NET, the Recordset is a
dinosaur that doesn't exist anymore. There is no direct replacement, but
the DataTable and / or DataSet objects come closest.

Second, if you are using Visual Studio .NET, you can design your query
visually and make manual edits if needed by dragging an AccessDataSource
control onto a UI and then going through the configuration wizard.

-Scott
 
Top