I've done a fair amount of access programming in the past but that was 8 years ago ! So I a little rusty ! ( a little hmmm might be too soft a word)


Code might be a little messy as I've been trying many different combinations!
Here is the problem !

Since I have 2 active connections the Left join complains about not seeing the other active connection! I would guess there is a better way to do an Left join using 2 active connections !

++++++++++++++++++++++++++++++++++++++++++
Option Compare Database

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='userid here';pwd='pwdhere'"
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("\\hotelserver\hotel.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



rec_web.Filter = "Email Like '*@*'"
MsgBox "here is a test " + rec_web!email
Dim strmessage As String


MsgBox "here is a recordcount = " + strmessage






End Sub

++++++++++++++++++++++++++++++++++++++++++




I had to remove the where clause down to a filter statement because that
caused another error, but using it as a filter works !