ADO Connection to Backend Sql server

[ashleycvernon]

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

[Lyle Fairfield]

as***********@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.

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

[ashleycvernon]

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

[Lyle Fairfield]

as***********@gmail.com wrote:

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

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

[Jim Devenish]

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
as***********@gmail.com wrote:

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