VBA - Access 2013 - Programmatically assigning a Form's (DAO RecordSet) Record Source to the result of Stored Procedure (ADO RecordSet) executed on a remote SQL Server triggers “Run time error 31 – Data provider could not be initialized” error message.

Suppose we have this scenario where a local Access database uses Forms bound to SQL server
data sources:

-  a company has developed a front-end application based on MS Access 2007 and VBA, which has been
   distributed to end-users as an ADP file;

-  its back-end data source runs on SQL Server;

-  on the client side, the front-end Access application contains Forms and they are programmatically bound to
   ADO RecordSets;

-  in turn, those RecordSet objects get populated with data when Access is invoking a Stored Procedure on the
   SQL Server;

-  end-users can sort the client-side Access Forms without any problems in Access 2007 and 2010;  

-  however, when migrating to Office 2013 it was found that attempting to sort the bound Forms triggers this
   runtime error:

 "Data provider could not be initialized"  

-  on the other side, if those Forms are bound to a  ADO RecordSets which retrieve their values directly from a
   SQL Table, then sorting also works as expected in Access 2013;

Steps to reproduce the issue:

  1.   create a new SQL database;
  2.  insert a new Table and save it with some name (example: 'dbo.tblSourceData');
  3.   populate the Table with values:

4.   create a new Stored Procedure named 'dbo.TestSP_SQLServer'; Paste this code:

   CREATE PROCEDURE TestSP_SQLServer    AS    BEGIN     SET NOCOUNT ON;      SELECT * FROM dbo.tblSourceData;    END    GO

5.  test your Stored Procedure to verify that it returns the expected results:

6.   make sure your SQL server is accessible from the outside: enable SQL Server Browser service, create Firewall
     rules to allow SQL to be visible on the network, grant remote access to your account;

  [these steps are for preparing the client-side Form]

7.   switch to a client machine running Office 2010; Create a new Access Database;
8.  create a blank Form, then save it as: 'frmSQL_StoredProc_SourceData';

9.   we want to display our Form in a tabular layout, so we'll have to enter Design mode, then select the top-level
     window border, go to its 'Property Sheet', and from the 'Format' tab click on the 'Default View' drop-down
     and select 'Datasheet'; 

     
      Alternatively, you can create a Table with the 'DataSheet' view selected by default by going to the 'Create'
      tab, then to 'More Forms' drop-down and selecting 'Datasheet';

10.   at this point we'll need to add a few TextBox controls into our blank Form; Our SQL table has 7 columns
       therefore we'll add 7 items;
11.   these controls are currently not bound to any data source, so we have to bring up the Property Sheet for
       each of them, then go to the 'Data' tab where we have to write a string value corresponding to the name
       of the SQL Table column a particular TextBox control is bound to;  

12.   from the VBA code editor window, go to 'Tools', 'References' and insert a reference to 'Microsoft ActiveX 
Data Objects 6.1 Library', then paste this code inside a new Module:  

Note: I am using SQL Native Client 2012 to connect to a SQL 2012 back-end server.

Sub Test()

Dim o_rs As New ADODB.RecordsetDim connectionStr As StringDim connection As ADODB.connectionDim cmd As ADODB.Command

'https://msdn.microsoft.com/en-us/library/ms130822.aspx (Using Connection 'String Keywords with SQL Server Native Client)

connectionStr = "Provider=SQLNCLI11.1;" & _                "Server=CRISTISQL2;" & _                "Initial Catalog=TestStoredProcedure;" & _                "User ID=sa;" & _                "Password=123;"

Set connection = New ADODB.connectionconnection.ConnectionString = connectionStrconnection.Open

Set cmd = New ADODB.Commandcmd.ActiveConnection = connectioncmd.CommandType = adCmdStoredProccmd.CommandText = "TestSP_SQLServer"

Set o_rs = New ADODB.Recordset'https://www.access-programmers.co.uk/forums/showthread.php?t=62482'the following line is needed to avoid a runtime error when attempting'to programmatically bind the Form to the 'o_rs' RecordSet object:'_________________________________________________________________' Run-time error '7965':' The object you entered is not a valid Recordset property.'_________________________________________________________________o_rs.CursorLocation = adUseCliento_rs.Open "TestSP_SQLServer", connection, adOpenKeyset, adLockOptimistic

DoCmd.OpenForm "frmSQL_StoredProc_SourceData", acFormDS, , , acFormEdit, acWindowNormal

Set [Form_frmSQL_StoredProc_SourceData].Recordset = o_rsDoCmd.OpenForm "frmSQL_StoredProc_SourceData", acFormDS

'-------------------------------------------------------------------------'Display the data we fetched from SQL in the VBA Immediate window:Dim item_fld As VariantDim strCols As StringstrCols = ""For Each item_fld In o_rs.Fields    strCols = strCols & item_fld.Name & vbTabNextDebug.Print strCols

