locked
Me.RecordsetClone: works in Access 2010, not in Access 2016 RRS feed

  • Question

  • All

    I switched over to Access 2016 and came to conclusion code below does not work anymore. I've tried on multiple machines inside my company: on computers with Access 2010 it does work, on computers running Access 2016 the "Select ODBC Source" pops up (the windows which can be found under - control panel - administrative tools - Data Sources (ODBC)).  Reference "Microsoft ActiveX Data Objects 2.8 Library" must be enabled.

    Sample code below has been reduced to the essential: when the main form opens, it makes a connection to a MS SQL database, picks up some data from table1 and assigns the data to the form. Next I click on a button named cmdClone. This code makes a clone of form's data into a recordset named RS. In Access 2010 this works as expected and the code continues. In Access 2016 the ODBC windows pops up.

    Does someone have a solution? I use similar code in multiple databases and it turns out all of them have the same issue in Access 2016.

    Regards
    Kris

    Option Compare Database

    Private Sub Form_Load() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String Set cn = ADODB_Connection strSQL = "select * from table1" Set rs = ADODB_Recordset(strSQL, cn) Set Me.Recordset = rs.Clone End Sub

    Private Sub cmdClone_Click() Dim rs As New ADODB.Recordset Set rs = Me.RecordsetClone 'here the code pops up the "ODBC Select dialog box" in Access 2016 whilst it continues in Access 2010 rs.MoveFirst MsgBox ("done") End Sub

    Public Function ADODB_Connection() As ADODB.Connection 'Make an ADO-connection to a MS SQL-Server Set ADODB_Connection = New ADODB.Connection With ADODB_Connection .Provider = "sqloledb" .Properties("Data Source").Value = "MySQLServer" .Properties("Initial Catalog").Value = "MyDatabase" .Properties("user ID").Value = "UserID" .Properties("Password").Value = "Password" .Open End With End Function

    Public Function ADODB_Recordset(sSQL As String, con As ADODB.Connection) As ADODB.Recordset 'Retrieve an ADO-recordset from a given SQL-string, ADO-connection Set ADODB_Recordset = New ADODB.Recordset With ADODB_Recordset .CursorLocation = adUseClient .LockType = adLockOptimistic .Open sSQL, con End With End Function



    • Edited by kvhoof Wednesday, June 8, 2016 9:12 AM
    Wednesday, June 8, 2016 8:55 AM

Answers

  • As you already clone the recordset once:

    Set Me.Recordset = rs.Clone

    .. you don't have to do it twice so change :

    Set rs = Me.RecordsetClone

    to

    Set rs = Me.Recordset

    .. and it works with Access 2016


    Best regards, George

    • Marked as answer by kvhoof Friday, June 10, 2016 6:29 AM
    Thursday, June 9, 2016 10:14 AM

