dbSeeChanges

I'm getting an error : A new error has occured. Run-time error 3622

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

I've tried adding dbSeeChanges (in red below), but I then get an Invalid Argument Error.

Can anyone tell me where I'm going wrong. Thanks.

Dim rs As ADODB.Recordset
Lot = """" & Me.Lot.Value & """"
SQL = "Select * from dbo_WipDevStatus where Lot = " & Lot & ""
Set rs = CurrentDb.OpenRecordset(SQL, dbSeeChanges)

Sponsored Links:

Related Topics

using dbSeeChanges
I have had to upsize an Access 2003 BE to SQL Server 2005. This means for each table in SQL Server that has an IDENTITY field I will need to add the optional parameter dbSeeChanges to each recordset used.
What I would like to know is, does it matter if I use the dbSeeChanges option on tables that don't have an IDENTITY field?
Also can it be used on local access tables?

The reason I ask is that it is easier to change all OpenRecordset commands than just the ones that use the IDENTITY field.


dbSeeChanges
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


dbSeeChanges
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


You Must use dbseechanges option with openrecordset Error
I am trying to run queries in other databases by using the following code:

'Opening the Database Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Test\Test12\Testdb.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


Error #3622 After Migrating my Database
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:

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:

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:

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


error 3061
I have a form, in which, when the user enters the zipcode, it should obtain the relevant city, state and county from the table.

After entering the zipcode, it gives out an error 3061 and highlights as below:

Set rstAny = dbAny.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

The fullcode is as below.

Private Sub txtZipCode_AfterUpdate()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
strSQL = "SELECT Z.City, Z.StateCode, C.CountyCode, C.PrimaryPOPArea " & _
"FROM ZIPCodes As Z INNER JOIN ZipToCountyLink As C " & _
"ON Z.ZIPCode = C.ZIPCODE " & _
"WHERE Z.ZIPCode=" & Chr(34) & Me.txtZipCode & Chr(34) & _
" ORDER BY C.PrimaryPOPArea Asc"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If rstAny.RecordCount > 0 Then
rstAny.MoveLast
rstAny.MoveFirst

Me.txtCity = rstAny!City
Me.txtState = rstAny!State


View Select Query using DAO
I am trying to create a button on a form and have it run a select query and display the results using DAO. it debugs fine, just does not actually show me the query

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qry_Test", dbOpenSnapshot, dbSeeChanges)


ODBC Error 3146 on rs.update
Using MS Access, I have a form that is based on a table called Motion_Imagery. In the VBA of this code I want to take two fields on the form and add the data in those two fields to the last record that was added to table Online_Books. When I run my code I get the error when it gets to rs.update, here is my code:

Set db = CurrentDb()
Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset, dbSeeChanges)

rs.MoveLast

rs.Edit
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR


Vba rs.update causing odbc error 3146
I am getting an error 3146 odbc on rs.update. I have a form (form1) based on table motion_imagery. In form1, using

VBA I'm trying to load two fields from form1 into table 2, File_Books.

Set db = CurrentDb()
Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset, dbSeeChanges)

rs.MoveLast

rs.Edit
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR


Why isn't this working?
I have used this type of code many times before, but its simply not working now for some odd reason I can't figure out. I am using Access 2003 with a SQL database. The error I get is ODBC --call failed 3146
Any help would be appreciated. Here is the code:

Dim rsDxMg As Recordset Dim db As database Set db = CurrentDb Set rsDxMg = db.OpenRecordset("Select * FROM [Exam Info]", dbOpenDynaset, dbSeeChanges) With rsDxMg
.AddNew
!riop = "100"
.Update End With


Issues with using SSMA (SQL Migration Assistant)
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?


Adding a new record with VBA
I am trying to add a new record using the following code

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_TimeTracker", dbOpenDynaset, dbSeeChanges)

With rs
.AddNew
!IIDI_Major_ID = Me.IIDI_Major_ID
!Time = Me.Time
!Status = Me.Status
!Notes = Me.Notes
!UserID = CurrentUser()
.Update

End With
rs.Close

Now the problem I'm getting is that I get a runtime error # 3146 ODBC call failed error message and is highlighting the .Update part of the code The strange part is that the above table is updated with the new record?