Dim strItems As StringstrItems = ""Do While Not o_rs.EOF    strItems = o_rs(0).Value & vbTab & o_rs(1).Value & vbTab & o_rs(2).Value    Debug.Print strItems    strItems = ""    o_rs.MoveNextLoop

End Sub

13.   execute the code .. your Form should display these records:

14.   right-click on any Column, then click on 'Sort A to Z' or 'Sort Z to A';

Everything works in Office 2010 ... however step #14 fails when running Access 2013 our action will result in
this error message:

 

Why do we get this error?

It seems that this is not supposed to work ...

I have discussed with a senior Escalation resource from the MS Access support team and I've been told that
internally an Access Form's RecordSet object is built on a design inherited from DAO (for more information
see: https://msdn.microsoft.com/en-us/library/ms810810.aspx - Data Access Technologies Road Map).

In our scenario, we are working with ADO Recordsets connected to SQL, therefore these are not native
Access data objects. Our internal database contains lots of entries for the "Data provider could not be
initialized" error when sorting, filtering, refreshing or requerying ADO Recordsets pointing at SQL Tables
and over the course of time customers have requested a fix.

The Access Product Group carefully evaluated this behavior and decided not to provide a fix up to this in point
in time. This is due to the complexity of the changes that would have to be made in the product (very high dev
and test costs) to fully support ADO Recordsets.

https://msdn.microsoft.com/en-us/library/aa261340(v=vs.60).aspxADO Compared with RDO and DAO------------------------------------------------------------------------------ADO isn't automatically code-compatible with your existing data access applications. While ADO encapsulates the functionality of DAO and RDO, you must convert many of the language elements over to ADO syntax. In some cases, this will mean only a simple conversion of some functions of your existing code. In other cases, it might be best to rewrite the application using ADO's new features.

DAO (Data Access Objects) was the first object-oriented interface that exposed the Microsoft Jet database engine (used by Microsoft Access) and allowed Visual Basic developers to directly connect to Access tables - as well as other databases - through ODBC. DAO is suited best for either single-system applications or for small, local deployments.

RDO (Remote Data Objects) is an object-oriented data access interface to ODBC combined with the easy-to-use style of DAO, providing an interface that exposes virtually all of ODBC�s low-level power and flexibility. RDO is limited, though, in that it doesn't access Jet or ISAM databases very well, and that it can access relational databases only through existing ODBC drivers. However, RDO has proven to be the interface of choice for a large number of SQL Server, Oracle, and other large relational database developers. RDO provides the objects, properties, and methods needed to access the more complex aspects of stored procedures and complex resultsets.

ADO is the successor to DAO/RDO. Functionally ADO 2.0 is most similar to RDO, and there's generally a similar mapping between the two models. ADO "flattens" the object model used by DAO and RDO, meaning that it contains fewer objects and more properties, methods (and arguments), and events. For example, ADO has no equivalents to the rdoEngine and rdoEnvironment objects, which exposed the ODBC driver manager and hEnv interfaces. Nor can you currently create ODBC data sources from ADO, despite the fact that your interface might be through the ODBC OLE DB service provider.

Much of the functionality contained in the DAO and RDO models was consolidated into single objects, making for a much simpler object model. Because of this, however, you might initially find it difficult to find the appropriate ADO object, collection, property, method, or event. Unlike DAO and RDO, although ADO objects are hierarchical, they are also creatable outside the scope of the hierarchy.

It should be noted, however, that ADO currently doesn't support all of DAO's functionality. ADO mostly includes RDO-style functionality to interact with OLE DB data sources, plus remoting and DHTML technology.

In general, it's probably too early in the evolution of ADO to migrate most DAO applications (except possibly ones using ODBCDirect) to ADO right now, since ADO doesn't currently support data definition (DDL), users, groups, and so forth. If you use DAO only for client-server applications and don't rely on the Jet database engine or use DDL, however, then you can probably migrate to ADO now. Eventually, Microsoft will provide an ADO DDL component to aid in DAO-to-ADO migration and provide generic DDL support for OLE DB providers.

 

Finding a workaround...

