Results 1 to 2 of 2

Creating an updatable query recordset using Access 2010

  1. #1
    Bill McCoy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012

    Creating an updatable query recordset using Access 2010

    I am trying to create a recordset in Access 2010 using a query. The query uses global variable that captures the users log-name to filter for the records that they entered. The query executes, but the records are not updatable.

    The code uses ADO. My goal is to split this database to create a multi-user environment. The source table has a primary key and there are no calculated fields.

    Below is the code. Can someone please explain how I can make this recordset updatable.

    Private Sub Form_Open(Cancel As Integer)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    'Use the ADO connection that Access uses
    Set cn = CurrentProject.AccessConnection
    'Create an instance of the ADO Recordset class,
    'and set its properties
    Set rs = New ADODB.Recordset
    With rs
    Set .ActiveConnection = cn
    & strUser & "'"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    End With
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing
    End Sub



  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Why are you using code to set a connection and why code to set form's RecordSet?

    Will the backend of split db be Access file? Set links to the backend tables with UNC pathing. No code needed to set connection. Set form's RecordSource property with the SQL statement.

    Otherwise, with code, consider:
    .LockType = adLockPessimistic
    .CursorType = adOpenDynamic
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 23
    Last Post: 01-24-2012, 11:46 AM
  2. Why does this recordset become not-updatable
    By bginhb in forum Programming
    Replies: 3
    Last Post: 08-24-2011, 05:29 PM
  3. Recordset not updatable...WHY?
    By jgelpi16 in forum Queries
    Replies: 3
    Last Post: 11-30-2010, 07:40 AM
  4. Recordset not updatable
    By Ogian in forum Forms
    Replies: 3
    Last Post: 10-20-2010, 12:08 AM
  5. The Recordset is not updatable
    By bullet_proof302 in forum Access
    Replies: 2
    Last Post: 11-07-2009, 11:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums