ADO Connection to Backend SQL server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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/data*** 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

.

Quantcast