Consulting

Results 1 to 3 of 3

Thread: ERROR JOIN, ACCES ,SQL...

  1. #1
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    3
    Location

    ERROR JOIN, ACCES ,SQL...

    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!

  2. #2
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    3
    Location
    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

  3. #3
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    3
    Location
    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •