Solved Error 3662 You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column

[June7]

Did you try dbOpenDynaset instead of adOpenDynamic?

 

[lacampeona]

Hello
sorry for late reply.
yes i try it and no is not working.
i put dbSeeChanges and then the module start working.

thanks to all of experts to resolving my problem

 

[Kayleigh]

Hi
I have this error flagging up practically every before and after update event of one table in my DB. I just trapped the error but was wondering if there was any way to completely avoid it.
Other errors which persist since linking to SQL Server tables are Error 91 whenever I try adding new record to different table and sometimes Error 94 - null value but not sure why this happens.
Any ideas how I can work out why these errors are happening when BE is linked SQL tables and how to resolve please?

 

[Isaac]

And @lacampeona if you don't need to actually edit the recordset, just use snapshot

 

[Isaac]

Hi
I have this error flagging up practically every before and after update event of one table in my DB. I just trapped the error but was wondering if there was any way to completely avoid it.
Other errors which persist since linking to SQL Server tables are Error 91 whenever I try adding new record to different table and sometimes Error 94 - null value but not sure why this happens.
Any ideas how I can work out why these errors are happening when BE is linked SQL tables and how to resolve please?

One error ( + diagnosis, root cause, solution) at a time.

Describe your SQL table structure, show all your code, etc. etc.

 

[lacampeona]

Hello Krayna,
did you change your ID to primary key in the tables on sql?
do you have any module/procesure behind your before and after update? in your forms?
my error was in my audit trail...

 

[Minty]

Hi
I have this error flagging up practically every before and after update event of one table in my DB. I just trapped the error but was wondering if there was any way to completely avoid it.
Other errors which persist since linking to SQL Server tables are Error 91 whenever I try adding new record to different table and sometimes Error 94 - null value but not sure why this happens.
Any ideas how I can work out why these errors are happening when BE is linked SQL tables and how to resolve please?

If you have any Yes/No (Bit) fields they will cause you no end of pain see here http://allenbrowne.com/NoYesNo.html

You shouldn't be trapping these errors, you should be fixing them!

 

[Kayleigh]

Yes I think it was the audit trail in my DB as well. But have just temporarily removed the code - definitely need to address the underlying issue here.

And thanks for bringing this up as I discovered from this thread how dbSeechanges can help sort issues I've been having.

 

[gemma-the-husky]

out of interest, you need it with currentdb.execute as well.

currentdb.execute qryname, dbseechanges

 

[Kayleigh]

Thank you.

Now I am having another weird issue. The code to add new record in recordset works fine on my computer with SQL Server backend. Then when I copy DB to the computer which will be running it - still local but different SQL server instance it keeps bringing up error 3146. Any ideas what is happening here??

 

[WayneRyan]

Kayla,

How are you connecting to the Server?
Are they connecting to YOUR server?
Do you have remote logins enabled?

Wayne

 

[Kayleigh]

So its really 2 issues:
1. I'm currently testing the prototype DB on the Server itself and it won't allow recordsets to create new records. ODBC call failed - even when I use dbSeeChanges
Further to this issue I have locate the following info which seems to nail it but I'm quite new to SQL server and ODBC drivers so not sure what it all means. If someone can explain what to do in layman's terms I would appreciate.
Link: https://jeffpar.github.io/kbarchive/kb/160/Q160762/

2. When trying to have remote users connect to the Server to run the DB on their own machine it will not connect to the SQL Server even though the driver has been installed.

 

[Minty]

That article is referencing 25 + year old technology. I'd be amazed if any of it was relevant.

1. Please post up the exact code, unedited, that you are using. Recordsets don't create records, append queries do, or recordset edits/adds might do, but it's rarely the efficient way to do it.
2. As per @WayneRyan, with SQL server you can't just get anyone to connect, they either specifically need permissions, or the server is able to check their login credentials via an active directory link. So how are you trying to connect them? How is the FE authenticating against the BE database?

 

[Kayleigh]

1. My code generally is:

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

    rs.AddNew
        rs!fldJLOrderID = Me.fldOrderID
        rs!fldJLStaffID = Forms!frmlogin!cmbstaff
        rs!fldJLDateTime = Now()
        rs!fldJLNote = txt
    rs.update

rs.Close

Set db = nothing
Set rs = nothing

2. We are using the active directory link. FE authenticates using the sa login to SQL server.

 

[NauticalGent]

or the server is able to check their login credentials via an active directory link.

One of the sites I visit frequently posted some handy code to do this recently. Would have come in handy about 2 years ago but I was assured by my IT that it was impossible.

 

[Minty]

Using the SA account is not recommended, if you are using Active Directory login's have you tested them?

There doesn't appear to be anything intrinsically wrong with that recordset code, however, you are opening the whole table to insert one new record. (Using a naming convention is to be admired, but typing fld and the table name before every field would drive me nuts...)

If this is an audit log, I would pass the whole thing to a stored procedure in a module using a generic pass-through query, just because I already have that all set-up.

Something like

    strSQL = "sp_AUDIT_InsertRecord " & glUserID & ", '" & sVersion & "' , '" & sProcess & "', '" & sMessage & "', '" & glCompName & "'"
     sSendToPT_Generic strSQL, False

 

[Kayleigh]

I've pinpointed the issue finally! It wasn't the recordsets per se - I tried inserting same values in an append query and it would not allow due to INCOMPATIBLE DATA TYPES. So I keep coming back to the same issue - date/time field does not allow certain values if formatted to datetime2. So I've changed to datetime and success!
Yes I will try that for the audit code though. Didn't know append queries can be pass through.

Yes I have tested the Active Directory login but keep getting an error. Perhaps permissions?

 

[Minty]

Yes I have tested the Active Directory login but keep getting an error. Perhaps permissions?

Glad you have sorted your error out.

What permissions do the users have or the groups that they are in on SQL server?