none
Binding form to ADO recordset results in a "Select Data Source" popup RRS feed

  • Question

  • My computer was recently updated to Windows 10 / Office 2013 (32-bit) and I'm now experiencing some strange issues when attaching an ADO recordset to a form. I am attempting to query an Access backend database (accdb) and assigning the ADO recordset to the form's recordset so that it can later be disconnected. This process worked without issue on Windows 7 / Office 2010, but now I receive a pop-up to "Select Data Source" (for a DSN) whenever I try accessing the Me.Recordset through the Locals window, through a Me.Recordsetclone, etc.

    I have pared down my code to the bare essentials and the error still occurs. The code below is tied to a frontend form with a single button.

    Private Sub Command3_Click()
        
    Dim conn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim strConn As String
    
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\test.accdb;"
        conn.Open strConn
        rec.Open "SELECT * FROM tblTest", conn, adOpenStatic, adLockReadOnly
        Set Me.Recordset = rec
        Set rec = Me.RecordsetClone
        
    End Sub

    I have the "Microsoft ActiveX Data Objects 6.1 Library" (C:\Program Files(x86)\Common Files\System\ado\msado15.dll) enabled in my references, but the above example also fails with late binding.

    I've tried the code above on several computers with Office 2013 (both 32-bit and 64-bit) and receive the same error. I've also tried different variations on the connection string, comparing object properties on the 2010/2013 versions, etc., but no luck. I'm guessing that this is somehow related to database engines and provider versions but I'm not too savvy in this area. Any ideas?


    • Edited by leprendun Monday, February 13, 2017 8:29 PM formatting
    Monday, February 13, 2017 8:29 PM

Answers

  • Hi,

    I could reproduce your issue using your code,

    To fix it, please use

     Set Me.Recordset = rec
        Set rec = Me.Recordset.Clone

    Regards,

    Celeste


    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, February 15, 2017 9:24 AM
    Moderator

All replies

  • Did you try a different provider number,

      strConn = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=C:\Test\test.accdb;"

    I think that 12.0 is for Access 2010, 14.0 is for Access 2013 and 15.0 for Acess 2016, but I'm not completely sure.

    Matthias Kläy, Kläy Computing AG

    Tuesday, February 14, 2017 12:45 AM
  • Thanks, I should have mentioned that I tried this as well. Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.15.0 give me the issue I described in my original post. Providers 13 and 14 return a run-time error (3706: Provider cannot be found). Provider 16.0 results in an instant crash (no error message, the application just closes).

    I scanned by registry and I have a key for Access Connectivity Engine in the following locations:

    HKCU\SOFTWARE\Microsoft\Office\15.0\Access\
    HKLM\SOFTWARE\WOW6432Node\Microsoft\Office\15.0\Access\
    HKLM\SOFTWARE\WOW6432Node\Microsoft\Office\15.0\
    HKLM\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\

    Tuesday, February 14, 2017 4:44 PM
  • Hi,

    I could reproduce your issue using your code,

    To fix it, please use

     Set Me.Recordset = rec
        Set rec = Me.Recordset.Clone

    Regards,

    Celeste


    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, February 15, 2017 9:24 AM
    Moderator