dbSeeChanges option ?

Hi,

I have an Access Front end App, using a link Table with SQL. The table has a Primary Key as "Auto Number". I just imported the Table to SQL and linked through Access, but when I enter a new record or changing an existing rec. I get an error message "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."
I am not a programmer so please if someone knows a solution, please explain in a way so I can understand what this means and what I am doing worng.I have to distribute this app on Monday to the users and I am warry about it

Thanks a lot,
Berti


Post your answer or comment

comments powered by Disqus
I am trying to run queries in other databases by using the following code:

	Code:
	'Opening the Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:TestTest12Testdb.mdb")
'Running specified queries
db.Execute "qry_try_This", dbFailOnError
Set db = Nothing

But I keep getting the error of:
Run time error 3622
You must use dbseechanges option with openRecordset option when accessing a SQL server table that has an Identity column

I am by far a beginner using DAO, and am not sure what exactly is causing this error, or how to correct it.

I have an application with an Access 2002 front end linked to Access 2002 db. I am trying to convert the db side to SQL Server 2000. Everything appears to link up properly and I can run most everything with no problems....upon further testing I have gotten this error and I am stumped...

"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has as IDENTITY column."

help?!?!

TIA

CJK

I have a combo-box which is generating the following warning message:

You must use the dbSeeChanges option with the OpenRecordset when accessing a SQL Server table that has an Identity column.

It doesn't seem like I can trap this warning message and I'm not sure how to get rid of it.

The form uses a query as its recordsource, so I'm not using VBA code (OpenRecordset) to access the data.

The data is coming from a SQL 7 table -- the column is an auto-number.

Thanks in advance..

I've migrated the whole database to SQL server and i got popup up with this error:

"error 3622 you must use the dbseechanges option with openrecordset when accessing a sql server table that has an identity column"

Debugging the issue is directing me to this line:

Set rst = CurrentDb.OpenRecordset("Select * from registrygz")

any guideline?

Hi all,

i'm trying to retrieve some info from a SQL table but i keep getting an error saying i need to add the dbSeeChanges option. i have found some bits on this forum about it but i am doing something slightly different and it doesn't work when i add it.

Here's what i've got....


	Code:
	Dim st As String
Dim res As New Recordset
Dim db As Database
Set db = CurrentDb

ReqNo = Me.lab3.Caption
stkCod = RTrim(Me.lab4.Caption)

st = "SELECT dbo_tbl_RetRANrequest.UNID " & _
      "FROM dbo_tbl_RetRANrequest " & _
      "WHERE dbo_tbl_RetRANrequest.ReqNo = '" & ReqNo & "' " & _
      "AND dbo_tbl_RetRANrequest.stockCode = '" & stkCod & "'"


Set res = db.OpenRecordset(st, dbOpenDynaset, [dbSeeChanges])


The red part is my SQL query result. In the other example the red text has speech marks ("st") aroung it but mine is coming from a variable and i get a type mismatch error.

Any ideas?

Cheers,
Spinkung.

Upsizing Access 2010 back end to a SQL Server 2008. Line 20 creates an error 3622. This worked in Access 2010 but errors in SQL Server.
The error description is:
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

If I globally replace all of my dbAppendOnly with dbSeeChanges, can anyone see any downside or problems?


	Code:
	          Dim rst As DAO.Recordset  ' The table Wells_ProgSentToField_History
              '    Table Name
20            Set rst = CurrentDb.OpenRecordset("Const_Reclamation_Remarks", 2, dbAppendOnly)
30            rst.AddNew
40                rst![ID_Wells] = ID_Wells
50                rst![Remarks] = Remarks
60                rst![User_ID] = Environ("username")
70                rst![Date_Entered] = Now()
80            rst.Update
90            rst.Close
100           Log_ConstRemarkHistory = True



Since I've converted to SQL Server, I now get the following error:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column." It didn't happen before. Any help would be super. This has been driving me cracked for hours. I have no doubt it's related to the following:

