Re: ADO Connection to Backend SQL server

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


<ashleycvernon@xxxxxxxxx> wrote in message
news:1165898984.536393.144590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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:

Remove this part from your connection string:

DRIVER={SQL Server};

After that, and assuming client system is able to resolve the name
"myserver", the specified login has been granted access to MyDBase, and its
password is correct, it should work.

Using Enterprise Manager or Query Analyzer to connect to the server/db, as a
reality check on the server name can save some time.

Use the CLICONFG.EXE tool to establish server aliases and configure other
client-side protocol details. Or if the server's host name can be resolved
by DNS, fully qualify the server name (or use its IP.)


-Mark



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