vba - Access form linked to disconnected ADODB.Recordset: save changes -

i trying set form use disconnected adodb.recordset source.
issue have changes not saved original access table upon closing form , replying "yes" prompt. missing ?

note: please don't tell me method useless, it's poc local table, plan try later more "distant" recordset.

dim conn adodb.connection dim rs adodb.recordset  private sub form_load()     set conn = new adodb.connection     conn.open currentproject.connection      set rs = new adodb.recordset     rs         rs.cursorlocation = aduseclient         rs.open "select * amspor", conn, adopenstatic, adlockbatchoptimistic         set rs.activeconnection = nothing     end     set me.recordset = rs     conn.close end sub  private sub form_unload(cancel integer)     select case msgbox("save changes ?", vbquestion + vbyesnocancel)     case vbno         'do nothing     case vbyes         conn.open currentproject.connection         rs.activeconnection = conn         rs.updatebatch         rs.close         conn.close         set conn = nothing     case vbcancel         cancel = true     end select end sub 

---

steps reproduce:

• take small table has primary key
• generate automatic form it
• save form.
• add above code form, replacing table name in select clause.
• empty record source property of form.
• save , close form.
• you can open form , make changes data. upon close, prompted saving changes.

---

edit: wonder if issue might in currentproject.connection ?
in debug window, typed ? currentproject.connection , got following:

provider=microsoft.ace.oledb.12.0;user id=admin;data source=\\xxxxxx\yyyy$\documents\ams.accdb;mode=share deny none;extended properties="";jet oledb:system database=c:\users\g828992\appdata\roaming\microsoft\access\system.mdw;jet oledb:registry path=software\microsoft\office\14.0\access\access connectivity engine;jet oledb:database password="";jet oledb:engine type=6;jet oledb:database locking mode=1;jet oledb:global partial bulk ops=2;jet oledb:global bulk transactions=1;jet oledb:new database password="";jet oledb:create system database=false;jet oledb:encrypt database=false;jet oledb:don't copy locale on compact=false;jet oledb:compact without replica repair=false;jet oledb:sfp=false;jet oledb:support complex data=true;jet oledb:bypass userinfo validation=false 

i came here looking same answer , after tons of googling , trial , error able perform attempting do. understand old post did not see answers provided answer allow attempting work. use example , try , apply had change , add work properly.

dim rs adodb.recordset dim conn adodb.connection  private sub form_load()     if currentproject.connection.state = adstateopen currentproject.connection.close      set conn = new adodb.connection     conn.open currentproject.connection.connectionstring      set rs = new adodb.recordset     rs.cursorlocation = aduseclient     rs.open "select * amspor", conn, adopenforwardonly, adlockbatchoptimistic      if not rs nothing         if not rs.activeconnection nothing set rs.activeconnection = nothing         if not (rs.eof , rs.bof)             set me.recordset = rs         end if          if conn.state = adstateopen             conn.close         end if     end if      call addnewrecord(me.recordset) end sub   private sub addnewrecord(byref rs adodb.recordset)     on error resume next       if not rs nothing         if rs.supports(adaddnew)             rs.addnew             rs.fields("firstname").value = "john"             rs.fields("lastname").value = "doe"              if rs.supports(adupdate) rs.update         end if     end if      if err.number <> 0         debug.print "addnewrecord err msg: " & err.description         err.clear     end if end sub   private sub form_unload(cancel integer)     select case msgbox("save changes ?", vbquestion + vbyesnocancel)     case vbyes         call updatedbwithrs(me.recordset)     case vbcancel         cancel = true     case else         ' nothing.     end select end sub   private sub updatedbwithrs(byref rs adodb.recordset)     if not rs nothing         if currentproject.connection.state = adstateopen currentproject.connection.close          set conn = new adodb.connection         conn.open currentproject.connection.connectionstring         rs.activeconnection = conn          if rs.supports(adupdatebatch)             rs.updatebatch              if not conn nothing                 if conn.state = adstateopen conn.close                 set conn = nothing             end if             if not rs nothing                 if rs.state = adstateopen rs.close                 set rs = nothing             end if         end if     end if end sub 

with code above able add record recordset , verify did not show in database table. when performed updatedbwithrs record had added recordset, previously, pushed database table.

the biggest changes had code changing conn.open currentproject.connection conn.open currentproject.connection.connectionstring, adding in code if currentproject.connection.state = adstateopen currentproject.connection.close fix error receiving connection being opened. final biggest change made replacing cursortype of adopenstatic adopenforwardonly. not sure if last change required used based on disconnected recordset example found on microsoft support site.