Insert using VBA

[Rowen]

I'm hoping someone can help me, below is my code, to get some data from a table in SQL server and to insert it into different table into an access file. However what I need to add is something like the following line of code:

dbo.tblEstateInspections.EstateInspectionID NOT IN (Select tblEstateInspections.EstateInspection From tblEstateInspections

However I'm not really sure where I can put this, I've tried putting it into the rs1.Open "select ...." section but then I get the message that EstateInspection is an invalid column name. I think this is because the column is coming from a different table. Is it possible I'm going to need to rethink my approach to downloading this data to the access table?

Dim cnTo As New ADODB.Connection, cnFrom As New ADODB.Connection
Dim strInsertCommand As String, commInsert As String
 
'Connection string to the database on the system
cnFrom.Open "Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=******;Initial Catalog=LTest;Data Source=*******\SQLExpress"
            
'Set up the connection string to the database on SQL Server
'Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DB_From & _ ";
 Jet OLEDB:Database Password=;"

cnTo.ConnectionString = commInsert
cnTo.Open commInsert
 
'Insert the data from the database to the database on sql server
Dim rs1 As New ADODB.Recordset
 
'Insert Estate Inspections data

rs1.Open "Select * From dbo.tblEstateInspections Where dbo.tblEstateInspections.LobbyStairs IS NULL and dbo.tblEstateInspections.InspectionDate IS NOT NULL", cnFrom, adOpenStatic, adLockOptimistic

While Not rs1.EOF

strInsertCommand = "Insert into tblEstateInspections (EstateInspection, StairID, InspectionDate)" & _
                 "Values (" & rs1("EstateInspectionID") & ", " & rs1("StairID") & ", '" & rs1("InspectionDate") & "') "
                    
                 
' Debug.Print strInsertCommand
cnTo.Execute (strInsertCommand)
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing

 

[teball20]

Observation:
(1) the problem might encompass your syntax use for referencing the recordset fields
(2) I commonly use one of the following:
rs1!InsepctionDate --> bang operator OR
rs1.Fields("InspectionDate") -->explicit naming OR
rs1.Fields(0) --> enumerate with integers for each field
(3) I can't imagine your current use will survive which mah lead to your invalid column error

good luck...

strInsertCommand = "Insert into tblEstateInspections (EstateInspection, StairID, InspectionDate)" & _
"Values (" & rs1("EstateInspectionID") & ", " & rs1("StairID") & ", '" & rs1("InspectionDate") & "') "