Re: Unexplained error updating or deleting record using ADO and VB6


"Victor Peters" <Victor Peters@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0584DE73-D02C-4362-A708-681A1EB77606@xxxxxxxxxxxxxxxx
> Situation:
> A 'flat' Access 97 database with just one table. All fields in the table
> are
> of type text and no (primairy) key column is defined.

Therein lies your problem: each row must be uniquely identifiable, in order
to be updateable. DAO is proprietary to Jet, it must use some internal
'uniqueifier' to allow this. ADO is more generic. No business-class dbms
I've ever worked with allows updates without a PK. (No, Jet does not
qualify as a business-class dbms, imho.)


> For accessing data in
> this table a recordset is filled using a simple SELECT * statement.
>
> Using ADO I can add a record to the recordset that contains the exact same
> data for all fields. But as soon as I try to update or delete one of the
> duplicate rows (after having set the record pointer to the desired record)
> I
> get an error:
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> Clicking on OK results in the update or deletion of all (in this case
> both)
> duplicate rows.
>
> In previous versions of the ActiveX DLL I'm programming, using DAO, it was
> quite possible to add duplicate rows to a recordset and manipulate these
> duplicate rows withou a problem or error.
> ADO seems to treat these duplicate rows differently.
>
> Does ADO have additional properties to enable the manipulation of
> duplicate
> records?
>
> Is there a way around this 'limitation'?

The way around is to add a primary key, just add an autoincrement column,
make it the PK, and forget about it... it's a no-brainer, really. The only
problem I can think of is if you want to insert using SELECT * [mytable] as
the source, into another existing table that has the same autoincrement
column. Jet does not allow for this, however, SQL Server does, and given
that SQL 2005 Lite is free, and now even comes with passable management
tools.

In any case, a PK will be a necessity in this case; and it's a good schema
design practice as well.


-Mark


> It's not that I conciously want users to be able to add duplicate data to
> the database, but the functionality of the new ActiveX DLL (using ADO)
> must
> be exactly the same as it was before (using DAO).
>
> Please e-mail me at: vt.peters@xxxxxxxxxxxxxxxxxx
>
> Thanks in advance!
>


.

Relevant Pages