Append records from form
I'm not quite sure how to put this (or if it is even possible) but I'm sure one of you will be able to tell me.

I am entering records through a form (as you would ), closing the form and the record displays in the table.

What I would like to do is be able to modify that record on the form and on clicking a command button, if the record has changed, append it to the table instead of overwrite it thus keeping the old record in the table as well

I imagine (very broadly) that the code would look something like.

IF Me.something.Value is not null AND Me.something.Value <> Me.something.Oldvalue THEN Dim db As Database, tb As DAO.Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("SomethingTable", dbOpenDynaset, dbSeeChanges)
tb.AddNew
tb!Something = [Forms]![SomethingForm]![SomethingSubForm].[Form].Something
tb.Update
tb.Close
End If

please stop laughing now, I'm still trying to figure this lot out


Access to SQL error
I'm converting an Access database to SQL and can't get this Module to run. It won't go past the Loop and if I take out Loop I get an "End With with With" error.

Function ClearPrintBook()
'Make all Printing set to = False This first part works!
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordset("Sheet1", dbOpenDynaset, dbSeeChanges)
With rst
Do While Not .EOF
.Edit
!Printed = False
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing


VBA to add record into SQL 2005 not working
Hye guys

Any idea why this bit of code might not import into a table? I dont get any errors and all the values are correct, it just simply will not add the data into the table.

ErrHandler:



If Err.Number <> 0 Then
CurrentDb.Execute "INSERT INTO [tblErrorLog] SELECT """ & wbk.Name & """ AS WorkbookName,#" & Now() & "# AS ErrorDate,""" & Err.Description & """ AS ErrorDesc;", [dbSeeChanges]
Err.Clear: On Error GoTo -1: On Error GoTo 0
End If
wbk.Close 0
Set wks = Nothing
Set wbk = Nothing

WorkbookName - nvarchar(255)
ErrorDate - datetime
ErrorDesc - nvarchar(255)
WFB-Last-Update - datetime ((getdate()))
ErrID - uniqueidentifier


Stuck with syntax for Module!
I've written a short module to update a field in my table:

Function TestUpdateQuery()
Dim strSQL As String
strSQL = ""

DoCmd.SetWarnings False
strSQL = "UPDATE tblLocal set [LoadBoxNo] =2" & " WHERE [LoadBoxNo] =100 And [TruckNo] = " & Forms!frmDriverEdit2.Form!txtTruckNo & ";"
CurrentDb.Execute (strSQL), dbSeeChanges + dbFailOnError
End Function

I need to filter by delivery date also, therefore i've added Delivery Date to the SQL statement:

strSQL = "UPDATE tblLocal set [LoadBoxNo] =2" & " WHERE [DeliveryDate] = " & Format(Forms!frmLoadAllocation.Form!cboAllocationD ate.Column(0), "mm/dd/yyyy") And [LoadBoxNo] =100 And [TruckNo] = " & Forms!frmDriverEdit2.Form!txtTruckNo & ";"

but I'm getting a compile error. I can't seem to see where I'm going wrong


Access 2003 crash with Office 2010
I have a problem with an Access 2003 (mdb) running with Office 2010 applications on Windows 7, Windows XP & Windows Server 2008 R2. Access 2003 runs fine with Office 2003 on Windows XP The line it crashes on ( or hangs if I step into it) is "dbsLocal.Execute strSql, dbFailOnError + dbSeeChanges" dbsLocal is a Database object.

strSQL contains an update statement, if I change it to an insert statement I get no errors.

But I need to be able to update, Another thing I noticed was that a textbox that contains the Default Value of "=Format(Now(),"mm/dd/y")" doesn't populate correctly.

I get "#Name?" on computers with Office 2010 instead of what's expected "06/21/2012" on computers with Office 2003. I tried to unregister vbe6.dll and replacing it with the one on my computer and registering it again, but no luck.

The version it originally had was 6.5.10.5.10.53. Here is the what Event Viewer shows Faulting application msaccess.exe, version 11.0.5614.0, stamp 3f3c8e3c, faulting module vbe6.dll, version 6.5.10.53, stamp 4a947697, debug? 0, fault address0x001cc4a3.

but pointed to vbe7.dll instead of vbe6. I've tried to compress and repair the database with no luck &