| Author |
ADO Connection to Backend SQL Server
|
|
| ashleycvernon@gmail.com 2006-12-11, 7:16 pm |
| 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;DR
IVER={SQL
Server};SERVER=MySer
ver;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
| |
| Erland Sommarskog 2006-12-11, 7:16 pm |
| ashleycvernon@gmail.com (ashleycvernon@gmail
.com) writes:
> 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?
I guess there is, but you should ask in Access newsgroup where they
might know this. I even more as clueless then you are when it comes to
Access.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Dave Patrick 2006-12-12, 12:16 am |
| 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;DR
IVER={SQL
> Server};SERVER=MySer
ver;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
>
| |
| ashleycvernon@gmail.com 2006-12-12, 12:16 am |
| 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:[color=darkred
]
> 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:
| |
| Dave Patrick 2006-12-12, 12:16 am |
| 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
| |
| ashleycvernon@gmail.com 2006-12-12, 7:14 pm |
| I seem to have figured out a way to display this to the user using an
ADO connection...
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...
| |
| Dave Patrick 2006-12-13, 12:15 am |
| Now you've given the users the plain text credentials to link to your DB and
do as they please.
--
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 seem to have figured out a way to display this to the user using an
> ADO connection...
>
> 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...
>
|
|
|
|