Error join, acces ,sql...

[batserra]

Hi everyone...

I have a table in memory ,name table1 and i want to open another database with same table name (table1) and also insert from table in memory records that dont exist in the external database. Records find with TEL_NUM_FACTURA that don't exist.
What i done is next:

connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strpath
Set objConn = CreateObject("ADODB.Connection")
objConn.Open connStr
Dim dbs As Database
Dim cadena As String
cadena = "SELECT [tabla1].TEL_NUM_FACTURA, [tabla1].TEL_NU_TELEFONO, [tabla1].TEL_NU_EXTENSION, [tabla1].TEL_PRO_CARGOS, [tabla1].TEL_SUMA_TOTAL, [tabla1].TEL_DTO_PLAN, [tabla1].TEL_DTO_CLIENTE FROM [tabla1] LEFT JOIN tabla1 ON [tabla1].[TEL_NUM_FACTURA] = tabla1.[TEL_NUM_FACTURA] WHERE (((tabla1.TEL_NUM_FACTURA) Is Null));"
Set dbs = objConn.Execute(cadena)
dbs.Close
Set rs = Nothing
Set objConn = Nothing

Before insert into y have problems with join... and i don't know how to solve it.
Does anybody can help me?

And happy new year...

Thanks!

[batserra]

hi ...

Any idea please?

Also i tryed to do something diferent, before insert i try to do a select ,but also 0 records... two tables have records with same TEL_NUM_FACTURA ,if i do a select * from tabla1 i have records, and also a tel.tel_nu_telefono="SERRA" also i have a record, but no one with left join...

Dim miconexion As New ADODB.Connection
Dim mirecordset As New ADODB.Recordset
Dim instruccion As String
' instruccion = "select * from Tabla1 as t1 where t1.TEL_NU_TELEFONO='SERRA'" ' tabla1 referencia a la tabla de la base de datos externa
' instruccion = "SELECT * FROM Tabla1 as t2 LEFT JOIN Tabla1 as t1 ON t1.[TEL_NUM_FACTURA] = t2.[TEL_NUM_FACTURA] WHERE ((t1.TEL_NUM_FACTURA) Is Null)"
instruccion = "SELECT * FROM Tabla1 as t1 LEFT JOIN Tabla1 as t2 ON t1.TEL_NUM_FACTURA = t2.TEL_NUM_FACTURA WHERE ((t2.TEL_NUM_FACTURA) Is Null)"
' instruccion = "SELECT * from Tabla1 as t1"
miconexion.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strpath
mirecordset.Open instruccion, miconexion, adOpenDynamic, adLockOptimistic
' mirecordset.Open instruccion, miconexion, , , adCmdText
Dim c As Long
c = 0
Do Until mirecordset.EOF
    c = c + 1
    Debug.Print (mirecordset.Fields(1) & ";" & mirecordset.Fields(2) & ";" & mirecordset.Fields(3) & ";" & c)
    ' Debug.Print mirecordset!TEL_NUM_FACTURA & " " & mirecordset!TEL_NU_TELEFONO
    mirecordset.MoveNext
Loop
mirecordset.Close
Set mirecordset = Nothing
miconexion.Close
Set miconexion = Nothing
End Sub

[batserra]

Fixed...

instruccion = "SELECT * from tabla1 as t1 LEFT JOIN (SELECT * FROM " & "[MS Access;PWD=databasePWD;DATABASE=" & strpath & "].tabla1) as t2 ON t1.[TEL_NUM_FACTURA] = t2.[TEL_NUM_FACTURA]  WHERE (((t2.TEL_NUM_FACTURA) Is Null))"
Dim db      As DAO.Database
Dim rs      As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(instruccion)
rs.Close
Set rs = Nothing