ADO Connection to Backend SQL Server

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
<ashleycvernon@.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
Dave Patrick wrote:[vbcol=seagreen]
> 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
> <ashleycvernon@.gmail.com> wrote:
|||Personally I'd do the security on SQL server rather than trying to do in
Access. I'd use windows authentication rather than sql logins. You could
also link to a view rather than the table and setup security on the view
based on windows user and or windows group association.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
> 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