Summary
In a Microsoft Access 2010 database (accdb), I have a form that dynamically loads other forms into a subform object on the main form. The forms used in the subform object are bound to ODBC pass-through queries that execute stored procedures to return recordsets. I can't figure out why I can use one sproc and it works perfectly fine, but if I bind the form to another sproc, it fails to load the subform.
Technical Walkthrough
I have two pass-through ODBC queries. qryGood
and qryBad
. They use identical ODBC connection strings (ODBC;DRIVER=SQL Server;SERVER=MyServer;UID=MyUser;Trusted_Connection=Yes;DATABASE=MyDatabase
), and the SQL behind them is identical, but pointing to two different SQL stored procedures on the SQL 2012 database server.
qryGood source: exec spGoodProc 123456
qryBad source: exec spBadProc 123456
The SQL behind the sproc is very simple. Return records from a single table, filtering by the ID passed as a parameter. (Some will do more complex things, but I am just focusing on a simplified example here that demonstrates the problem.)
The RecordSource
property of frmMySubform
is set to qryBad
.
The subform SourceObject
is set via VBA code: sfrmMain.SourceObject = "frmMySubform"
No errors are thrown at this point. While the SourceObject
property now returns frmMySubform
, the .Form
object does not seem to be set.
I then try to reference a property on the subform: Debug.Print sfrmMain.Form.Name
This fails with error 2467: The expression you entered refers to an object that is closed or doesn't exist.
I can then open frmMySubform
in design view, change the RecordSource
property to qryGood
and it works just fine. This seems to point to a problem with spBadProc
that only manifests itself when used as the RecordSource
on a subform.
What I Have Tried
In an effort to troubleshoot this problem, I have used the process of elimination to narrow this down as far as I can, but I am still not understanding why the one sproc works and the other doesn't. Both return records just fine in SQL and when running the pass-through query directly. Both work fine when opening the form directly. It only becomes a problem when the form is set as a SourceObject
in a subform control.
I have used sp_procedure_params_rowset
to compare the parameters in the sprocs, and they are identical. I have compared the data types of the columns in SQL and there is nothing new or different in tblBad
that isn't in tblGood
. I have also tried profiling the SQL server while setting the form, and it seems to call the sproc just fine. I didn't see any clues when comparing the trace between the bad and the good calls.
Setting the RecordSet
directly to an ODBC link to tblBad
works just fine (and I presume a view would be fine as well) but having the simple stored procedure wrapper somehow triggers the error.
I have also compared the security, properties and extended properties for spGoodProc
and spBadProc
and they are identical.
My Question
What can I do on the troubleshooting side to reduce this down further? Has anyone out there encountered similar issues with bound sprocs on subforms? I am working on a very complex database with hundreds of forms, tables and queries, so I would really like to understand why this is occurring before I go too far down this path.
Thanks in advance for any insight you are able to share on this perplexing problem. :-)
Found it!
After tracing it back to something with the specific table, I removed all constraints, keys, and then columns from a copy of the table, systematically testing to see if I could pinpoint the problem. Sure enough, it was a specific column name in the stored procedure!
Simply aliasing this column to a different name solved the problem. (See below for expanded details)
Update after Further Testing
After additional testing to further pinpoint the issue, I think I now understand why this was occurring. When you link an ODBC table and specify a unique (key) column, Access will automatically attempt to set the
LinkMasterFields
andLinkChildFields
to the key column name when a subform is loaded and the subform has a column with the same name. While this works fine with linked tables or views, it does not work when the RecordSource of the subform is set to a stored procedure.If you attempt to do this by manually adding the subform, you will see the following notification:
However, if you set the subform target through VBA code, you don't get any warning or error message. It simply doesn't (fully) load the subform. @Albert D. Kallal, you were right on about this being related to a master/child fields issue!
I was able to consistently reproduce the issue in a test database file in both Access 2010 and Access 2016. If you would like to see this for yourself, you can use the following steps to reproduce it:
PrimaryID
column.tblBugTest
.PrimaryID
as the unique column.qryPassThrough
using ODBC to the same database, and set the SQL toexec spBugExample
.frmMain
,frmSubForm
, andfrmBlank
.frmBlank
as a subform tofrmMain
. Name the subformsfrmSubform
.RecordSource
offrmMain
to the linked table.frmMain
to switch the subform fromfrmBlank
tofrmSubForm
.frmSubForm
toqryPassThrough
.frmSubForm
.frmSubForm
by itself. It should load a record fromtblBugTest
.frmMain
and click the button. It should throw an error.If you step through the code, you will see that before setting the
SourceObject
, theLinkMasterFields
property is blank. After settingSourceObject
, you can hover overLinkMasterFields
and see that it is now set to thePrimaryID
column.Workarounds
Changing any of the following will work around the error by avoiding the problematic auto-linking of the master/child fields.