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.