Run-Time error 3622 - when trying to run a form from Access form linking to SQL server 2005

Question

question

Hanson74 asked Apr 21, '10

Run-Time error 3622 - when trying to run a form from Access form linking to SQL server 2005

Please can anyone help me on this. I have just migrated my access database to SQL server 2005 using SSMA When i am trying to use the one of the forms in Access i am having this ERROR - 'Run-time error 3622' - You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL server table that has an IDENTITY column.

this is my code:

Public Sub Command23_Click()

If Me.Combo51.Value = "" Or IsNull(Me.Combo51.Value) = True Then
    MsgBox "Please select the Nurse from the list for whom you want to enter clinical data", vbOKOnly + vbExclamation
    Exit Sub
End If

If Me.backOk = False Then
    Exit Sub
End If

Dim rs As DAO.Recordset
DoCmd.GoToRecord , , acNewRec

DoCmd.OpenForm "frmSearchPatient", acNormal, , , acFormEdit, acDialog
Me.THPCTID.Value = Me.lblthpct.Caption

Set rs = CurrentDb.OpenRecordset("SELECT [First name], Surname, [NHS number] FROM tbl_Patients WHERE tbl_Patients.[THPCT ID]=" & Me.lblthpct.Caption)
Me.PatientFirstName.Value = Nz(rs(0), "")
Me.PatientSurname.Value = Nz(rs(1), "")
Me.PatientNHSNo.Value = Nz(rs(2), "")
rs.Close

Me.Place_of_contact = "Home (including residential homes)"

Set rs = CurrentDb.OpenRecordset("SELECT tbl_Nurses.Nurse_ID FROM tbl_Nurses WHERE tbl_Nurses.Surname='" & Me.Combo51.Value & "'")
Me.Nurse_Id.Value = rs(0)
rs.Close
Me.PatientFirstName.SetFocus
Me.Command23.Enabled = False
End Sub

sql-server-2005 error-message microsoft-access

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-21T07:40:46Z

Fatherjack answered Apr 21, '10

this link seems to resolve the same problem as you are encountering
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.modulesdaovba.ado&tid=9f2fcc8c-1167-476d-afa1-cdffcdd92cd3&cat=&lang=&cr=&sloc=&p=1

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Apr 21, 2010 at 07:55 AM

+1: nice. Did you resist the lmgtfy? :)

1 ·

Fatherjack ♦♦ commented · Apr 21, 2010 at 07:57 AM

Hah! very nearly did. taking deep-breathing pauses regularly

0 ·

TimothyAWiseman commented · Apr 21, 2010 at 03:02 PM

Ok, for the aconym-challenged amoungst us, what is lmgtfy?

0 ·

Fatherjack ♦♦ commented · Apr 21, 2010 at 03:37 PM

@Tim - simply this http://lmgtfy.com/

0 ·

Kev Riley ♦♦ commented · Apr 21, 2010 at 05:11 PM

Tim: sorry about that. We are bound to see those type of questions here, it just comes with the territory. It's quite funny, sometimes the lmgtfy results can point back to the very question that caused someone to say lmgtfy. Still...helps to bring traffic here so that's got to be good!

0 ·

Answer 2 · 2010-04-21T07:52:27Z

Kev Riley answered Apr 21, '10

Yup, the error message tells you everything you need really, in that you need to specify the dbSeeChanges option.

I'm guessing it's on the second OpenRecordSet, where you are trying to return the ID (an IDENTITY ?) from the Nurses table.

I'm not over familiar with VBA, but it seems to be needed here (line breaks my own):

Set rs = CurrentDb.OpenRecordset
        ("SELECT tbl_Nurses.Nurse_ID FROM tbl_Nurses WHERE tbl_Nurses.Surname='" 
          & Me.Combo51.Value & "'", dbSeeChanges)

the link provided by FatherJack also suggests that dbOpenDynaset may also be needed

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question