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

jwcolby jwcolby at colbyconsulting.com
Tue Mar 23 14:28:30 CDT 2010


Nope.  If I edit an existing record and just close the form, when I reopen the form the changes are 
retained.

However I am not able to create a new record yet.  Working on the why of that now.

John W. Colby
www.ColbyConsulting.com


Doug Murphy wrote:
> HI John,
> 
> Interesting. Do you have to perform an update after changing data? 
> 
> -----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
> 



More information about the AccessD mailing list