[AccessD] SPAM-LOW: Re: Binding forms and controls directly to SQL Server

Very cool.  I'll have to try that. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 23, 2010 12:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SPAM-LOW: Re: Binding forms and controls directly
to SQL Server

OK, I got it working using the ADO recordset thing.  Basically I just do
the typical ADO recordset:

Private Function GetADORst(strsql)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    'Create a new ADO Connection object
On Error GoTo Err_GetADORst

    Set cn = New ADODB.Connection

    'Use the Access 10 and SQL Server OLEDB providers to
    'open the Connection
    'You will need to replace MySQLServer with the name
    'of a valid SQL Server
    With cn
       .Provider = "Microsoft.Access.OLEDB.10.0"
       .Properties("Data Provider").Value = "SQLOLEDB"
       .Properties("Data Source").Value = "M90"
       .Properties("Integrated Security").Value = "SSPI"
'      .Properties("User ID").Value = "sa"
'      .Properties("Password").Value = ""
       .Properties("Initial Catalog").Value = "C2DbBilling"
       .Open
    End With

Then open the recordset

    'Create an instance of the ADO Recordset class, and
    'set its properties
    Set rs = New ADODB.Recordset
    With rs
       Set .ActiveConnection = cn
'      .Source = "SELECT * FROM tblTime"
       .Source = strsql
       .LockType = adLockOptimistic
       .CursorType = adOpenKeyset
       .Open
    End With

then assign that as the recordset.

    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing

Voila, editable form.  And FAST!

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> I seem to remember Charlotte saying that you could open an ADO 
> RECORDSET and bind the form to that recordset to make the form
read/write.
> 
> John W. Colby
> www.ColbyConsulting.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************