There are two possible solutions:
  > use DAO instead of ADO to connect to SQL (but this is not a very good long term approach since DAO is
     deprecated);  
  > use linked Access Tables (on the client-side database application) and bind the Forms to those local data
     sources (https://support.microsoft.com/kb/281998 - How to bind Microsoft Access forms to ADO recordsets);

Designing and implementing the fix

We are going to implement the second workaround and for this purpose we will need to introduce these
changes:

[Server side]

   - a temporary Table has to be created dynamically in SQL; It will contain the output data obtained by running
     the Stored Procedure

     Note:  I started this article with the assumption that we were using a simple Access Form bound to a simple
     SQL Stored Procedure data source. But as you can imagine, in real life this scenario is more complex. The
     Stored Procedure takes several input Parameters and it executes a complex JOIN on several SQL Tables
     to return some results. Different users will work with different Parameter combinations, so we cannot store
     the Stored Procedure output values in an unique temp. SQL Table ... instead, each Access client will have
     its own unique temporary Table in SQL and will be responsible for cleaning it up when done;

   - since each Access client will have its own corresponding temporary Table in SQL, clients must have a way to
     send a String that uniquely identifies their data source; This means that the Stored Procedure will receive an
     input Parameter: [strSqlOutputTempTableName];

   - an additional input Parameter added to the SQL Stored Procedure will allow Access clients to select the SQL
     Table that is being queried: [strSqlSourceTableName]; As I wrote in the Note above, in real life scenarios 
      lots of other input Parameters may be added, since clients may want to execute JOINS between several
     Tables, use filter criteria ..etc;

   -  the Stored Procedure will be resaved as 'dbo.TestSP_SQLServer2'; 

USE TestStoredProcedureGO

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

CREATE PROCEDURE TestSP_SQLServer2                 @strSqlOutputTempTableName NVARCHAR(100),                 @strSqlSourceTableName NVARCHAR(100)ASBEGIN DECLARE @SQLQuery NVARCHAR(100); DECLARE @NewLineChar AS CHAR( 2 ) = CHAR( 13 ) + CHAR( 10 )  SET nocount on

 SET @SQLQuery = 'SELECT * INTO ' + CAST(@strTmpOutputTableName As NVARCHAR) + @NewLineChar +                 'FROM ' + CAST(@strSourceTableName As NVARCHAR) + ';'  PRINT @SQLQuery EXEC (@SQLQuery) -- EXEC parameter needs to be enclosed in "( )", else we get this error:   -- "Could not find stored procedure 'CREATE TABLE ..."

END

 

      Before switching to the client side, go ahead and execute the new Stored Procedure in SQL to make sure
      everything is OK ..

exec dbo.TestSP_SQLServer2 @strTmpOutputTableName = 'tmpTbl' ,                                                  @strSourceTableName        = 'tblSourceData' ;

      .. this should be your output:

 

[Client side]

   - we'll start by generating an unique name to be able to identify the temporary Table from SQL (for
     demonstration purposes my code uses the machine name and a time stamp): 

 

 

Dim oShell As Object

Set oShell = CreateObject("WScript.Shell")strSqlOutputTempTableName = "tmp_" & _ Replace(oShell.ExpandEnvironmentStrings("%ComputerName%"), "-", "_") & "_" & _Format(Now, "yyyymmddhhms")

Set oShell = Nothing

   - the temporary SQL Table gets created when we invoke our SQL Stored Procedure using this VBA function:

Module1---------------------------------------------------------------------------------Sub Execute_SQL_StoredProc(strSqlStoredProcName As String,                            strSqlOutputTempTableName As String, strSqlSourceTableName As String)

Dim m_cnnStr As StringDim m_cnn As New ADODB.ConnectionDim m_cmd As New ADODB.CommandDim m_rst As New ADODB.RecordsetDim tmpRowsAffected As Long

'SQL Native Client 2012'---------------------------------------------------------------- m_cnnStr = "Provider=SQLNCLI11.1;" & _           "Data Source=CRISTISQL2;" & _           "Persist Security Info=False;" & _           "Initial Catalog=TestStoredProcedure;" & _           "User ID=sa;" & _           "Password=123;"

'"Integrated Security=false;" https://support.microsoft.com/kb/269495

'When you try to open an ActiveX Data Objects (ADO) connection, you may receive'the following error message:' > Run-time error '-2147217887 (80040e21):'' > Multiple-step OLE DB operation generated errors. Check each OLE DB ' status value, if available. No work was done.'----------------------------------------------------------------

m_cnn.Open m_cnnStrm_cmd.ActiveConnection = m_cnn 

m_cmd.CommandText = strSqlStoredProcName m_cmd.CommandType = adCmdStoredProc

'https://msdn.microsoft.com/en-us/library/windows/desktop/ms675318(v=vs.85).aspx - DataTypeEnum

'https://msdn.microsoft.com/en-us/library/windows/desktop/ms677209(v=vs.85).aspx - CreateParameter Method (ADO)  

m_cmd.Parameters.Append m_cmd.CreateParameter("@strSqlOutputTempTableName", adWChar, adParamInput, 100,                                               strSqlOutputTempTableName)m_cmd.Parameters.Append m_cmd.CreateParameter("@strSqlSourceTableName", adWChar, adParamInput, 100,                                               strSqlSourceTableName)m_cmd.Execute tmpRowsAffected     

