Insert into statement with tables from 2 databases

[ijamal]

Dear members,

I have two databases, Source and destination ;

strSrceDB = "C:\WSS_DB\Rehabilitated_Water_Supply_Kulyob.mdb"

StrDestDB = "C:\DB\WSS_Khatlon.mdb"

I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).

My code is as follows:


Private Sub CommandButton1_Click()

Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database

On Error GoTo ErrorHandler
strSrceDB = "C:\WSS_DB\Rehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:\DB\WSS_Khatlon.mdb"

'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")

ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else

strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"

Set mdb = DBEngine.OpenDatabase(StrDestDB)
Debug.Print strSQL
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("The Geodatabase tables have been successfully appended!")
End If
Exit Sub

ErrorHandler:
strTemp = Err.Description & " [Update_SystemTab]"
Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub

I get Syntax Error in "Insert into" clause.

Any help on this will be most appreciated.

Thanks in advance.

Irshad

 

[gromit]

Hi -

First try building your SQL statement as an insert query from one table to another in the same database. Build and test in a query design window before developing the code.

My first guess is that you are missing some spaces in the SQL statement (such as after commas).

- g

 

[ijamal]

Need to use setlink

Hi g,

I have found out that I cannot have more than one database pathname using the "IN" clause. I will need to create a link to the other database and then use the append statement. I know how to do this. I will sum this once I have succeeded.

Thanks,

Irshad