ADO with Access DB - Urgent

[AdaHacker]

I'm not certain what broke it, but I have some code for updating a database which no longer works. It's important that I get this working today, so any help would be greately appreciated. I'm using an Access 97 database with and ADO data control from VB 6. The SQL I use to populate the data control contains an inner join, in case it's important. I also played around with adding relationships and cascading updates to it a while ago, but decided against it and removed them. (May not be important, but you never know.)

Everything goes fine up until I do an UpdateBatch on the data control's recordset. At that point, I get the following unhelpful error message:

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

A quick search of MSDN turned up only one match on this particular message. However it had to do with joining tables where the primary key of one table matches a column in the other. However, my column names are all unique across tables, so that doesn't apply.
In case it helps, here's the code I use to populate the data control:

    SQL = "SELECT ak_site, kr_veh_dept, kr_tran_num, kr_veh_no, ak_employee_no, ak_empl_dept, empl_first_name, " & _
            "empl_last_name, kr_date, kr_time, kr_pr, kr_qty, kr_odom FROM tranfile INNER JOIN emplfile " & _
            "ON tranfile.ak_employee_no = emplfile.empl_number"

'...Irrelevant stuff omitted

    With TransDC
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .CursorType = adOpenDynamic
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False;Data Source=" & db_name & ";"
        .RecordSource = SQL
        .Refresh
    End With

Has anybody ever seen this error before, or have any idea what could be causing it? I suspect it's something with the database itself, as I haven't done much to the code, but I have no idea what the problem is. Can someone please give me a hand?

 

[LW]

If your code has been working Ok, I would look at the database.
Look for things that might have changed in the structure or at the data it's self. (Did someone enter something your program expect.) I would look at the key fields and the latest data in those fields.

Hope this helps.

 

[AdaHacker]

Thanks a lot for the response. I have the problem half fixed now. I figured it out just as you posted.

Turns out it wasn't in the database. Half the reason I was so confused by the error message was that I didn't define keys in my database. I also don't remember changing the structure since the last time it was used and any changes my user has made simply couldn't cause this.

I use the same form, and thus the same code, for both adding records and updating them. Turns out I had forgotten about that and was attempting to update certain fields before I did my Recordset.AddNew. Since I'm using BatchUpdate, I would have thought that this would not be a problem, that it would just update the wrong record as well. However, when I changed the code to assign the values I calculated to variables and then do the AddNew and update all the fields in one place, it suddenly works.

But it only works when I'm adding records, not when I try to modify existing ones. If I do that, I still get the same error message. This makes no sense to me, as I'm using the exact same code for both. I have a flag which tells me if I'm adding a new record or not, but that only guards the .AddNew statement and doesn't appear anywhere else in the subroutine. Any ideas?

 

[LW]

Is there a reason you are using an updatebatch rather than update as you don't indicate you are using disconnected recordsets?

If you are not using disconnected recordsets I would try Update. Also the MDAC 2.5 SDK says:

"You should use batch updating with either a keyset or static cursor only." I noticed you were using a dynamic recordset.

ALSO:
"If the attempt to transmit changes fails for any or all records because of a conflict with the underlying data (for example, a record has already been deleted by another user), the provider returns warnings to the Errors collection and a run-time error occurs. Use the Filter property (adFilterAffectedRecords) and the Status property to locate records with conflicts."

Good Luck!

 

[AdaHacker]

Thanks for replying LW.

To answer your question, I can't exactly remember why I used UpdateBatch. I beleive my thinking was that I have a datagrid bound to the data control, and the user might want to edit several records from there before updating. I ended up not going that dircetion. At any rate, I tried changing cursor types and using Update, and it didn't help. I still got the same error message.

However, I did find that if I changed the SQL to eliminate the JOIN, then it seems to work. I was only using the JOIN to get a first and last name, neither of which I was changing, so it's not critical data. It was just easier and more intuitive to have it all in the same recordset.

Of course, I still haven't figured out why what I had isn't working, or what that stupid error message is supposed to mean. If anyone can figure it out, I'd love to know.

 

[AdaHacker]

In case anyone was wondering, I've done a small rewrite and worked around the JOIN in the SQL. The program is working correctly now. I'd still like to know why it wasn't working before, though.

 

[Zeroanarchy]

adaHacker, do not know if this is going to help, but came across a similar prob with an acccess database on update, the reason I was getting the error was that I was trying to update a table which had a autonumber as the first reference, and it was not to happy about getting flooded with records.
As i said do not know if this helps and do not know how you SQL is setup but thought i would mention it.

Zero Anarchy

 

[AdaHacker]

Zeroanarchy,
Thanks for the suggestion. Unfortunately, it doesn't seem to apply to me, as I'm not using any autonumber fields. My SQL was just a select with a simple inner join, and the only thing I did to it was add one field to the select. I can't think of any reason why that would cause a problem, but as I said, once I drop the join, it works. It's all very confusing.

 

[Jaco]

I think that maybe a reason, see the link below:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246905

Regards,

Jaco