Trouble Editing an ADO Recordset bound to an Microsoft Access Form

tommyboy115
tommyboy115 used Ask the Experts™
on
I'm new to using ADO to open recordsets and use them with Access forms.  I typically use queries as the recordsets.  I'm trying to make the move away from that and this is my first attempt.  I'm able to bind the recordset, but can't edit any of the data once available on the form.  It's all locked.  What am I doing wrong?  

Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   Set cn = New ADODB.Connection
   
   With cn
     
      .Provider = "sqloledb"
         
      .ConnectionString = "DRIVER=ODBC DRIVER 11 FOR SQL SERVER;SERVER=**;DATABASE=**;UID=**;PWD=**"
     
      .CursorLocation = adUseServer
     
      .Open
   
   End With
   
   Set rs = New ADODB.Recordset
   
   With rs
     
      .Source = "SELECT * FROM GLOBAL_CONTACTS WHERE CONTACTID = " & [Forms]![DD_GLOBAL]![GLOBAL_CONTACTID]
     
      .ActiveConnection = cn
     
      .CursorType = adOpenKeyset
     
      .LockType = adLockOptimistic
     
      .Open
   
   End With
   
   Set Me.Recordset = rs
   
Form_Open_Exit:
    Exit Sub

Form_Open_Err:
    MsgBox ""
    Resume Form_Open_Exit
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
What version of Access?   It's probably the provider.   You need to use the Access OLEDB provider:

https://support.microsoft.com/en-us/kb/281998

Jim.

Author

Commented:
I'm using Access 2016 and SQL Server 2014

Author

Commented:
Thanks Jim!  That reference worked!  If I want to create a new record instead of looking up an existing one, how should I change my code?  Scold me if I should be asking that in a new question.  Thanks!
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<< If I want to create a new record instead of looking up an existing one, how should I change my code?  >>

 You'd create an empty recordset and let the form add the record.   best way to do that is include a WHERE clause of:

 WHERE 1 = 0

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial