Error #3622 After Migrating my Database

[scotthutchings]

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?
help....

 

[pbaldy]

This is one of those rare error messages that is actually very accurate and descriptive. Here's an example:

Set rsLookup = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

The pattern, as the error says, is when the underlying table has an IDENTITY field (autunumber).

 

[scotthutchings]

Well that seemed to do the trick regarding err#3622 but for some reason, I can't get it to select my record:

The following code:

Set rsCompany = dbs.OpenRecordset("SELECT * FROM [Company Information] WHERE CompanyDefaultLocation = " & -1, dbOpenDynaset, dbSeeChanges)

now does not select my record. My table has the field CompanyDefaultLocation with a datatype bit. When I look at the data, it says "Yes" and "No" in the DefaultLocation column. If I remove the WHERE clause and debug.print rsCompany!DefaultLocation, it prints True and False. I have tried using True/False, Yes/No, and -1/0 for the WHERE clause but I am apparently missing something in my SQL statement. If I remove the WHERE clause, it successfully selects ALL of my records but I can't seem to be able to select only the record set at the company default.

Any idea?

 

[pbaldy]

I would have thought JET would handle the translation, but SQL Server uses 1 for True, so try

"...WHERE CompanyDefaultLocation = 1"

or

"...WHERE CompanyDefaultLocation = True"

If you use True/Yes, you don't want the value in quotes as you have it above.

 

[LPurvis]

I agree, ODBC should perform that translation (it's largely why it exists ;-).
However it's one reason why many choose to perform boolean/bit selections by comparison against the universally accepted zero for False.

Set rsCompany = dbs.OpenRecordset("SELECT * FROM [Company Information] WHERE CompanyDefaultLocation <> 0", dbOpenDynaset, dbSeeChanges)

Now... see if that also fails. Then it's something to chase.
(This newly converted Bit field - it has a default value of zero in the SQL Server table definition yes? And no Null values stored in it?)
How did you perform the converstion by the way?