Are "joined" recordsets updateable?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin - 25 Feb 2008 18:55 GMT

In one of my VB6 programs, there are several text boxes on a form that
are tied to an ADO Data control with the following SQL statement to
connect the Data control to a table in an SQL Server database:

Adodc_Projects.RecordSource = "SELECT * FROM Projects ORDER BY JobNum"
Adodc_Projects.Refresh

This all worked fine - I was able to view, edit and delete records.

Then I complicated it. I wanted to show some information that's in a
different table (in the same database). So, I used this:

SQL$ = "SELECT Projects.*, Customers.Name as CustName "
SQL$ = SQL$ & "FROM Projects LEFT JOIN Customers "
SQL$ = SQL$ & "ON Customers.CustNum = Projects.CustNum "
SQL$ = SQL$ & "ORDER BY Projects.JobNum"

Adodc_Projects.RecordSource = SQL$
Adodc_Projects.Refresh

This works - in the sense that I can scroll through and view the
different records but, if I try to change the contents of a field,
when I go to save it, I get an error msg that says "the current
recordset does not support updating...".

Does this mean that "joined" recordsets are not updateable or is there
something else going on here that I need to understand?

Thanks for any guidance.

Reply to this Message

DK - 25 Feb 2008 19:28 GMT

> In one of my VB6 programs, there are several text boxes on a form that
> are tied to an ADO Data control with the following SQL statement to
[quoted text clipped - 25 lines]
>
> Thanks for any guidance.

Generally if it's a one to one match on the joined files it is
updateable but if it's a one-to-many then it's not updateable.

Reply to this Message

Martin - 26 Feb 2008 17:38 GMT

>> In one of my VB6 programs, there are several text boxes on a form that
>> are tied to an ADO Data control with the following SQL statement to
[quoted text clipped - 28 lines]
>Generally if it's a one to one match on the joined files it is
>updateable but if it's a one-to-many then it's not updateable.

Thanks, DK. I'm trying to re-design this - I still want to enable the
user to maintain the Projects.CustNum field by viewing/selecting from
the Customer names as stored in another table.

What I'm thinking of now, is to display all available Customer names
in a drop-down list and have the "current" customer (as indicated by
the CustNum) be "selected". IOW, when the user selects a Project, the
Customer Name list would be positioned (using the CustNum) to the
proper customer

Then, if the user changed the customer (by selecting a different one),
the CustNum field would be updated.

But I simply cannot figure out how to do this.

I have the drop-down list (it tied to yet another ADODC which is
connected to the Customers table). But I can't figure out how to
coordinate it with the CustNum in the Projects record.

Can anyone here give me a clue?

Thanks

Reply to this Message

DK - 26 Feb 2008 19:45 GMT

> >> In one of my VB6 programs, there are several text boxes on a form that
> >> are tied to an ADO Data control with the following SQL statement to
[quoted text clipped - 53 lines]
>
> - Show quoted text -

If I understand you correctly you have 2 combo boxes --- one has the
project record tied to it and the other has the customer id tied to
it. And you want the customer combo box to automatically scroll to the
customer record that is found on the project record.

If that's the case then you will to recort to some API call like
SendMessage to the Customer combo box tomake it scroll to the correct
record. Do a search on CB_FINDSTRING and for SendMessage.

Reply to this Message

VB Forum / Database Access / February 2008

Are "joined" recordsets updateable?