2nd db connection from 12/21

[janetb]

I have an odbc connection to an sql database backend. Everything's working
fine. But, just for a small section of code, is it possible to modify the
code below and connect to another sql database in another city for which I
have an account and password?

Dim mySql as string
mySql="select email, name from dbo_table where eid=" & forms!myForm!eid

Set db = CurrentDb() 'Probably need to change it here.....
Set rs = db.OpenRecordset(mySql, dbOpenDynaset, dbSeeChanges)
Do While Not rs.EOF
strMsg = "This is a final reminder blah....." & _
vbCrLf & Trim(rs("name"))
DoCmd.SendObject acSendNoObject, , , strTo, strCC, , strSubject,
strMsg, False
rs.MoveNext
rs.Close
Loop

DoCmd.Close

 

[Ron Weiner]

Never used DAO to connect to a Sql Back end but if you can use ADO this
ought to get you going, if the server accepts remote connections on the
standard Port (I think it is Port 1433).

Dim strCon As String, strSql As String
Dim cn As ADODB.Connection, rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
strSql = "SELECT columns FROM table " _
& "WHERE whatever ORDER BY columns"
strCon = "ODBC;" _
& "DRIVER=SQL Server;" _
& "SERVER=ServerName -OR- IP Address;" _
& "UID=UserName;" _
& "PWD=Password;" _
& "DATABASE=DatabaseName"
cn.ConnectionString = strCon
cn.Open
rs.Open strSql, cn, adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
' Your Code Here
rs.MoveNext
Loop
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

The only semi difficult thing here is to get the Connection String right for
your server. There is a great resource for Connection strings here
http://www.connectionstrings.com/

Depending you your connection to the remote server this could be a pretty
slow operation, and again depending on your connection all of this data
could be traveling on a public line in the CLEAR. You will need to add some
error trapping, and a reference to ADO in your database to make it all work.

Good luck with your project.