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

re: ADO Connection to Backend Sql server

ashleycvernon@gmail.com wrote:Private Sub Form_Open(Cancel As Integer)
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim r As ADODB.Recordset


Set c = New ADODB.Connection
With c
.ConnectionString = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=Some Database;" _
& "DATA SOURCE=Some Data Source;" _
& "USER ID=Some User;" _
& "PASSWORD=Some Password"
.CursorLocation = adUseClient
.Open
End With


Set m = New ADODB.Command
With m
.ActiveConnection = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Transactions"
Set r = .Execute()
End With


With r
.Find "TransactionID = 56"
If .EOF Then .MoveFirst
End With


Set Me.Recordset = r


End Sub

re: ADO Connection to Backend Sql server

I actually ended up going a different direction with this...

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...

re: ADO Connection to Backend Sql server

ashleycvernon@gmail.com wrote:It may be simpler to

1. connect to the sql db via an adp;
2. create the form with the sproc, table, sql string, view, whatever as
the recordsource; the wizard could be used (ugh!);
3. now all the controls have the desired fields as their control
sources;
4. remove the recordsource string;
5. use the form in the adp or import it to wherever;
6. add the form open code that set's the form's recordset to the AD)
recordset

re: ADO Connection to Backend Sql server

I am at a similar stage to this. I can open a recordset and can loop
through it.

What I would also like to be able to do is to copy a table on the
server into a local table on the front-end. What is the simplest way
of achieving this?

Jim


ashleycvernon@gmail.com wrote: