Web Development

Ask something
Discussion Locked

Problem updating database in VB6

0 helpful
4 replies

by jade29

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm having a problem updating an Access database with my VB6 application. I get this
error message when I try to update the table that does not have the primary key included
in the application:

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

The database has two tables, and the primary key on each is called "RouteNumber". If I
specify to display the value of "RouteNumber" in the appropriate textbox from the "Carrier" table, then all of the fields from the "Carrier" table update just fine, but the fields from the "Complaints" table do not, and visa-versa.

I've tried using the RouteNumber for both tables in the application, making one textbox
invisible, but then I getthe error message on any textbox I try to update.

Any ideas on the problem? Thanks for any advice.

4 replies

by mohammad_m_saggaf

Hi,
This is problem reported from Microsoft in its Knoladge Base Article Q246905. You will find it in the following page:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246905

For me I usually use a SQL statement that mintion the primary field only once and link them with it like the folloing Ex.:
(SELECT T.A, T.B, T.C, L.D, L.E ,L.F FROM Table1 T, Table2 L WHERE T.A = L.A)

* Table1 has the fields A, B, and C
* Table2 has the fields A, D, E, and F

Try it.
I hope I Helped.

by jade29

Mohammad, I just found the answer to my problem. If I include all of the fields in both tables with * in my SQL statement then it all updates just fine.

Thanks a lot for taking the time to try and help.

by jade29

Hi Mohammad, thanks for responding. I actually used an SQL statement like yours in the DataLink, and only made reference to one of the RouteNumber fields (except to experiment a little). And I also checked out the Microsoft site that you pointed out, and it seems to be aiming the problem at Oracle databases. I'm not sure how I would apply their solution to my little Access db.

Here is the code that I put in the Validate event of the text box array:
Private Sub Text1_Validate(Index As Integer, Cancel As Boolean)
' Make sure the user wants to make any changes to the field

Dim Warning
Dim Word
Dim rst As New ADODB.Recordset

Set rst = Adodc1.Recordset
Word = rst.Fields.Item(Text1(Index).DataField).OriginalValue If Text1(Index).Text <> Word Then
Warning = MsgBox("Are you sure you want to make changes?", _
vbExclamation + vbYesNo, "Warning!")
End If

' If yes, then update the record
If Warning = vbYes Then
Cancel = False:
rst.Update
Else

' If no, then cancel the change and return the original value
If Warning = vbNo Then
Cancel = True
Text1(Index).Text = Word
End If End If

Cancel = False

frmCarrier2.SetFocus

End Sub

Thanks a lot for any further assistance you can give me.

This discussion is closed to new replies.