'https://support.microsoft.com/kb/195048 - How To Determine Number of Records Affected by an ADO UPDATE

Debug.Print vbNewLineDebug.Print "[Execute_SQL_StoredProc] SQL stored procedure completed."Debug.Print " > SQL StoredProc name: " & strSqlStoredProcNameDebug.Print " > SQL output Temp table name: " & strSqlOutputTempTableNameDebug.Print " > SQL source table name: " & strSqlSourceTableNameDebug.Print " > Rows affected: " & tmpRowsAffected

End Sub

   -  for the next step, we are linking the temporary SQL Table to a local one created inside the Access client;
      In turn the local Table gets programmatically set as data source (ADO RecordSet) for a local Access Form
      using this VBA function:

Module1---------------------------------------------------------------------------------Sub CreateLocal_SQL_TempLinkedTable(strSqlOutputTempTableName As String)

If Len(strSqlOutputTempTableName) = 0 Then    MsgBox "Error![CreateLocal_SQL_TempLinkedTable] function didn't receive" & _ "a valid SQL server table name!"    Exit SubEnd If

Dim dbCurr As Database, dbLink As DatabaseDim tdfLocal As TableDef

'https://support.microsoft.com/kb/892490 How to create a DSN-less connection to SQL Server for linked tables in Access

Dim td As TableDefDim stConnect As StringDim stLocalTableName As String 'Name of the table that you are creating in 'the local Access database

Dim stRemoteTableName As String ' Name of the table that you are linking to on 'the SQL Server database

stLocalTableName = strSqlOutputTempTableName & "_ExternalLink"stRemoteTableName = strSqlOutputTempTableName 

'delete any existing linked SQL tables

For Each td In CurrentDb.TableDefs    Debug.Print " > Checking table [" & td.Name & "] .."

    If td.Name = stLocalTableName Then       Debug.Print " Table found and deleted!"       CurrentDb.TableDefs.Delete stLocalTableName    End IfNext 

'//WARNING: This will save the username and the password with the linked table information.

'SQL Native Client 2012'----------------------------------------------------------------

stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _            "SERVER=CRISTISQL2;" & _            "APP=Microsoft Office 2013;" & _            "DATABASE=TestStoredProcedure;" & _            "User ID=sa;" & _            "Password=123;"

'Microsoft OLEDB Provider for SQL Server'----------------------------------------------------------------'stConnect = "ODBC;DRIVER=SQL Server;" & _' "SERVER=CRISTISQL2;" & _' "DATABASE=TestStoredProcedure;" & _' "UID=sa;" & _' "PWD=123;"

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName,                                   stConnect)

CurrentDb.TableDefs.Append td

Set Form_frmSQL_StoredProc_SourceData.Recordset = CurrentDb.TableDefs(stLocalTableName).OpenRecordset

Set td = Nothing

End Sub

    - all we need now is a way to trigger our code and to cleanup the temporary Tables when we are done; We will
     use the Form_Load and Form_Close events:  

Form_frmSQL_StoredProc_SourceData---------------------------------------------------------------------------------Option Compare Database

Private strSqlOutputTempTableName As String

Private Sub Form_Close()  DoCmd.DeleteObject acTable, strSqlOutputTempTableName & "_ExternalLink"End Sub

Private Sub Form_Load()

Dim oShell As ObjectSet oShell = CreateObject("WScript.Shell")

strSqlOutputTempTableName = "tmp_" & _Replace(oShell.ExpandEnvironmentStrings("%ComputerName%"), "-", "_") & "_" & _Format(Now, "yyyymmddhhms")

Set oShell = Nothing

Execute_SQL_StoredProc "dbo.TestSP_SQLServer2", strSqlOutputTempTableName, "tblSourceData"CreateLocal_SQL_TempLinkedTable strSqlOutputTempTableName

End Sub

 

That's it :-) !

If you have done everything correctly, then once you double-click on your unbound Access Form, it should be
populated with data.

Moreover, if you click on the Navigation Panel and press F5 to refresh it, you will notice that a new linked
Table object has appeared.

 

 

Now switch to the SQL server and see that a Table got created there as well.

Note: my sample code will not clean up temporary Tables from SQL .. you will have to implement this
         functionality;

 

 

'DISCLAIMER:Please note that the code I have offered is just a proof of concept and should not be put into production without a thorough testing!

Microsoft is not responsible if your users will lose data because of this programming solution. It’s your responsibility to test it before deployment in your organization. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that. You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

 

Thank you for reading my article! If you have liked it, please use the rating button.
Bye :-)

 

P.S. Things at work are very busy these days, so I can't always manage to reply to your comments as fast
       as I'd like. If you need help with understanding or getting something in my blog to work, just drop me
       an email at cristib-at-microsoft-dot-com.