RE: Bind ADO recordset from SQL2K query to recordsource for form's datasheet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Billy Yao [MSFT] (v-binyao_at_online.microsoft.com)
Date: 06/07/04 

Date: Mon, 07 Jun 2004 09:45:31 GMT

Hi Sparky,

Thank you for your update! After reviewing the whole thread, I understand that you would like to bind Access
Forms to ADO Recordset(s), where the back-end database is a SQL Server database.

Based on my experience, there are limitations when binding forms to the ADO record. (The previous
method "Dynamically set the RecordSource property" seems good, but it meets some performance
issues) In Access 2000, you may meet with the known issue that the bound form is read-only:

227053 ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/?id=227053

In Access 2002 and later, to bind a Microsoft Access form to a recordset, you must set the Recordset
property of the form to a valid Data Access Objects (DAO) or ADO Recordset object. However, there are
still two main requirements for supporting updateability when you bind a form to an ADO recordset that is
using SQL Server data:

The ADO recordset's connection must use the Microsoft Access 10.0 (Access 2002) OLEDB provider as its
service provider.
The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.

When you create ADO recordsets within Microsoft Access, you have a choice as to which ADO connection
will be used by the recordset. Your ADO code can share the ADO connection that Microsoft Access is using
for the SQL Server database currently open in an Access project (ADP) file; or you can programmatically
create a new ADO connection to a different SQL Server database.

For detailed code to bind the form to an ADO recordset (SQL Server), please refer to the topic "Opening a
Separate ADO Connection" in the following KB article #281998:

281998 How to Bind Microsoft Access Forms to ADO Recordsets
http://support.microsoft.com/?id=281998

For the second issue of "a datasheet to be filled with the recordset", I recommend you use the Form
Datasheet View to solve this issue if feasible.

If there is anything else we can assist you with, please feel free to post it in the group. Thanks for your
cooperation.

Best regards,

Billy Yao
Microsoft Online Support
--------------------------------------
Please reply to newsgroups only. Thanks!

Relevant Pages

  • Re: Loading Data Tables in C#
    ... ADO Recordsets? ... Do you mean you want a client to update when changes are made? ... This, however, is not normal, as web apps are disconnected. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Closing ADODB sub reference outs in VIs? (LV 8.5.1)
    ... The idea is to call that first to create the Parameters object, ... I've tried using ADO recordsets to insert large datasets, ... Not sure how ADO does this. ... But even if there is no wire comming out of a reference property, ...
    (comp.lang.labview)
  • Re: VB6 Access97
    ... I retrieve and manipulate the data using ADO and all works very well. ... Access has builtin functionality to export data to text files. ... ADO recordsets have a Save method to allow them to be saved to files, ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Stored procedure executes twice
    ... I have used profiler that is how I discovered it was running twice. ... All access to the DB is through ADO Recordsets ... >> The ADO recordset executes stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: move computer to ou
    ... you bind with WinNT using the NetBIOS name and bind with LDAP using the DN. ... The NameTranslate interface is used in many environments/languages, ... The LDAP provider exposes all attributes of the computer object. ... NetBIOS name) is to use ADO to search AD. ...
    (microsoft.public.windows.server.scripting)