I have an MS Access 2010 (.mdb) application linked to a MS SQL Server 2008 R2 SP2 database on the backend. I am using an SQL Server ODBC connection driver. I am not using a SQL Server Native client driver.
When I run my application at some point I receive the following error:
You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column
The error happens when I double click on a item (record) in the list of the screen (on Form: frmABCList) in order to get the detail information for that record. Instead of receiving the detailed information filled out on the Form: frmABCDetail, I get the error message shown above, and the Form: frmABCDetail appears, but it is blank.
How can I correct this error and keep it from appearing?
Here is the code that I use for the detail section on Form frmABCList:
Private Sub Detail_Click()
On Error GoTo Error_Detail_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = [doc_index]
stDocName = "frmABCDetail"
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryDocumentList", dbOpenDynaset, dbSeeChanges)
With rs
.MoveFirst
.FindFirst ("[doc_index]= '" & Me![doc_index] & "'")
If .NoMatch Then
MsgBox "NO RECORDS MET REQUESTED CRITERIA.", "ADIMS"
Else
stLinkCriteria = Me.[doc_index]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Modify"
End If
rs.Close
db.Close
End With
'Check for no records
'If (Me.RecordsetClone.RecordCount <> 0) Then
'DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Modify"
'Else
' MsgBox "NO RECORDS MET REQUESTED CRITERIA.", "ABC App "
'End If
Exit_Detail_Click:
Exit Sub
Error_Detail_Click:
MsgBox Err.Description
Resume Exit_Detail_Click
End Sub
As you can see, I am using dbSeeChanges in my code, but I still get the error. Also, could the problem be caused by the .ldb file associated with the MS Access application?
Or could the problem be with the tblDocument table, which does use and has to use an IDENTITY column called 'doc_index'.
Any help would be greatly appreciated.
Thanks,
Cheryl73
When I run my application at some point I receive the following error:
You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column
The error happens when I double click on a item (record) in the list of the screen (on Form: frmABCList) in order to get the detail information for that record. Instead of receiving the detailed information filled out on the Form: frmABCDetail, I get the error message shown above, and the Form: frmABCDetail appears, but it is blank.
How can I correct this error and keep it from appearing?
Here is the code that I use for the detail section on Form frmABCList:
Private Sub Detail_Click()
On Error GoTo Error_Detail_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = [doc_index]
stDocName = "frmABCDetail"
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryDocumentList", dbOpenDynaset, dbSeeChanges)
With rs
.MoveFirst
.FindFirst ("[doc_index]= '" & Me![doc_index] & "'")
If .NoMatch Then
MsgBox "NO RECORDS MET REQUESTED CRITERIA.", "ADIMS"
Else
stLinkCriteria = Me.[doc_index]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Modify"
End If
rs.Close
db.Close
End With
'Check for no records
'If (Me.RecordsetClone.RecordCount <> 0) Then
'DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Modify"
'Else
' MsgBox "NO RECORDS MET REQUESTED CRITERIA.", "ABC App "
'End If
Exit_Detail_Click:
Exit Sub
Error_Detail_Click:
MsgBox Err.Description
Resume Exit_Detail_Click
End Sub
As you can see, I am using dbSeeChanges in my code, but I still get the error. Also, could the problem be caused by the .ldb file associated with the MS Access application?
Or could the problem be with the tblDocument table, which does use and has to use an IDENTITY column called 'doc_index'.
Any help would be greatly appreciated.
Thanks,
Cheryl73
