SELECT INTO statement not working in VB.NET, but works when ran directly in Access

I am converting old VB6 code to VB.NET with ADO.NET (OleDB). This is my query that will create a blank table when ran in VB.NET, but then works when ran directly in Access. This code presumably also works in VB6, as I am using the same SQL:

SELECT qryAsOf.name, qryAsOf.type, 0开发者_如何学Go as opt, 0 as swap
INTO qryCon
FROM qryAsOf
LEFT JOIN qryLinked on qryAsOf.c = qryLinked.lc

I feel like this has something to do with the left join and select into being together, but like I said it is only VB that has an issue with it, Access handles it perfectly.

Thanks :)

Edit: more details --

Without the INTO line, this query returns all 600+ rows.

This DOES NOT WORK:

cm.CommandText = "CREATE TABLE qryCon (etc...)"
cm.ExecuteNonQuery()
cm.CommandText = "INSERT INTO qryCon SELECT ..." '(rest of query above without INTO line)
cm.ExecuteNonQuery

This DOES WORK:

cm.CommandText = "CREATE PROC qryCon AS SELECT ..." '(same select as above without INTO, again)
cm.ExecuteNonQuery

The CREATE PROC that does work is fine, except I need to insert data into it later, so I get errors about how I need an updatable table. I really want the end qryCon to be a table, but I can't seem to get that to work :(

*However, when I do something like this (using the stored proc (renamed) above which, if viewed in Access, is full of data)

cm.CommandText = "SELECT * FROM storedProc"
dr = cm.ExecuteReader

while dr.Read
    cm.CommandText = "INSERT INTO qryCon VALUES (dr.GetValue(0), dr.GetValue(1), dr.GetValue(2), dr.GetValue(3))
    cm.ExecuteNonQuery
end while

This DOES NOT WORK! By the way, I removed the concatenation in the query for readability. It is correct in the actual project.

Your VB6/Access syntax functions where you would iterate through the query object itself when parsing the results.

For .Net, you want to leverage the newer ADO.Net library and use a DataReader (or DataAdapter if you need to work with the results more than once in your code without having to make a second trip to the database) object in order to process your query. You can find a nice overview of how to work with them on the MSDN site.

EDIT:

After your question was updated, you want your SQL query to be this:

SELECT qryAsOf.name, qryAsOf.type, 0 as opt, 0 as swap
FROM qryAsOf
LEFT JOIN qryLinked on qryAsOf.c = qryLinked.lc

and then you'll iterate through the results and parse the values through each loop (as found in the MSDN link with something like

If reader.HasRows Then
   Do While reader.Read()
      Console.WriteLine(reader.GetInt32(0) & vbTab & reader.GetString(1))
   Loop       
Else
   Console.WriteLine("No rows found.")
End If

Select into syntax may not be supported in the middleware, which tends to be somewhat generic. The statement might have to be executed in"passthrough" i.e. native-syntax mode.

Since my earlier comment about pass-through mode appears to have been downvoted, perhaps by @onedaywhen, I include this link:

http://msdn.microsoft.com/en-us/library/ms681754(v=vs.85).aspx

Jet OLEDB:ODBC Pass-Through Statement (DBPROP_JETOLEDB_ODBCPASSTHROUGH)

Indicates that Jet should pass the SQL text in a Command object to the back end unaltered.

The documentation seems to suggest that the SQL text can be altered by the middleware unless this feature is invoked. So I repeat, you might want to make sure that oleDB supports SELECT INTO syntax. I'm not saying that SELECT INTO is not supported -- just that it is an issue worthy of the OP's attention.

P.S. When talking to a database via a middle layer that is designed to support a variety of back-ends, it is a good idea, I think, to use pass-through mode when the statement contains a function like IIF() or a UDF, or any syntax, such as SELECT INTO, which is not universally supported.

P.P.S. Here's some additional info that might be relevant: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx