Table Locked

[tegb]

I have a linked table (ODBC) that was working perfectly ok but after nes field were added to the table, it locked up. I can add records but not delete nor edit, how can I unlock this table to allow edits?

 

[Pat Hartman]

Access doesn't automatically see changes to linked tables unless they are Jet/ACE. It caches the definiton specifically so it doesn't have to retrieve that information every time the table is used.

Open the linked tables manager and refresh the links.

 

[tegb]

I have refreshed link, I can see the changes to the fields but the records are locked. when I try to edit, it tells me someone else is making change to the same data but I am the only one that have access to this table

 

[Pat Hartman]

Access can only update linked tables if they have a primary key or unique index. Was the PK removed?

Do you have code in the form that is updating the recordset behind the scenes?

 

[tegb]

No, the PK ws not removed, I added more fields to the table. is there a limit to the no of memo field that can be on a table? below is the code that updates the recordset


Private Sub CmdSaveNew_Click()
On Error GoTo Tina
Tina:
If IsNull(ctlName) Then
MsgBox "Please enter your name"
ctlName.SetFocus
ElseIf IsNull(XNAC) Then
MsgBox " Please enter XNAC"
XNAC.SetFocus
ElseIf IsNull(txtDate) Then
MsgBox " Please enter Proposed Close Date"
ElseIf IsNull(txtDateRequested) Then
MsgBox " Please enter Accept Date"
Else
Dim db As Database
Set db = CurrentDb
Dim tblpool As Recordset
Dim Sixtyplus As Integer
Dim rstemp As Recordset
Dim rstemp2 As Recordset
Dim rstemp1 As Recordset
Dim rstemp3 As Recordset
Dim ctl As Control
Set tblpool = db.OpenRecordset("Report_History_Table")

tblpool.AddNew
tblpool![DateModified] = Date
tblpool![Status] = cmbStatus.Value
tblpool![AccountName] = ctlName.Value
tblpool![AssignedTo] = txtAssignedTo.Value
tblpool![AcceptDate] = txtDateRequested.Value
tblpool![ProposedclsDate] = txtDate.Value
tblpool![Escalatedby] = txtElevatedby.Value
tblpool![CustomerContactName] = CustomerContactName.Value
tblpool![CustomerPh] = CustomerPh.Value
tblpool![CustomerEmail] = CustomerEmail.Value
tblpool![Issuecate1] = Issuecate1.Value
tblpool![Issue1Resolved] = chkIssue1Resolved.Value
tblpool![Issue2Resolved] = chkIssue2Resolved.Value
tblpool![Subprocess4] = Subprocess4.Value
tblpool![Prevention1] = Prevention1.Value
If Len(XNAC.Value & "") > 0 Then
tblpool![XNAC] = XNAC.Value

Else
MsgBox " Please enter XNAC"
XNAC.SetFocus
End If
tblpool![Priority] = XNAC2.Value
'New Code
If Len(XNAC2.Value & "") > 0 Then

Set rstemp = db.OpenRecordset("Select sum(invamt) AS [Sixtyplus] from Debits where (XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' or XNAC Like '" & [Forms]![Request lookup and update]![XNAC2] & "*') and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>1")
With rstemp
.MoveFirst
tblpool![SixtyPlus_Acceptdt] = !Sixtyplus

End With
Else
Set rstemp = db.OpenRecordset("Select sum(invamt) AS [Sixtyplus] from Debits where (XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*') and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>1")
With rstemp
.MoveFirst
tblpool![SixtyPlus_Acceptdt] = !Sixtyplus

End With


End If
tblpool.Update
Me!ctlName.Requery
MsgBox " Saved"
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = ""
ElseIf ctl.ControlType = acComboBox Then
ctl.Value = ""
ElseIf ctl.ControlType = acCheckBox Then
ctl.Value = False
ctl.Enabled = True
End If
Next ctl
Set ctl = Nothing
Exit Sub
End If
DoCmd.RunCommand acCmdRefresh



End Sub

 

[gemma-the-husky]

is it erroring when you try to add records?

your error handler won't work correctly. it "goes to" tina - but never resumes, so it won't reset, and will almost certainly not work correctly in event of an error

i expect one of your fields is mistyped, or some other error causing the .update statement to fail

eg - is this one correct?
tblpool![Escalatedby] = txtElevatedby.Value

put a breakpoint in, and step through to trace programme execution.


----
it looks like this was always an issue, but was never triggered until now

 

[tegb]

the update works well, the code adds records to the table very well. the problem occurs after the records have been added. I can neither delete the record not make changes. once a record is added, the table locks up. i do not want the table to lock, i want to be able to make changes or delete records when I want