Suppose we have this scenario where a local Access database uses Forms bound to SQL server
- 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
- in turn, those RecordSet objects get populated with data when Access is invoking a Stored Procedure on the
- 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
"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
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.
Dim o_rs As New ADODB.Recordset
'http://msdn.microsoft.com/en-us/library/ms130822.aspx (Using Connection
connectionStr = "Provider=SQLNCLI11.1;" & _
Set connection = New ADODB.connection
Set cmd = New ADODB.Command
Set o_rs = New ADODB.Recordset
o_rs.Open "TestSP_SQLServer", connection, adOpenKeyset, adLockOptimistic
DoCmd.OpenForm "frmSQL_StoredProc_SourceData", acFormDS, , , acFormEdit, acWindowNormal
Set [Form_frmSQL_StoredProc_SourceData].Recordset = o_rs
Dim strItems As String
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: http://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.
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
> use linked Access Tables (on the client-side database application) and bind the Forms to those local data
sources (http://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
- 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';
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE TestSP_SQLServer2
SET @SQLQuery = 'SELECT * INTO ' +
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',
.. this should be your output:
- 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_" & _
Set oShell = Nothing
- the temporary SQL Table gets created when we invoke our SQL Stored Procedure using this VBA function:
Dim m_cnnStr As String
'SQL Native Client 2012
'"Integrated Security=false;" http://support.microsoft.com/kb/269495
'When you try to open an ActiveX Data Objects (ADO) connection, you may receive
m_cmd.CommandText = strSqlStoredProcName
'http://support.microsoft.com/kb/195048 - How To Determine Number of Records Affected by an ADO UPDATE
- 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:
If Len(strSqlOutputTempTableName) = 0 Then
Dim dbCurr As Database, dbLink As Database
'http://support.microsoft.com/kb/892490 How to create a DSN-less connection to SQL Server for linked tables in Access
Dim td As TableDef
Dim stRemoteTableName As String 'Name of the table that you are linking to on
stLocalTableName = strSqlOutputTempTableName & "_ExternalLink"
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
'//WARNING: This will save the username and the password with the linked table
'SQL Native Client 2012
stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
'Microsoft OLEDB Provider for SQL Server
Set td = CurrentDb.CreateTableDef(stLocalTableName,
Set Form_frmSQL_StoredProc_SourceData.Recordset =
Set td = Nothing
- 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:
Private strSqlOutputTempTableName As String
Private Sub Form_Close()
Private Sub Form_Load()
Dim oShell As Object
strSqlOutputTempTableName = "tmp_" & _
Set oShell = Nothing
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
Please note that the code I have offered is just a proof of concept and should not be put into
Microsoft is not responsible if your users will lose data because of this programming solution.
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:
Thank you for reading my article! If you have liked it, please use the rating button.
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.