[AccessD] Fwd: Combo bound to ADODB not showing data

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Wed Dec 2 15:18:18 CST 2020


Hi,

A contact I respect has suggested I join this list and post my current
problem here - so here it goes:

I have an MS Access form that I bind to an ADODB recordset retrieved
through a call to a SQL Server user stored procedure (USP).
One of the recordsets returned by the USP is bound to a combo box and this
has worked flawlessly until now.

After adding a timestamp (a.k.a. rowversion) column to the primary
underlying table (a practice I understand to be highly recommended), the
combo box, when opened after executing the VBA code, never shows the record
it has retrieved from SQL Server.  The combo box shows no record and does
not even show the column headers that are normally shown.  Removing the
timestamp column results in the combo correctly showing data after the
combo is bound again by the VBA code and subsequently opened.

The presence or absence of the timestamp column makes no difference to the
recordset that is retrieved as far as I can discern (same columns, same
data, same data type). This is consistent when the USP is called by SSMS, a
pass-thru query or the VBA code itself (verified using debug.print
statements).

Using the F9 or refresh (as distinct from 'refresh all') function in the
Access interface results in the combo correctly displaying the data when
opened.
Alternatively, after the normal code to bind the combo, which is:
  Set rstTemp = rstTemp.NextRecordset
  Set Me.MyControl_cbo.Recordset = rstTemp
If I add the following line:
  Me. MyControl_cbo.Requery
Then the problem does not occur.  However, I have found no reference that
has ever suggested the requery statement should ever be used in this
situation.

I have observed this using Access 2010 32 bit on both WIn7 and Win10 with
the back end as both SQL Server 2008R2 and 2014 (3 different servers).
In returning the relevant recordset, the USP references the 'primary' table
and others, including a view that references the same 'primary' table twice
in a CTE.

I would expect to get consistent results when following the recommended
practices - and I understand I'm doing that.  Where have I gone wrong
please???

Thanks in advance for your responses,
Paul Wolstenholme


More information about the AccessD mailing list