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