Using dbSeeChanges OpenRecordset option

[Powerhouse123]

Hi,

I'm currently updating local Access tables to SQL linked tables. I've updated all the links in the relationships, queries and code.

The database appears to be working perfectly except...

I have a login form to open the database and I get the common "You must use the dpSeeChnages option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column" error message.

I have updated the code on the frmLogin, cmdLogin button but it still throws up the same error...please can you help? I'm going round the bend!

Many thanks as always! PS I apologise if I've picked the wrong forum!

The code for the cmdLogin button is shown below... I've *** the likely cause...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()

******************************************************
Set rst = CurrentDb.OpenRecordset("dbo_tblSecurity", dbOpenDynaset, dbSeeChanges)
*******************************************************

If Not IsNull(Me.txtUserID) And Not IsNull(Me.txtPassword) Then
rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUserID & "'"

If rst.NoMatch Then
MsgBox "You entered the wrong User Name or Password." & Chr(13) & _
"Please enter the correct User Name and Password or " & Chr(13) & _
"contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
ElseIf Me.txtPassword = "password" Then
MsgBox "This is the first time using the database or your password has been reset." & Chr(13) & _
"You must change your password before you can enter the database.", _
vbOKOnly + vbExclamation, "Change Password"
stDocName = "frmUserLoginNew"
stLinkCriteria = "[UserID]=" & "'" & Me![txtUserID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
stDocName = "frmStartup"
stLinkCriteria = "[UserID]=" & "'" & Me![txtUserID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmLogin", acSaveNo
End If
Else
MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
"Please enter the correct User Name and Password or " & Chr(13) & _
"Contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
End If

With User
.AccessID = rst.Fields("AccessID")
' .ViewID = rst.Fields("ViewID")
.Active = rst.Fields("Active")
.Password = rst.Fields("Password")
.SecurityID = rst.Fields("SecurityID")
.UserID = rst.Fields("UserID")
End With

rst.Close

Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click

End Sub

 

[jsteph]

Powerhouse,
What is "USER" in the With/End With block?
Where does the error occur?

 

[Powerhouse123]

Hi jsteph,

User is picking up the Windows login User ID and the error appears to occur on the Set rst line.

Being only a novice at SQL I may not be locating the error correctly. If you can guide me through finding the information you are looking for (ie stepping into VB) then I may be able to provide you with more useful information.

Many thanks for your time...

 

[PHV]

What happens if you use this instead ?
Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_tblSecurity", dbOpenDynaset, dbSeeChanges)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Powerhouse123]

Hi PHV,

Just tried the above and still get the same message!

Thanks for the advice...

 

[Powerhouse123]

In true school boy error fashion I've realised... thanks to jsteph who asked where the error occurs. The coding aboves works fine...I have another startup form which also has an openrecordset in the VBA which requires the dbSeeChanges option to be included.

Everything is working perfectly!

Thanks so much for the thought provoking technique, apologies if I've wasted anyone's time.

Until next time!

Thanks again...