VB6 and syntax for inner join with a subquery

Microsoft Visual basic professional ed 6...
July 23, 2009 at 09:20:49
Specs: Windows Vista
The following SQL is rejected by VB6 but is accepted by the SQL standard. Is there a way around this?

SELECT S.StudentID
FROM Students S
INNER JOIN
(SELECT A.StudentID
FROM Absence A) SA
ON S.StudentID = SA.StudentID

The error message is: "Syntax error in FROM clause" which is the subquery FROM

I need to use an INNER JOIN because my goal is to avoid using unnecessary GROUP BYs (which occurs when I use a WHERE clause and no subquery). The SQL here can certainly be written without the subquery - my more complicated SQL reqires it because of the GROUP BY.
Thank you
John Jaros
jkjaros@verizon.net


See More: VB6 and syntax for inner join with a subquery

Report •


#1
July 23, 2009 at 12:05:25
It's up to the database to accept or reject the SQL, not VB6. Display the query, and make sure the string VB6 is sending doesn't have a mistake.

Report •

#2
July 23, 2009 at 12:21:30
Thank you for responding to my question. I am using the following connection string to connect to a MIcrosoft Access database. I do NOT use the ADO data control; instead I define a connection object, a command object, and a recordset object and do ALL of my database access via code.

connection string
-------------------------

"Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & THE_DATA_SOURCE & ";" & _
"Persist Security Info=True"

THE_DATA_SOURCE is simply the path name for my MS Access database.

Perhaps I need documentation on how MS Access implements SQL.

Thank you very much.

---John Jaros
jkjaros@verizon.net


Report •

#3
July 23, 2009 at 12:24:30
Perhaps I need documentation on how MS Access implements SQL.
Easy enough
And I was talking about the SELECT string, not the connection string.

Report •

Related Solutions

#4
July 23, 2009 at 14:25:12
Thanks again for your response. The VB6 SQL string that I am using is correct. I have tested it in Visual Data Manager where it is possible to quickly test'execute a SQL query. I get the same error in Visual Data Manager as I get in my VB6 code. I tried your link to the MS help database but all of the Visual Basic forums exclude VB6.

---John Jaros
jkjaros@verizon.net


Report •

#5
July 23, 2009 at 14:56:08
The VB6 SQL string that I am using is correct. I have tested it in Visual Data Manager where it is possible to quickly test'execute a SQL query.
I'm not saying you screwed the query up, I'm implying the query got screwed up when inserted into the VB6 source. It really shouldn't be too hard to say
sql = "SELECT . . ."
Debug.Print sql
Set rs = connection.Execute sql

I tried your link to the MS help database but all of the Visual Basic forums exclude VB6.
Again, SQL is the language of the database. It shouldn't matter if you're using Toad or writing a program in assembly. So long as you get the query to the DB, it's the DB's job to parse it.


Report •

#6
July 24, 2009 at 08:15:17
THank you so much for assisting me. I think that I will investigate using a newer version of VB 6 (mine is from 2000 or earlier) or even a newer version of Visual Studio.Net Perhaps these programs support an implementation of SQL that is more in line with the SQL standard.

---John Jaros
jkjaros@verizon.net


Report •


Ask Question