Re: 2nd db connection from 12/21
- From: "Ron Weiner" <weinNoSpam1@xxxxxxxxxxxxxx>
- Date: Thu, 29 Dec 2005 12:47:13 -0500
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.
--
Ron W
www.WorksRite.com
"janetb" <janetb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:965A84D9-2744-4D27-BBE1-FBDD201D0B5A@xxxxxxxxxxxxxxxx
> 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
>
.
- Prev by Date: Re: Tables in Current DB
- Next by Date: Re: Form's Icon
- Previous by thread: values passed in OpenArgs not displaying in text boxes on called f
- Next by thread: Re: How can i Focus a field?
- Index(es):
Relevant Pages
|
