Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: Left join error using 2 different active connections

    Hi ,
    I've done a fair amount of MSaccess programming, but that was 8 years ago and I quite rusty. I have an application that I run on a local PC that need to access an ODBC data source on a web server and update the info from a MDF table on another remote computer . I'm trying to use a Left join but an having difficulty using to active connections!

    Here is the code I'm using , it may be a little messy as I've been trying differnt combinations!

    ==================================
    Private Sub Command2_Click()
    Dim dbs_chaletsR As Database
    Dim dbs_chaletsW As Database
    Dim rec_rest As ADODB.Recordset
    Dim rec_web As ADODB.Recordset
    Dim strtable1 As String

    Dim strSQLw As String
    Dim strSQLr As String
    Dim dbs_con As ADODB.Connection
    Dim dbs_con2 As ADODB.Connection
    strSQLr = "SELECT TableA.NumA, TableA.Nom, TableA.Prenom, TableA.Vip, TableA.Adr, TableA.Vil, TableA.Pro, TableA.Pays, TableA.Cp, TableA.Telmi from TableA WHERE ((TableA.Email) Like '*@*') "
    strSQLw = "SELECT * from email "
    Set dbs_con = New ADODB.Connection
    Set dbs_con2 = New ADODB.Connection

    'dbs_con.ConnectionString = "Provider='OLEDATABASE='chalets_test';DSN='testcha let2';uid='chalets_web';pwd='melissa2600'"
    dbs_con.ConnectionString = "DSN=testchalet2"
    dbs_con2.ConnectionString = "DSN=chalets_dsn"
    dbs_con.Open
    dbs_con2.Open
    Set rec_web = New ADODB.Recordset
    Set rec_rest = New ADODB.Recordset
    strtable1 = "email"
    strtable2 = "TableA"
    rec_rest.Open strtable1, dbs_con, adOpenKeyset, adLockOptimistic
    MsgBox "here is a test " + rec_rest!email


    'Set dbs_chaletsR = OpenDatabase("\\main_comp\hotel\hotelsoft.mdb")

    'rec_web.Open "SELECT NumA, Nom,Par,Prenom, Vip, Adr, Vil, Pro, Pays, Cp, Telmi, Email FROM TableA", dbs_con2
    rec_web.Open "SELECT TableA.NumA, TableA.Nom, TableA.Prenom, TableA.Vip, TableA.Adr, TableA.Vil, TableA.Pro, TableA.Pays, TableA.Cp, TableA.Telmi, email.email, TableA.Email FROM TableA LEFT JOIN email ON TableA.Email=email.email", dbs_con2

    'WHERE ((email.email) Is Null)
    Dim intPublisherCount As String
    intPublisherCount = rec_web.RecordCount

    rec_web.Filter = "Email Like '*@*'"
    MsgBox "here is a test " + rec_web!email
    Dim strmessage As String
    strmessage = "Orders in original recordset: " & _
    vbCr & intPublisherCount & vbCr & _
    "Orders in filtered recordset email without = " & vbCr & _
    rec_web.RecordCount

    MsgBox "here is a recordcount = " + strmessage






    End Sub


    ==================================


    I can create a query with the query wizard with no problem
    But I can't use the SQL from that query in VB !

    The code from the wizard query is this

    ---------------------------------------------
    SELECT TableA.NumA, TableA.Nom, TableA.Prenom, TableA.Vip, TableA.Adr, TableA.Vil, TableA.Pro, TableA.Pays, TableA.Cp, TableA.Telmi, email.email, TableA.Email
    FROM TableA LEFT JOIN email ON TableA.Email=email.email
    WHERE (((email.email) Is Null) AND ((TableA.Email) Like "*@*"));

    ---------------------------------------------
    I also tried using the where part from the query in VB but had to put it in a filter instead because of errors!

    If someone can explain how I can use 2 active connections or at least suggest another way of doing this, I'd really appreciate it!

    I wrote a web application in ASP and made the mistake of telling my client "
    hey no problem I can extract your MSaccess info no problem, won't take me more than 2-3 hours! "

    Oh well ! I'll keep my mouth shut next time ! ...... ( probably not ! LOL)


    thanks
    Pete
    Last edited by peterg2000; 10-06-09 at 01:11.

Posting Permissions

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