mySQL = "INSERT INTO tblSuFileActions (fkFileId,fkActionId,actionAuthoriser,actionDate,a ctionReason, actionPerson, actionCompany)"
mySQL = mySQL & " SELECT file_id," & Me.cboAction & "," & Me.cboAuthoriser & ",#" & Me.txtDate & "#, '" & Me.txtReason & "', " & Me.cboPerson & ", " & cboCompany & " "
mySQL = mySQL & " FROM (SELECT tblSuFile.file_id"
mySQL = mySQL & " FROM tblSuBoxLocation INNER JOIN tblSuFile ON tblSuBoxLocation.box_ID = tblSuFile.box_id"
mySQL = mySQL & " WHERE tblSuBoxLocation.box_ID=" & Me.cboBoxNo & " )"


CurrentDb.Execute mySQL, dbFailOnError

I'm running Access 97 and am in the process of converting my tables to SQL Server 2000. My project has a few forms, and in one form, I had no problems opening a table with the following:

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("dbo_tblNonSap")

However, since I've converted to SQL Server, I now get the following error:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

I've tried modifying the dbs.OpenRecordSet line to

Set rst1 = dbs.OpenRecordset("dbo_tblNonSap",,dbSeeChanges)

but that does not help.

Any comments or suggestions are most appreciated -- My head hurts from pulling out too many hairs!

Hi All

We have moved an access backend to SQL Server 2000, and use Access 2k as the front end.

When a user tries to delete a line record using the following code


	Code:
	DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

we get an error message

Quote: The DoMenuItem action was cancelled I changed the code to

	Code:
	DoCmd.RunCommand acCmdDeleteRecord

but got a message

Quote: You must see the DBSeeChanges option with open recordsets when access a SQL Server table that has an identity column Doing some searching on this forum, I amended my code to be the below. I get no error messages, but the record is not deleted either.

Any ideas?


	Code:
	Private Sub cmdDelRec_Click()
On Error GoTo Err_cmdDelRec_Click

Dim strSQL As String
Dim intLineNo As Integer

intLineNo = Me.ContLineNo


strSQL = "delete from tblContAttribLin where ContLineNo = " & intLineNo

If MsgBox("Delete this record?", vbQuestion + vbYesNo, "Delete?") = vbYes Then

    CurrentDb.Execute strSQL, dbSeeChanges
    Me.Refresh

End If


    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    'DoCmd.RunCommand acCmdDeleteRecord

Exit_cmdDelRec_Click:
    Exit Sub

Err_cmdDelRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelRec_Click
    
End Sub



Good morning all,

I have a form which acts as a search form to find records within the database.

The database was created in access but has since had the table upsized to SQL 2005. The Search Form was working but now I get the following error displayed.

You must see the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

The debugger takes me the the following lines of code.

Set db = CurrentDb
Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

Can anyone help me with where I should be putting the dbSeeChanges.

Many thanks,

A while back I had a question on how best to append a record without opening the entire recordset. The Better Approach SQL or DAO

I took the append approach

	Code:
	Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable", dbOpenDynaset, dbAppendOnly)

Everything worked fine in testing mode. Of course when I added it to the production database, it immediately crashed. As I experimented, I received two error messages. One of the errors was: "Run-time error '3001' invalid argument". The other error was "error: 3622 dbSeeChanges with SQL Server". Error 3622 Open SQL Server Table with Identity Column.

The problem is that we are using Microsoft SQL server as the production back-end and one of the fields is an auto-number field (Identity Column), which requires the dbSeeChanges option. In testing, I was using a "local" Access back-end version which worked just fine. After searching, I found the solution here. Adding a new record

The improved code is:

	Code:
	Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable", dbOpenDynaset, dbAppendOnly + dbSeeChanges)

I had figured that I needed to get dbAppendOnly and dbSeeChanges in the "options" block, but I was unaware of how to do it. Adding the "+" signed solved that issue.

I successfully (I think) migrated my Access 2007 db to MSSQL Server 2008R2 but I am getting error #3622 "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column." when I try to run the following code:

	Code:
	Set rsCompany = dbs.OpenRecordset("SELECT * FROM [Company Information] WHERE CompanyDefaultLocation Like 'Yes'")

