[RESOLVED] ADODC Control : How to alter RECORDSOURCE at Runtime?

I have an ADODC2 control with its properties established to link it to a MS Access database. Datagrid2 displays to the user the content of the specified Table of that database. All this works fine.

My wish now is that the user may type into a Text box (Text83) another Table number in the selfsame database (eg. "Table3" instead of "Table2"). I therefore need the program to be able to alter the ADODC's RecordSource property to what the user has entered in that Text Box.

Try as I may, I can not get it to work. I get errors 3704 and 3705 coming up, then when I write code which does not give rise to errors the RecordSource of the ADODC seems to update OK, but the associated Datagrid when displayed is blank. It is as if I need to re-draw that Datagrid, but how?

My current code is :-



If Adodc2.Recordset.State = adStateOpen Then Adodc2.Recordset.Close

Dim SOURCETABLE As String

SOURCETABLE = Text83

Adodc2.Recordset.Source = Text83 'Text83 starts as "Table2" but the user may specify another file path therein eg. "Table2 dd-mm-yy"

Adodc2.Recordset.Open



I bet there is a very simple answer to this, but none of my books, nor MSDN, seem to help. Suggestions would be much appreciated.


camoore

Wales, UK

Change the ADODC properties, not the properties of it's underlying Recordset.

Adodc1.RecordSource = "Orders"
Adodc1.Refresh

Try Adodc2.Refresh before Adodc2.Recordset.Open

Hi Camoore

An interesting read by Si_the_Geek

http://www.vbforums.com/showthread.php?t=416218

Thank you for your suggestions.

The brucevde approach was that which I tried first, but I was not aware of the need for a subsequent Adodc2.Refresh statement. Have now added it.

What I then found was that VB6 rejected MSAccess table names which MSAccess was happy with. For example if I re-named a Table 2 as "Table2 12-08-09" MSAccess accepted it, but when I tried to use this table name as a new RecordSource property for Adodc2 in VB6 I got all sorts of errors.

So I altered the format of my re-named MSAccess tables to, in this case, "Table2120809". By eliminating the space and - characters, it has started to work.

Problems still arise if a user tries to enter a Table number which does not exist. As I write I am trying to sort out a suitable error trapping routine.

The following code snippet is what I have got more or less working OK now :


'Now to select the version of Table 2 which the user wants to replay. This is entered
'at Text 83 on Picture 6. Text83 starts as "Table 2" but the user may alter this
'by typing into Text 83 - eg. Table2120809

On Error GoTo Line10

Adodc2.RecordSource = Text83

On Error GoTo Line10

Adodc2.Refresh

GoTo Line11

Line10:

MsgBox "User Selected Data Table not found. Check entry and re-try"

GoTo Line200 'exit the routine


Line11: 'program continues.................

Thanks also to koolsid for the pointer towards si-the-geek's article on database access. Si himself has mentioned this to me recently, and I am reading up the topic. However this query relates to an old program I wrote which I want to update. To deviate now from the Adodc / Datagrid / MSAccess database approach would involve too much re-writing.

For my limited purposes, a text file is a perfectly suitable database and even easier to handle / program then the aforementioned. So now I use text files, thanks once again to this Forum and the help of Garrcomm in Holland in a recent previous thtread.

I will await any further comments, then mark this thread as resolved. Thanks folk.

camoore

Wales, UK