[AccessD] Connect to SQL and retrieve records

Darren D wrote:
> Hi
>
> Thanks for the post Jeff
>
> When I first copied and pasted your code - the debugger failed on "con =
> setconnection"
>
> So it rem'd "Option Explicit" and it worked - Any suggestions on how to get it
> to work with Explicit on?
>
> Also - When I ran it after that I got the following error...
>
> "-2147467259 [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified"
>
> Any suggestions on that one?
>
> Obviously I have somehow stuffed the connection info - Was your code designed to
> go hand in hand with Jim's earlier connection string post?
>
> Thanks again
>
> Darren
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Demulling Family
> Sent: Wednesday, 31 October 2007 1:03 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Connect to SQL and retrieve records
>
> Darren,
>
> Here is one way:
>
> Dim con As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmdtext As String
>
> cmdtext = "SELECT"
> cmdtext = cmdtext & " tblSEILinks.SEIAccountNumber"
> cmdtext = cmdtext & " FROM tblSEILinks"
> cmdtext = cmdtext & " GROUP BY tblSEILinks.SEIAccountNumber;"
>
> con = setconnection
> con.Open
>
> rs.Open cmdtext, con
>
> If Not rs.BOF And Not rs.EOF Then
>         rs.MoveFirst
>
>         Do Until rs.EOF
>             rs.MoveNext
>         Loop
> End If
>
> rs.Close
> con..Close
> set rs = Nothing
> set con = Nothing
>   
Sorry about that, I have a function that I use to set the connection string.

Function setconnection()
'This is for trusted connections  
setconnection = "Provider=SQLOLEDB.1;Persist Security Info=True;User 
ID=<<User Id Here>>;Initial Catalog=<<Database Name Here>>;Data 
Source=<<Server Name Here>>;Trusted_Connection=Yes"
'This is for non trusted connections
setconnection = "Provider=SQLOLEDB.1;Persist Security Info=True;User 
ID=<<User Id Here>>;Initial Catalog=<<Database Name Here>>;Data 
Source=<<Server Name Here>>;Password=<<Password Here>>

End Function