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
    Posts
    1

    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)
    DoCmd.Maximize
    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
    .Source = "SELECT * FROM FACT_PROGRESS_NOTES WHERE [USER_NAME] = '" _
    & strUser & "'"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .Open
    End With
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing
    End Sub

    Thanks,

    Bill

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,687
    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 if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. Replies: 23
    Last Post: 01-24-2012, 10:46 AM
  2. Why does this recordset become not-updatable
    By bginhb in forum Programming
    Replies: 3
    Last Post: 08-24-2011, 04:29 PM
  3. Recordset not updatable...WHY?
    By jgelpi16 in forum Queries
    Replies: 3
    Last Post: 11-30-2010, 06:40 AM
  4. Recordset not updatable
    By Ogian in forum Forms
    Replies: 3
    Last Post: 10-19-2010, 11:08 PM
  5. The Recordset is not updatable
    By bullet_proof302 in forum Access
    Replies: 2
    Last Post: 11-07-2009, 10: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