Problem executing a make table query in vb.net

[Neil Robbins]

I am trying to execute a make table query in my vb.net program. The db that
I am accessing is an Access 2000 format db. The SQL code that I am using has
been cut and pasted from the SQL View having designed the query in the
design view of Access.

The code in question is below, I apologise for the length of the SQL
statement

Dim SqlComText As String = "SELECT TblCentreSessio n.ID,
TblCentreSessio n.Session AS [SessionID], TblSession.Sess ionYear AS [Session
Year], TblSession.Sess ionSeason AS [Session Season], TblCentre.Centr eName AS
[Centre Name], TblExaminer.Exa minerCode AS [Examiner Code],
TblExaminer.Exa minerSName AS [Examiner Surname], TblExaminer.Exa minerMName
AS [Examiner Middle Name], TblExaminer.Exa minerFName AS [Examiner First
Name], TblSyllabus.Syl labusName AS [Syllabus Name],
CLng([TblCandidate].[CandidateID]) AS [Candidate ID], TblCandidate.SN ame AS
[Candidate Surname], TblCandidate.MN ame AS [Candidate Middle Name],
TblCandidate.FN ame AS [Candidate First Name], CLng([TblSubject].[SubjectID])
AS [Subject ID], TblSubject.Subj ectName AS [Subject Name],
CLng([TblGrade].[GradeID]) AS [Grade ID], TblGrade.GradeN ame AS [Grade
Name], TblExamResults. Result, TblExamResults. AdjResult1 AS [Adjusted Result
1], TblExamResults. AdjResult2 AS [Adjusted Result 2], tblNote.Notes AS
[Adjustment Notes] INTO [Results Table] IN '" & ChosenFilename & "' FROM
tblNote RIGHT JOIN (((((TblCentreS ession LEFT JOIN TblCentre ON
TblCentreSessio n.Centre = TblCentre.Centr eCode) LEFT JOIN TblSession ON
TblCentreSessio n.Session = TblSession.Sess ionID) LEFT JOIN TblSyllabus ON
TblCentreSessio n.Syllabus = TblSyllabus.Syl labusID) LEFT JOIN TblExaminer ON
TblCentreSessio n.Examiner = TblExaminer.Exa minerCode) RIGHT JOIN
(((TblCandidate RIGHT JOIN TblExamResults ON TblCandidate.Ca ndidateID =
TblExamResults. CandidateID) LEFT JOIN TblSubject ON TblExamResults. Subject =
TblSubject.Subj ectID) LEFT JOIN TblGrade ON TblExamResults. Grade =
TblGrade.GradeI D) ON TblCentreSessio n.ID = TblExamResults. CentreSession) ON
tblNote.ExamID = TblExamResults. ID WHERE (((TblCentreSes sion.Session)=" &
chosenSessionID & ") AND ((TblExamResult s.Result) Is Not Null And
(TblExamResults .Result)>0)) ORDER BY TblCentreSessio n.ID"

Dim cnn1 As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;Jet
OLEDB:System Database=" & systemdblocatio n & ";Data Source=" &
examdblocation & ";User ID=" & User & ";Password= " & PWord)

Dim cmd1 As New OleDb.OleDbComm and
cnn1.Open()

Dim runningcount As Integer = 0

Try
With cmd1
.Connection = cnn1
.CommandText = SqlComText
.ExecuteScalar( )
End With
Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try

cnn1.Close()

I have tried executing it but each time I get the following error:

"System.Data.Ol eDb.OleDbExcept ion: IErrorInfo.GetD escription failed with
E_FAIL(0x800040 05).
at System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32
hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteScalar( )
at MYDB.ExportForm .btnExport_Clic k(Object sender, EventArgs e) in
C:\Documents and Settings\Admini strator\My Documents\Visua l Studio
Projects\myDB\m yDB\ExportForm. vb:line 146;"

Any help in understanding what I am doing wrong, or instructions on how to
do this better would me greatly appreciated.

[Cor]

Peter,

Great.

As I have said that in this thread I hate SQL and this is one of the
reasons, so many impossible reserved words

Cor