Very Computer

Hi there. I am using VB6 & ADO 2.6. Here's the problem...

Run-time error '-2147467259 (80004005)': Insufficient key column information
for updating or refreshing.

Sound familiar? Knowledge Base Article Q246905. I've read it, too, and
almost understood. Anyway, here's my setup (Simalar to VisData readout):

--Table1
    |--Fields
        |--UID
        |--Name
        |--Quality
        |--And so on...
    |--Indexes
        |--Id Number One (UID, Primary)
--Table2
    |--Fields
        |--UID
        |--Tag
        |--And so on...
    |--Indexes
        |--Id Number Two (UID, Primary)

I use this:
SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1
INNER JOIN Table2 ON Table1.UID = Table2.UID
    or
SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1,
Table2 WHERE Table1.UID = Table2.UID

In both cases, when I try to update Table2.Tag I get that error. Now, I've
tried
renaming Table2.UID to Table2.UID2, no effect. I have no idea why it doesn't
work. Additional information is available if you want. Please help.

Thank you in advance.

Bewildered,
    -Luke Abbott

P.S. - Sorry for posting twice, but I feared no one would answer my
'answered' post, of which the solution didn't work...

When you used JOIN you really created a View.  You cannot update on a view.
That being said, there are a couple of work arounds.  You could open a
recordset for each of the base tables, and update them individually. If you
will only be updating one of the two tables, then look at the Unique Table
property.

regards
ed

Quote:> Hi there. I am using VB6 & ADO 2.6. Here's the problem...

> Run-time error '-2147467259 (80004005)': Insufficient key column
information
> for updating or refreshing.

> Sound familiar? Knowledge Base Article Q246905. I've read it, too, and
> almost understood. Anyway, here's my setup (Simalar to VisData readout):

> --Table1
>     |--Fields
>         |--UID
>         |--Name
>         |--Quality
>         |--And so on...
>     |--Indexes
>         |--Id Number One (UID, Primary)
> --Table2
>     |--Fields
>         |--UID
>         |--Tag
>         |--And so on...
>     |--Indexes
>         |--Id Number Two (UID, Primary)

> I use this:
> SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1
> INNER JOIN Table2 ON Table1.UID = Table2.UID
>     or
> SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1,
> Table2 WHERE Table1.UID = Table2.UID

> In both cases, when I try to update Table2.Tag I get that error. Now, I've
> tried
> renaming Table2.UID to Table2.UID2, no effect. I have no idea why it
doesn't
> work. Additional information is available if you want. Please help.

> Thank you in advance.

> Bewildered,
>     -Luke Abbott

> P.S. - Sorry for posting twice, but I feared no one would answer my
> 'answered' post, of which the solution didn't work...

2 things.
1. try putting the field names in []. For example,  Table1.[UID]
    Probably not the problem, but a very good habit to avoid other problems.
2. Include in your query the key field(s) from the tables that you want to
update. That's
cause of the 'Insufficient key column information' error.

--
Eric Zieg

remove nospam from above
************************************************************
*  It's not MY fault, it's somebody elses.                 *
*  Yeah, right ! Personal responsibility, what a concept ! *
************************************************************

Try using a command  object with a SQL statement.  Pull the data fields that
you want to update, including the key, out of the recordset and put them in
variables.  Then using the SQL command, update, update the table.   Loop
through the recordset finding the fields that have been altered, if there is
only one record then you won't have to do this, and build your update
statement.

sSQL = "Update tablename set fieldname = variable , fieldname2 = variable
where key = keyvariable"
command. execute

Please note than this example is not syntactically correct.  Depending upon
datatypes you will need to concatenate the variables onto the SQL statement
with or without quotes as well as provide your connection object and SQL
statement when executing the command object.  If you need the correct syntax
please fell free to email me for a code snippet.

Quote:> Hi there. I am using VB6 & ADO 2.6. Here's the problem...

> Run-time error '-2147467259 (80004005)': Insufficient key column
information
> for updating or refreshing.

> Sound familiar? Knowledge Base Article Q246905. I've read it, too, and
> almost understood. Anyway, here's my setup (Simalar to VisData readout):

> --Table1
>     |--Fields
>         |--UID
>         |--Name
>         |--Quality
>         |--And so on...
>     |--Indexes
>         |--Id Number One (UID, Primary)
> --Table2
>     |--Fields
>         |--UID
>         |--Tag
>         |--And so on...
>     |--Indexes
>         |--Id Number Two (UID, Primary)

> I use this:
> SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1
> INNER JOIN Table2 ON Table1.UID = Table2.UID
>     or
> SELECT Table1.UID, Table2.Tag, Table1.Name, Table1.Quality FROM Table1,
> Table2 WHERE Table1.UID = Table2.UID

> In both cases, when I try to update Table2.Tag I get that error. Now, I've
> tried
> renaming Table2.UID to Table2.UID2, no effect. I have no idea why it
doesn't
> work. Additional information is available if you want. Please help.

> Thank you in advance.

> Bewildered,
>     -Luke Abbott

> P.S. - Sorry for posting twice, but I feared no one would answer my
> 'answered' post, of which the solution didn't work...

Thanks, works fine now!