visual basic 2010 error with access database update query

Question

I have this code that should update my access database, but I keep getting an error saying:

It is not possible so change or delete the regists because the table 'MembrosCompasso' has relating records.

This is my code:

Dim query1 As String = "UPDATE MembrosCompasso SET MembrosCompasso.BI=@compass_bi WHERE MembrosCompasso.BI=Membros.BI" ' Membros.Pai=@pai, Membros.Mae=mae WHERE BI=@BI"
Dim query As String = "UPDATE Membros  SET Membros.BI=@num_bi, Membros.Nome=@nome, Membros.Hablitacoes=@hablitacoes, Membros.Contribuinte=@contribuinte, Membros.Telemov=@telemov, Membros.DataNasc=@natnasc, Membros.LocalNasc=@localnasc, Membros.DataBaptismo=@databapt, Membros.LocalBapt=@localbapt, Membros.Sexo=@sexo, Membros.Naturalidade=@natu, Membros.Profissao=@prof, Membros.Estadocivil=@estadciv, Membros.DataCasamento=@datacas, Membros.LocalCasamento=@localcas, Membros.Notas=@notas, Membros.Email=@email WHERE Membros.BI=@BI" 

con.Close()
con.Open()
Dim command As New OleDbCommand(query, con)
Dim command1 As New OleDbCommand(query1, con)
' Indicação dos parâmetros que serão actualizados

command1.Parameters.Add("@compass_bi", OleDbType.VarChar).Value = BI.Text
command.Parameters.Add("@num_bi", OleDbType.VarChar).Value = BI.Text
command.Parameters.Add("@nome", OleDbType.VarChar).Value = Nome.Text
command.Parameters.Add("@hablitacoes", OleDbType.VarChar).Value = hablitacoes.Text
command.Parameters.Add("@contribuinte", OleDbType.VarChar).Value = ncront.Text
command.Parameters.Add("@telemov", OleDbType.VarChar).Value = telmov.Text
command.Parameters.Add("@natnasc", OleDbType.VarChar).Value = datnasc.Text
command.Parameters.Add("@localnasc", OleDbType.VarChar).Value = NascLocal.Text
command.Parameters.Add("@databapt", OleDbType.VarChar).Value = DataBapt.Text
command.Parameters.Add("@localbapt", OleDbType.VarChar).Value = LocalBapt.Text
command.Parameters.Add("@sexo", OleDbType.VarChar).Value = sexo.Text
command.Parameters.Add("@natu", OleDbType.VarChar).Value = natu.Text
command.Parameters.Add("@prof", OleDbType.VarChar).Value = profi.Text
command.Parameters.Add("@estadciv", OleDbType.VarChar).Value = estciv.Text
command.Parameters.Add("@datacas", OleDbType.VarChar).Value = datcas.Text
command.Parameters.Add("@localcas", OleDbType.VarChar).Value = localcas.Text
command.Parameters.Add("@notas", OleDbType.VarChar).Value = notas.Text
command.Parameters.Add("@email", OleDbType.VarChar).Value = email.Text
command.Parameters.Add("@BI", OleDbType.VarChar).Value = numero_BI

There is a screenshot of my access db schema here.

Renaud Bompuis · Accepted Answer · 2012-05-01 07:53:59Z

1

It looks like you are trying to update the table MembrosCompasso before you make the change in table Membros.
Because you set MembrosCompasso.BI to be related to Membros.BI, it must exists in that table first.

If you set your relationship in Access to be automatically updated, you will not have to perform any manual update to MembrosCompasso, they will be updated automatically when you update the source field.

For instance, say I have a PO table that has many POItem.
I could set the one-to-many relationship between them as below:

enter image description here

In that case, if I update PO.ID, all POItem.POID fields will be updated.
Same if I delete a given PO record, all related POItem records will also be deleted.

Have a look at the MS Access documentation about table relationships.

answered May 1, 2012 at 7:53

Renaud Bompuis

16.8k4 gold badges59 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

visual basic 2010 error with access database update query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related