StaRKeY
starkey at wanadoo.nl
Sun Jul 11 15:45:41 CDT 2004
Hi Arthur,
Well for starters I am playing around with ADO and application roles and
trying to figure out what I can use best when not wanting users to have
permissions on a SQL Server database except the one to connect to a certain
database. This can be accomplished by using an application role. It would
also be possible to use DAO but ADO is a bit more enhanced though DAO is
optimized for Access, ohwell:-)
Here's an interesting link about application roles and SQL Server combined
with Access:
http://www.winnetmag.com/SQLServer/Articles/ArticleID/20534/pg/1/1.html
There is no real difference with queries/tables as a recordsource since the
use a recordset makes the form bound the way you are familiar with. I just
found out that reports in an MDB do not support the ADO recordset binding
(haven't tried DAO yet) so ohwell:-) ADP is another option I have and should
do the trick though I haven't got it running yet. What makes it all a bit
nasty is all the versions and different techniques to get the job done... So
much for plain and simple:-)
Recordset advantages can be... Unless you use a pass-through query,
networkload and performance. Less Server-connections if you make use of
'disconnected' recordsets. On the other hand, I think that pass-through
queries do not work with subforms... Not sure if a recordset will but I
haven't read about this yet or tried this. The thing is I'd like to work as
much unbound as possible and as secure as possible and have little SQL
server user maintenance.
I'd advise you to start sniffing around a little since I haven't got that
much experience myself yet and it's a pretty big workfield to explore.
Having DotNet as the next generation platform and thin clients etc... ADO
knowledge will be necessary and I am running a bit behind:-)
Regards,
Eric Starkenburg
-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Arthur Fuller
Verzonden: zondag 11 juli 2004 21:26
Aan: 'Access Developers discussion and problem solving'
Onderwerp: RE: [AccessD] Need ADO ADP help
Just out of curiosity, what is/are the advantage(s) of using a recordset
rather than a query or a bound form? I've been reading this thread
sporadically, but have never used a recordset for either a form or a report.
What significant powers am I missing due to this ignorance? So do you do
this because it's difficult or impossible to create a query that does the
same thing?
TIA,
Arthur
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Sunday, July 11, 2004 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Need ADO ADP help
No -- I haven't tried it in either version -- only used the Form's Recordset
property -- didn't know the Report property was limited.
Susan H.
Have you actually tried this Susan?:-)
In an Access 2002 whitepaper I found the following and it matches my case in
Access 2003 thinking MS has not yet covered this:
In Microsoft Access 2002, it is now possible to use ADO recordsets with
reports in Microsoft Access project files. Unfortunately, the use of the
report Recordset property is limited to project files. If you try to set or
retrieve a report's Recordset property in a Jet database (.mdb) file, you
receive the following error message: 'This feature is not available in an
MBD'.
Regards,
Eric
-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Susan Harkins
Verzonden: zondag 11 juli 2004 18:08
Aan: 'Access Developers discussion and problem solving'
Onderwerp: RE: [AccessD] Need ADO ADP help
Ofcourse, if someone knows a work-around for the report ADO 'problem' in my
MDB that would also be just fine though I'd rather do not want to use ODBC
DNS.
=============I may not have followed the exact problem -- but Access
2003 supports using Recordset objects as source for forms and reports --
using the Report object's Recordset property. And, the Recordset can be ADO.
Susan H.
Recordset Property
See AlsoApplies ToExampleSpecificsReturns or sets the ADO Recordset or DAO
Recordset object representing the record source for the specified form,
report, list box control, or combo box control. Read/write.
expression.Recordset
expression Required. An expression that returns one of the objects in
the
Applies To list.
Remarks
You cannot use this property with ODBCDirect recordset types in DAO.
The Recordset property returns the recordset object that provides the data
being browsed in a form, report, list box control, or combo box control. If
a form is based on a query, for example, referring to the Recordset property
is the equivalent of cloning a Recordset object by using the same query.
However, unlike using the RecordsetClone property, changing which record is
current in the recordset returned by the form's Recordset property also sets
the current record of the form.
This property is available only by using Visual Basic.
The read/write behavior of the Recordset property is determined by the type
of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the
recordset identified by the property.
Recordset type Based on SQL data Based on Jet data ADO Read/Write Read/Write
DAO N/A Read/Write
The following example opens a form, opens a recordset, and then binds the
form to the recordset by setting the form's Recordset property to the newly
created Recordset object.
Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient rstSuppliers.Open "Select *
>From Suppliers", _ CurrentProject.Connection, adOpenKeyset,
adLockOptimistic Set Forms("Suppliers").Recordset = rstSuppliers End Sub
Use the Recordset property:
To bind multiple forms to a common data set. This allows synchronization of
multiple forms. For example,
Set Me.Recordset = Forms!Form1.Recordset
To use methods with the Recordset object that aren't directly supported on
forms. For example, you can use the Recordset property with the ADO Find or
DAO Find methods in a custom dialog for finding a record.
To wrap a transaction (which can be rolled back) around a set of edits that
affect multiple forms.
Changing a form's Recordset property may also change the RecordSource,
RecordsetType, and RecordLocks properties. Also, some data-related
properties may be overridden, for example, the Filter, FilterOn, OrderBy,
and OrderByOn properties.
Calling the Requery method of a form's recordset (for example,
Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh
the data in a form bound to a recordset, set the RecordSource property of
the form to itself (Forms(0).RecordSource = Forms(0).RecordSource).
When a form is bound to a recordset, an error occurs if you use the Filter
by Form command.
Example
The following example uses the Recordset property to create a new copy of
the Recordset object from the current form and then prints the names of the
fields in the Debug window.
Sub Print_Field_Names()
Dim rst As DAO.Recordset, intI As Integer
Dim fld As Field
Set rst = Me.Recordset
For Each fld in rst.Fields
' Print field names.
Debug.Print fld.Name
Next
End Sub
The next example uses the Recordset property and the Recordset object to
synchronize a recordset with the form's current record. When a company name
is selected from a combo box, the FindFirst method is used to locate the
record for that company, causing the form to display the found record.
Sub SupplierID_AfterUpdate()
Dim rst As DAO.Recordset
Dim strSearchName As String
Set rst = Me.Recordset
strSearchName = CStr(Me!SupplierID)
rst.FindFirst "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
End If
rst.Close
End Sub
The following code helps to determine what type of recordset is returned by
the Recordset property under different conditions.
Sub CheckRSType()
Dim rs as Object
Set rs=Forms(0).Recordset
If TypeOf rs Is DAO.Recordset Then
MsgBox "DAO Recordset"
ElseIf TypeOf rs is ADODB.Recordset Then
MsgBox "ADO Recordset"
End If
End Sub
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_____
avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet
besmet.
Virus Gegevensbestand (VPS): 0428-1, 09-07-2004
Getest op: 11-7-2004 18:31:59
avast! auteursrecht (c) 2000-2004 ALWIL Software.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_____
avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet besmet.
Virus Gegevensbestand (VPS): 0428-1, 09-07-2004
Getest op: 11-7-2004 22:45:40
avast! auteursrecht (c) 2000-2004 ALWIL Software.