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

Excellent John...

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 23, 2010 10:23 AM
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