All replies

  • Easiest way to let many people try to solve this is if you can supply an example database file.

    Best regards, George

    Wednesday, June 8, 2016 1:29 PM
  • You cannot use Me. unless it refers to a specific control on a form or report. RecordsetClone and Recordset are not a controls on a form or report. Refer to Access Help to learn how to use the recordset clone command.
    Wednesday, June 8, 2016 1:33 PM
  • Vote for that but then it implies it's a bug while working in MS Access 2013! Or?

    Best regards, George

    Wednesday, June 8, 2016 1:35 PM
  • But why does it work on all Access 2010 installations and no Access 2016?  It is exactly the same database.
    Wednesday, June 8, 2016 1:42 PM
  • That's what I'm asking Lawrence!

    Best regards, George

    Wednesday, June 8, 2016 1:43 PM
  • Can you supply an example database for us to test?


    Best regards, George

    Wednesday, June 8, 2016 1:51 PM
  • First of all, change your VBA IDE options:

    and add an Option Explicit to any existing module manually. Otherwise you'll get interesting behavior when you misspell your variable or parameter names.

    Then for using ADODB you need an explicit reference. When changing the Office version, it is possible that Access could not update those references automatically. So you need to compare those references:

    And last but not least your error:

    Dim rs As New ADODB.Recordset
    Set rs = Me.RecordsetClone  

    Me.RecordsetClone returns a DAO.Recordset. Not a ADODB.Recordset. It never did. You need:

    Dim rs As New DAO.Recordset
    Set rs = Me.RecordsetClone 


    Wednesday, June 8, 2016 2:16 PM
  • I have always thought the use of Me. needed to refer to a control. Apparently, that is not the case. Sorry, my mistake.
    Wednesday, June 8, 2016 2:20 PM
  • Me is the form, in C++ its 'this'

    Best regards, George

    Wednesday, June 8, 2016 2:28 PM
  • Me refers to the current object, form/report.  That is why intellisense provides you with the names of all the controls within the object when you type Me. it knows the collection associated with the current object.

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, June 8, 2016 6:12 PM
  • The tip of Stefan Hoffmann to define the variable as DAO.Recordset neither works.  This results in another error message (even in Access 2010).

    I now put the testdatabase on WeTransfer.  You can download it here.  Instructions what to do are on the main form which starts up. Don't forget to enable the code and to modify the SQL parameters. 

    If you run the database on Access 2010 you'll see everything will work. It does not on Access 2016 and you can only get rid of the ODBC Dialog box by ending the msaccess.exe process in task manager.


    • Edited by kvhoof Thursday, June 9, 2016 6:44 AM
    Thursday, June 9, 2016 6:41 AM
  • Hi kvhoof,

    please check the suggestion given by the Stefan Hoffmann and make the changes according to the suggestion given by him.

    try to run your form again and check it worked or not.

    if not then please let us know and try to provide your demo copy of database. so that we can try to test on our side and try to reproduce this issue.

    if your issue solved then I would recommend you to mark the suggestion as an answer that helped you to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 9, 2016 6:46 AM
  • I just put the test database on WeTransfer. See my reply above.
    Thursday, June 9, 2016 6:48 AM
  • As you already clone the recordset once:

    Set Me.Recordset = rs.Clone

    .. you don't have to do it twice so change :

    Set rs = Me.RecordsetClone

    to

    Set rs = Me.Recordset

    .. and it works with Access 2016


    Best regards, George

    • Marked as answer by kvhoof Friday, June 10, 2016 6:29 AM
    Thursday, June 9, 2016 10:14 AM
  • Hi kvhoof,

    please check the suggestion given by the George.B.Summers and check it with Access 2016.

    if not then please let us know so that we can provide you further help.

    if your issue solved then I would recommend you to mark the suggestion as an answer that helped you to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 10, 2016 5:59 AM
  • George

    Thanks. This solved indeed the problem.  The only difference is that with my original code in Access 2010 the recordset was set at first record and with this code it seems the cursor is set at the position of the original.  So depending the needs, it might be necessary to use next code.

    set rs=me.recordset
    if rs.recordcount > 0 then
    rs.movefirst
    end if

    Friday, June 10, 2016 6:29 AM
  • None of this seems to resolve the original issue. Which to me is either a bug or an undocumented change in the way things work. The fact is I have a lot of code and suddenly, when trying to switch to 2016, I CAN NO LONGER REFERENCE THE RECORDSETCLONE PROPERTY WHEN USING ADO! At all! It is simply not available where it is available in 2010.

    Would anybody at Microsoft like to address this issue? Is this a bug? Can we expect to see it (the recordsetclone property) back? Or is it no longer going to be available when using ADO?


    Kim

    Saturday, April 15, 2017 1:39 AM
  • Hi MrKMosher,

    I try to use the Recordset.clone using code mentioned above on Access 2016.

    I find that on my side it is working.

    tested code:

    Option Compare Database
    Option Explicit
    
    Private Sub Command0_Click()
    Dim cn As ADODB.Connection
    Dim rs, rs2 As ADODB.Recordset
    Dim strSQL As String
    Set cn = ADODB_Connection
    strSQL = "select * from UTLogs"
    Set rs = ADODB_Recordset(strSQL, cn)
    Set rs2 = rs.Clone
    Debug.Print ("Total Records:- " & rs2.RecordCount)
    Debug.Print ("Field 1:- " & rs2.Fields(1).Name)
    
    End Sub
    
    Private Sub Form_Load()
    
    End Sub
    Public Function ADODB_Connection() As ADODB.Connection
    'Make an ADO-connection to a MS SQL-Server
    Set ADODB_Connection = New ADODB.Connection
    With ADODB_Connection
        .Provider = "sqloledb"
        .Properties("Data Source").Value = "10.168.177.50"
        .Properties("Initial Catalog").Value = "DemoDB"
        .Properties("user ID").Value = "sa"
        .Properties("Password").Value = "1010"
        .Open
    End With
    End Function
    Public Function ADODB_Recordset(sSQL As String, con As ADODB.Connection) As ADODB.Recordset
    'Retrieve an ADO-recordset from a given SQL-string, ADO-connection
    Set ADODB_Recordset = New ADODB.Recordset
    With ADODB_Recordset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open sSQL, con
    End With
    End Function
    
    
    

    Output:

    Make sure you add the reference to Microsoft ActiveX Data Objects 2.8 Library. it is also work with Microsoft ActiveX Data Objects 6.1 Library. I tested with both and both working.

    please try to make a test using code above. if it is not working then try to update your MS Access 2016 and try again.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 17, 2017 1:17 AM
  • Nowhere in the code you have listed is there any reference to the recordsetclone object. I don't know anything about a recordset.clone object (which you mention but is not in your code either.)


    Kim

    Tuesday, April 18, 2017 7:54 PM
  • Hi MrKMosher,

    you had mentioned that,"Nowhere in the code you have listed is there any reference to the recordsetclone object."

    may be you did not view the code properly.

    then you had mentioned that,"I don't know anything about a recordset.clone object"

    for more information regarding recordset.clone refer link below.

    Clone Method (ADO)

    Form.RecordsetClone Property (Access)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 12:21 AM
  • There is no reference to the RecordsetCLONE object in your code only recordset.

    What the original article here was talking about, and what I'm talking about, is the Recordsetclone property of the Form Object of Microsoft Access.


    Kim

    Wednesday, April 19, 2017 10:47 PM
  • Hi MrKMosher,

    I again make a test and this time try to clone the Forms Recordset.

    I find that it is working correctly on my side on MS Access2016.

    Private Sub Command0_Click()
    
        Dim rst As Recordset, intI As Integer
        Dim fld As Field
     
        Set rst = Me.RecordsetClone
        For Each fld In rst.Fields
            ' Print field names.
            Debug.Print fld.Name
            MsgBox (fld.Name)
        Next
    
    
    End Sub

    Output:

    Reference:

    Form.RecordsetClone Property (Access)

    if you still have an issue then try to post your code here. I will make a test on my side.

    try to install the latest update of MS Office and try to run the code again.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 25, 2017 7:37 AM
  • I no longer have the code. I am using a DAO passthrough query instead.

    Kim

    Tuesday, April 25, 2017 5:07 PM
  • Hi MrKMosher,

    you had mentioned that,"I no longer have the code. I am using a DAO passthrough query instead."

    so here, as per my testing results I can say that Recordset.clone is working properly in Access 2016 and there is no bug.

    it is possible that , there is some issue with your machine that can be corrected by repairing / reinstalling the Applications.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 26, 2017 12:46 AM