jmayer

jmayer

  • NA
  • 6
  • 0

SELECT INTO Statement issues

Dec 10 2007 10:19 AM

I am trying to to use the SELECT INTO statement to copy data from 2 sql server tables to 1 access table using vb .net 2005.  This statement workes great to quickly copy 1 table to 1 table, but I am having issues with my Joins.  I have a an Orders_tbl and Order_Details_tbl in sql server and want to copy select Order_Details_tbl records based off the status of the Orders in the Orders_tbl.  So I would like to copy all the Order_Details_tbl records whose parent Orders_tbl record has a status of "ORDERED".  Here is the code that I am trying:

Dim oConn As ADODB.Connection

oConn = New ADODB.Connection

oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtPath.Text & "\" & cmbPlant.Text & Format(Now.Date, "MMddyy") & ".mdb;Jet OLEDB:Database Password=smjmpm103;")

Dim sSQL As String

sSQL = "SELECT Orders_tbl.OrderNumber, Order_Details_tbl.AutoOrderDetail INTO Order_Details_Backup_tbl FROM "

sSQL = sSQL & "[odbc;Driver={SQL Server};Server=" & sqllocation & ";Database=" & sqlname & ";Uid=" & sqlun & ";Pwd=" & sqlpw & ";].Orders_tbl INNER JOIN Order_Details_tbl ON Order_Details_tbl.AutoOrder = Orders_tbl.OrderNumber WHERE (Orders_tbl.Status = 'ORDERED')"

oConn.Execute(sSQL)

I am getting a "Type mismatch in expression" exception.  Any suggestions would be greatly appreciated.


Answers (4)