Database - Why doesn't my Data Control reload data when I set the RecordSource?

It is natural to assume that the data for a Data Control will re-load and populate your controls when you change the RecordSource, however this is not the case.

In addition to setting the RecordSource you also need to tell the Data Control to reload the data, and you may need to tell your controls to update themselves to show the new data too.

This is how you can tell the Data Control to reload data like this:

VB Code:

'(change ADODC1 to the name of your data control)
ADODC1.RecordSource = "..(your recordsource text here).."
ADODC1.Recordset.Requery
ADODC1.Refresh

If you are showing the data in controls (textbox/grid/etc) that do not update after using the above code, you will need to refresh them too, like this:

VB Code:

ControlName.Refresh

One important thing to remember when using the ADO Data Control, if at design-time you set its CommandType to 2 (adCmdTable) you cannot change its Recordsource at run-time using "SELECT * FROM TableName", it will generate an error, a workaround for this is to set its CommandType to 1 (adCmdText) at design-time.

And to refresh a DataGrid which is normally associated with the ADO Data Control try using its Rebind method.

A similar issue.. if you are using a DataGrid connected to a DataEnvironment then a Refresh or Rebind does not seem to update the grid.

The solution in this case is to re-set the datasource, eg:

VB Code:

Set DataGrid1.DataSource = DataEnvironment1