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]

Hi Neil,

I understand now what you want to do, however I hate SQL.

I do not know how deep Peters knowledge is of that, however meanwhile you
can try to see if you get more information by setting this before the catch
ex as exception (you can let that stay)
\\\
Catch sqlEx As SqlException
MessageBox.Show (sqlEx.ToString )
Catch ex as Exception
.......
End try
///
Maybe you get than some better information.

Cor

[Neil Robbins]

Hi Cor,

Thanks for this I will put the code in and see what happens. I'll post the
results to this thread.

Regards,

Neil R.
"Cor" <no*@non.com> wrote in message
news:un******** ******@TK2MSFTN GP12.phx.gbl...

Hi Neil,

I understand now what you want to do, however I hate SQL.

I do not know how deep Peters knowledge is of that, however meanwhile you
can try to see if you get more information by setting this before the catch ex as exception (you can let that stay)
\\\
Catch sqlEx As SqlException
MessageBox.Show (sqlEx.ToString )
Catch ex as Exception
......
End try
///
Maybe you get than some better information.

Cor

[Neil Robbins]

Hi Cor,

Thanks for this I will put the code in and see what happens. I'll post the
results to this thread.

Regards,

Neil R.
"Cor" <no*@non.com> wrote in message
news:un******** ******@TK2MSFTN GP12.phx.gbl...

Hi Neil,

I understand now what you want to do, however I hate SQL.

I do not know how deep Peters knowledge is of that, however meanwhile you
can try to see if you get more information by setting this before the catch ex as exception (you can let that stay)
\\\
Catch sqlEx As SqlException
MessageBox.Show (sqlEx.ToString )
Catch ex as Exception
......
End try
///
Maybe you get than some better information.

Cor

[Peter Huang]

Hi Neil,

I am sorry if I have any confusion.

But have you tried to run the Query statement in Access Application
directly?
Will you get any error?

If no, you may try to simplify your SqlComText.
e.g. SqlComText="Sel ect * from tblNote"

So that we can isolate the problem to see what cause the problem.
Please make a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang]

Hi Neil,

I am sorry if I have any confusion.

But have you tried to run the Query statement in Access Application
directly?
Will you get any error?

If no, you may try to simplify your SqlComText.
e.g. SqlComText="Sel ect * from tblNote"

So that we can isolate the problem to see what cause the problem.
Please make a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang]

Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Sessio n in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKey words
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbidbinfo__ge tkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang]

Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Sessio n in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKey words
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbidbinfo__ge tkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Neil Robbins]

Hi Peter,

thanks for this. the query does run without a problem in Access, but not in
the .net app. This information about the word 'Session' fits perfectly
though. When I remove it the query runs without a problem.

Thankyou for your help,

Regards,

Neil R.

""Peter Huang"" <v-******@online.m icrosoft.com> wrote in message
news:ET******** ******@cpmsftng xa06.phx.gbl...

Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Sessio n in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKey words
http://msdn.microsoft.com/library/de...us/oledb/htm/o ledbidbinfo__ge tkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Neil Robbins]

Hi Peter,

thanks for this. the query does run without a problem in Access, but not in
the .net app. This information about the word 'Session' fits perfectly
though. When I remove it the query runs without a problem.

Thankyou for your help,

Regards,

Neil R.

""Peter Huang"" <v-******@online.m icrosoft.com> wrote in message
news:ET******** ******@cpmsftng xa06.phx.gbl...

Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Sessio n in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKey words
http://msdn.microsoft.com/library/de...us/oledb/htm/o ledbidbinfo__ge tkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[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