Do you know what is causing this? I get a similar error with the following lines of code:

	Code:
	Set rs = CurrentDb.OpenRecordset("SELECT * From Contacts WHERE ContactCompany =""" & Me.Client & """")

and

Set rsLoginAttempt = db.OpenRecordset("LoginAttempt")

I don't see a pattern to know what I need to change.

I do not get the error when I run the code:

	Code:
	Set rs = db.OpenRecordset("SELECT * FROM Employee WHERE EmployeeUserName =""" & EnteredUserName & """")

and 

Set rstProjectName = dbs.OpenRecordset("Project File")

What is the dbSeeChanges option that is referred to in the error message?
help....

I am converting an MDb to an mdb linked to a SQL back end

In one of my normal mdb tables I had a table with service dates for a specific machine. This table had no primary key as this had an impact on my function that would update the service dates. It worked fine in access.
The problem with a autonumber was that i messes up the order in the record set.

Now that I have a SQL backEnd I had to assign a primary key + identity to the table in order to make it a table I can add/edit records.

Hwr now i am using my code I get the following error message
"You must use the dbseechanges option with openrecordset when accessing a SQL server table that has an Idenity Column"


This is the code i am using

Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Date
Dim g As Integer
Dim servicedate As Date

g = 365 / (AvgRunHrs / ServiceInt)

Set db = CurrentDb()
Set rs = db.OpenRecordset("TblServicebySerialSummary")
With rs
.FindFirst "SerialId = " & Forms![addserialdata].[SerId] & ""
servicedate = ![plannedServicedate]
Do Until rs.NoMatch
.FindNext "SerialId = " & Forms![addserialdata].[SerId] & ""
.Edit
![plannedServicedate] = servicedate + g
.Update
servicedate = ![plannedServicedate]
Loop
.Close

End With



ANY Suggestions here?

In general, my application appears to be working. But, here is a huge difference. If anyone has ideas on the best way to fix the dates.

Used the SQL Server Migration Assistant Wizard for Access.
Very noticeable are the date format changes.
2003-04-29 00:00:00 - the Date text boxes don't bring up the Date Picker anymore. The dates use to be 4/29/2003
It would appear SQL Server-s format is YYYY-MM-DD Access' format is DD-MM-YYYY
The SQL Server conversion by the tool is datetime2(0), null i.e. DateTime2


If I will try the Access Upgrade Wizard, would it manage the dates better?


FYI: Updated dozens of forms, but missed this form's OpenRecordset statement. At least Microsoft gave a nice description:
Microsoft Access You must use the dbSeeChanges option with OpenRecorset when accessing a SQL Server table that has an IDENTITY column.

UPDATE: Nobody responded so I will up date this post for now. Think I found the answer. Will document it and post it once I re-migrate.

I'm using this code on a cmd button to confirm an order and at the same time take the order off my listbox which lists pending orders.

CurrentDb.Execute "UPDATE tblConfirmations SET ConfirmFlag = True Where JOBID =" & Forms!frmSwitchboardF.ListConfirm
Forms!frmSwitchboardF.ListConfirm.Requery

My problem is I'm trying to link this to my sql server and install it on multiple stations using an MDE file.

When I use that linked MDE file this code doesnt work

Same thing when I use the Main DBA linked to SQL Server

I get an error: "you must use dbseechanges option with openrecordset when accessing a SQL server table that has an IDENTITY column"

what does it mean? and how do I solve this?

Hello, yet again. I feel like a school kid, always having to ask for help, truth is I've only been working with Access for a couple of weeks and some of the problems I am coming across are way out of my depth. My gratitude to all of you is huge, believe me.

OK, the latest problem. I have a sub form which collects, or is supposed to collect, information on just two fields, one is a number field the other a date field, with a calendar set next to it. My problem is that all of a sudden these fields are no longer updatable. When we try to input data we get "Error number 3622: you must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

Now I have checked the forum and dbSeeChanges in access help but can't seem to find the solution to this problem. The code used is as follows:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Dim qdf As QueryDef

' Append current information to the AppendQuery
If SaveRecord(Me) Then
Set qdf = CurrentDb.QueryDefs("qAppStandardisedScoresCopy")
qdf.Parameters("theID") = Me!txtKeyField
qdf.Execute
DoEvents
qdf.Close

' update the current query to have no results

Set qdf = CurrentDb.QueryDefs("qupdStandardisedScoresCopy")
qdf.Parameters("theID") = Me!txtKeyField
qdf.Execute dbSeeChanges
DoEvents
qdf.Close

'run the query

Me.Requery
Me.Parent.subStandardisedScoresPrevious.Form.Reque ry

'Make the necessary fields available on the current screen

Me!txtAB_GRP_ACTUAL.Locked = False
Me!txtAB_GRP_ACTUAL.BackColor = vbRequired
Me!txtAB_GRP_DT.Locked = False
Me!txtAB_GRP_DT.BackColor = vbRequired
Me!cmdFromDateCalendar.Enabled = True
Me!txtAB_GRP_COMMENT.Locked = False
Me!txtAB_GRP_COMMENT.BackColor = vbWhite
Me!chkAB_IPM.Locked = False

End If

Once again, I truly appreciate any help anyone can offer.

Many thanks

Ouma

I've been developing an Application in Access 2010 for a few weeks and I've just used SSMA to migrate the tables only to SQL Server 2008. For the most part things work but I have 2 questions ..

1. I went to some trouble to draw the Relationship disgram in Access. Now I don't see table connections - is that diagram invalid now? Referential integrity seems to be working - any comments?
2. Are there any tricks I should be aware of before I migrate? I already changed settings for dates and had to update some code to use the"dbSeeChanges" option when writing to a recordset. I also noticed that the error handling has changed a little too?

Am I crazy to attempt this or should I do all the table design in SQL to start with (not strong at that) .. Mark

Hi,

Can anybody tell me if it's possible to hide the last menu-item File (with just the left items Print, Options and Close), so users cannot open the item Options in the File menu. That's all what's left from the menu and navigation panel.

I'm using Access 2010 with a lot of VBA and my database is still a mdb.

Jeezed

By now, we are realizing Access 2013 doesn't leverage all of the events and power of Access 2010 (and previous).

We realize that companies may not take the plunge to suddenly retire all of the existing, proven, working Access applications.

One of the options going forward is to run MS Access via Citrix or Remote Desktop. Those of us who have been distributing Access are willing to share the information. My end users have both Windows Portable and Apple. They use wireless data in remote locations nationally.
In some ways, the Citrix or MS Remote might be less overhead to set up than setting up (and maintaining) a SQL Server.

I recently came across this article:
http://www.vb123.com/toolshed/05_acc...otedesktop.htm
There may be many others.

Expanding Life into Access 2010 through the next decade may not include moving to Access 2013 and beyond.

For those who may walk into work some morning and discover the IT Network department has upgraded to office 2013 over night, this would be a good option to have available.

If there is a MS Access Citrix / RT discussion opened:
Myself and others will be happy to share information about the printer problems (it actually is not a problem). And other helpful hints.

In closing, I feel it would give this excellent community a 2nd life.

An example of the Discussion would be:
Citrix / Remote Desktop
- Thin Client vs Web (discussion)
- Licensing
- Useful Articles
- Access Design Considerations
- Conversion - Setup - Maintenance
- Distributing and Releasing New Versions
- Managing Printers and Desktop
- Access Code (VBA)

Hi,

Is it possible to set a Macro or by use of Code, user can print duplex report i.e. two sided. I have looked in help and found this PrtDevMode, which contains duplex as member. I couldn't figure out how to use so, when user click on print licence it prints two copies of it using duplex option.

If someone can help on this please!

I have a report that's based on a query whose form has an option group. The Options (and Values) are N/A (0), Yes (1) and No (2). I have the option group set to store the numeric value in the table. I need to report the average of the option group without including the N/A's.

I need three records with the results of "Yes", "No" and "N/A" to return an average of "1.5", not "1". Any suggestions? It's still early in the developement of the db, so I'm open to making changes on any object.

Have created an option box. This enters 1,2,3,4..... as selected.

Now I want to count in a report the number of 1's and 2's etc.

Any ideas please...............

In the Print window when printing an Access Report there is the option to print to file. This saves the file in a .prn file. How can I then open up the .prn file I saved and print it?

Dear All,

I'd like to go to the print set-up pop-up that you ussually go to when printing from applications when I click on the print button on my forms.
At the moment I have no options at all, all pages of the report are printed directly upon click. Some reports are really too big for this.

Anyone any ideas how this can be achieved?

Thanks


Not finding an answer? Try a Google search.