You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column
One error you may get when querying SQL Server databases through MS Access is "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column". This error appears when you open recordset that contains IDENTITY column.
Usually you have to do something like this to get this error (FindTrainer query gets data from SQL Server table).
Public Function GetTrainerId(strTrainerName As String) As Integer
Dim query As QueryDef
Dim rs As Recordset
Set query = CurrentDb.QueryDefs("FindTrainer")
query.Parameters("pName") = strTrainerName
Set rs = query.OpenRecordset()
If rs.RecordCount = 0 Then
rs.AddNew
rs("Name") = strTrainerName
rs.Update
rs.MoveLast
rs.MoveFirst
End If
GetTrainerId = rs("ID")
Set rs = Nothing
Set query = Nothing
End Function
To avoid this error you should use dbSeeChanges option when opening the recordset. You can see that I am using two parameters when I open recordset. First one, dbOpenDynaset, sais to Access that I need dynamic recordset and second one, dbSeeChanges, sais that there may be changes that are made in server and we need to retrieve row again after inserting or updating it.
Public Function GetTrainerId(strTrainerName As String) As Integer
Dim query As QueryDef
Dim rs As Recordset
Set query = CurrentDb.QueryDefs("FindTrainer")
query.Parameters("pName") = strTrainerName
Set rs = query.OpenRecordset(dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then
rs.AddNew
rs("Name") = strTrainerName
rs.Update
rs.MoveLast
rs.MoveFirst
End If
GetTrainerId = rs("ID")
Set rs = Nothing
Set query = Nothing
End Function
After adding these two parameters to recorset opening calls the error disappeared and everything started to work normally.
What can I do about doCmd.RunSQL when it demands that I use dbSeeChanges?
rs.MoveFirst and rs.MoveLast is not a reliable means of obtaining the ID of the newly created record unless you have an exclusive lock on the underlying table.
Thanks. It removed a substantial headache from me.
Thanks, this was exactly what I needed!
Thank you very much, its really help for me and things are very much smooth.
Please can you help me for this function as well, i want to execyte a Update query through this method, its runs but the result is not as per requirement.
CurrentDb.Execute(myqry)
what is best option to use this method if we have SQL Server at backend.
Thank you, I wasn’t sure where to add dbSeeChanges until I saw your posting. This worked for me (finds the contact selected and deletes it from the current project):
Dim dba As DAO.Database
Dim rst As DAO.Recordset
Dim strFind As String
Set dba = CurrentDb
Set rst = dba.OpenRecordset(“ContactAssoc”, dbOpenDynaset, dbSeeChanges)
strFind = “[AssocID]=” & Me!AssocID
rst.FindFirst strFind
rst.Delete
Set rst = Nothing
Set dba = Nothing
Thanks again!
Pingback:Dbseechanges Option With Openrecordset When Accessing A Sql Server | yuyu
The articles you write help me a lot and I like the topic http://www.kayswell.com
F*ckin?awesome things here. I am very glad to see your article. Thanks a lot and i am looking forward to contact you. Will you kindly drop me a e-mail?
Your articles are extremely helpful to me. Please provide more information! http://www.hairstylesvip.com
Hey there! I know this is kind of off topic but I was wondering which blog platform are you using for this site? I’m getting fed up of WordPress because I’ve had problems with hackers and I’m looking at alternatives for another platform. I would be fantastic if you could point me in the direction of a good platform.
Thank you for providing me with these article examples. May I ask you a question? http://www.kayswell.com
Thank you for writing this post! http://www.kayswell.com
How can I find out more about it? http://www.kayswell.com
Whats up this is kinda of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding skills so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!
Lovely blog! I am loving it!! Will be back later to read some more. I am taking your feeds also.
May I request more information on the subject? http://www.kayswell.com All of your articles are extremely useful to me. Thank you!
Thank you for your articles. They are very helpful to me. May I ask you a question? http://www.hairstylesvip.com
Your articles are extremely helpful to me. May I ask for more information? http://www.hairstylesvip.com
I really appreciate your help http://www.kayswell.com
Your articles are very helpful to me. May I request more information? http://www.kayswell.com
Thanks for posting. I really enjoyed reading it, especially because it addressed my problem. http://www.kayswell.com It helped me a lot and I hope it will help others too.
I’d like to find out more? I’d love to find out more details. http://www.kayswell.com
Yet another thing I would like to convey is that in lieu of trying to suit all your online degree programs on days and nights that you end work (since the majority of people are drained when they get back), try to find most of your classes on the weekends and only one or two courses in weekdays, even if it means taking some time away from your weekend break. This is beneficial because on the week-ends, you will be a lot more rested in addition to concentrated with school work. Thanks a bunch for the different suggestions I have mastered from your website.
Sustain the excellent work and producing in the group! http://www.ifashionstyles.com
The articles you write help me a lot and I like the topic http://www.kayswell.com
Your articles are extremely helpful to me. Please provide more information! http://www.ifashionstyles.com
Thank you for writing this article. I appreciate the subject too. http://www.kayswell.com
Your articles are extremely helpful to me. Please provide more information! http://www.ifashionstyles.com
You helped me a lot by posting this article and I love what I’m learning. http://www.kayswell.com
Your articles are extremely helpful to me. May I ask for more information? http://www.kayswell.com
What a information of un-ambiguity and preserveness of valuable familiarity on the topic of unexpected feelings. http://www.kayswell.com
I’m curious to find out what blog system you have been using? I’m experiencing some small security issues with my latest blog and I’d like to find something more safe. Do you have any suggestions? http://www.kayswell.com
I am actually happy to glance at this blog posts which consists of lots of useful information, thanks for providing such information. http://www.hairstylesvip.com
Hi! I know this is kinda off topic but I was wondering if you knew where I could find a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having difficulty finding one? Thanks a lot! http://www.kayswell.com
I feel this is one of the such a lot important information for me. And i’m satisfied reading your article. But should observation on some normal issues, The site style is perfect, the articles is actually nice : http://www.kayswell.com
It’s actually very complex in this busy life to listen news on TV, so I just use world wide web for that reason, and get the newest news. http://www.kayswell.com