>I am using MS Access forms as a front end to a backend SQL Server
> Database.  I am trying to use an ADO connection to return a Select
> Statement from the SQL Server to an Access form to be viewable by the
> user.  I have been able to establish the connection to the SQL Server
> and have verified that the SQL statement is correct.  I am completely
> new to ADO and I can't figure out how to display the data returned in
> the ADO recordset.  Could someone please help me out with this?  Is
> there a way to display the returned recordset in an Access
> form/datasheet to be viewable by the user?  Below is my code for your
> reference:
>
> Option Compare Database
> Option Explicit
> Private Sub Form_Open(Cancel As Integer)
>
>    Dim Conn As Connection
>    Dim RS As Recordset
>    Dim LOC
>    Dim SQL
>
>    LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID;PWD=MyPWD"
>
>        SQL = _
>        "SELECT * From tblClaim"
>
>        Set Conn = CreateObject("ADODB.Connection")
>        Set RS = CreateObject("ADODB.Recordset")
>
>        Conn.Open LOC
>        RS.Open SQL, Conn, adOpenKeyset
>
>            Me.RecordSource = RS
>
>        RS.Close
>        Set RS = Nothing
>        Conn.Close
>        Set Conn = Nothing
> End Sub
>

> Much simpler to make use of the linked tables. Make the form's source the
> linked table or use an access query based on the linked table.
>
> --
>
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
> <ashleycver***@gmail.com> wrote:
> >I am using MS Access forms as a front end to a backend SQL Server
> > Database.  I am trying to use an ADO connection to return a Select
> > Statement from the SQL Server to an Access form to be viewable by the
> > user.  I have been able to establish the connection to the SQL Server
> > and have verified that the SQL statement is correct.  I am completely
> > new to ADO and I can't figure out how to display the data returned in
> > the ADO recordset.  Could someone please help me out with this?  Is
> > there a way to display the returned recordset in an Access
> > form/datasheet to be viewable by the user?  Below is my code for your
> > reference:
> >
> > Option Compare Database
> > Option Explicit
> > Private Sub Form_Open(Cancel As Integer)
> >
> >    Dim Conn As Connection
> >    Dim RS As Recordset
> >    Dim LOC
> >    Dim SQL
> >
> >    LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> > Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID;PWD=MyPWD"
> >
> >        SQL = _
> >        "SELECT * From tblClaim"
> >
> >        Set Conn = CreateObject("ADODB.Connection")
> >        Set RS = CreateObject("ADODB.Recordset")
> >
> >        Conn.Open LOC
> >        RS.Open SQL, Conn, adOpenKeyset
> >
> >            Me.RecordSource = RS
> >
> >        RS.Close
> >        Set RS = Nothing
> >        Conn.Close
> >        Set Conn = Nothing
> > End Sub
> >

> Thanks Dave,
>
> I am currently utilizing the linked tables to pass information to and
> from the SQL server, but wanted to move to ADO connections so I could
> get rid of the tables in the Access application.  One of the tables
> contains password info, so I didn't want there to be any way for a user
> to access this table.  Maybe ADO isn't the way around this; any links
> or tips on securing linked tables in Access would be much appreciated.
>
> -Ashley

>I seem to have figured out a way to display this to the user using an
> ADO connection...
>
> Private Sub Form_Open(Cancel As Integer)
>    Dim c As ADODB.Connection
>    Dim r As ADODB.Recordset
>
>
>    Set c = New ADODB.Connection
>    With c
>        .ConnectionString = "PROVIDER=SQLOLEDB;" & _
>        "DRIVER={SQL Server};" & _
>        "SERVER=MyServer;" & _
>        "DATABASE=MyDBase;" & _
>        "UID=MyUID;" & _
>        "PWD=MyPwd"
>        .CursorLocation = adUseClient
>        .Open
>    End With
>
>
>    Set r = New ADODB.Recordset
>    With r
>        .ActiveConnection = c
>        .Source = "SELECT * From tblClaim"
>        .Open
>    End With
>
>
>    Set Me.Recordset = r
>    Text0.ControlSource = r.Fields("PrimaryKey").Name
>
>
> End Sub
>
>
> This seems to work as long as you create a form with unbound text boxes
>
> then set the control source of the text boxes equal to the name of the
> corresponding field in the recordset...
>