Link to home
Create AccountLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

OpenRecordset & SQL Server

Hello Experts,

I just converted my Access 2003 DB to SQL 2008 using the upsizing Wizard in Access, using most defaults.  After, in my UI I deleted my tables and linked to the new SQL DB.

Now, whenm i run my app, the first error i get is

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

at CurrentDb.OpenRecordset(strSQL)

My first question is what do i do?

If i i need to change the above line in my code, I use that line maybe 1,000 times in my code, so hopefully i can use Find & Replace, or do something in my startup proc?

My final concern here is that i also use this to read AND write , but something tells me that dbSeeChanges  is read-only.

Any advice will be greatly appreciated
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
If you're anxious/concerned about the change, a good approach is to try it with an isolated test recordset first...
Avatar of APD Toronto

ASKER

Do you think that this the only change required qith the Access-to-SQL migration?
by the way, if i do this code change, and decide to dwngrade back to Access, will I need to edit these again?
<if i do this code change, and decide to dwngrade back to Access, will I need to edit these again? >

I wish I had a copy of Access handy right now to test that for you...  I don't think you would have to edit them again (its something you can test with a access back-end).

I suggest that you make a backup of your Front End exactly as it is now and save it very clearly named as a pre-upgrade backup.

<Do you think that this the only change required qith the Access-to-SQL migration? >

From recollection, there is nothing else in code (Front-End) that would give you trouble.

One other thing I remember having to change however, was ensuring that boolean fields have default values.



 
Okay I just ran a check on an Access back-end using the followiing recordset code with dbSeeChanges:

Function q()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblFields", dbOpenDynaset, dbSeeChanges)
Debug.Print rs.Fields(0).Name
rs.AddNew
rs!Field1 = "Blah"
rs!MyFieldID = 16
rs.Update
rs.Close
Set rs = Nothing
End Function

Open in new window



It worked perfectly as expected, so backward compatibility with this code should not be a problem at all.
do you know if i need to add dbSeeChanges to my CurrentDB.Execute statements?
All of our Action Queries in our Access/SQL databases use DoCmd.RunSQL.

I'm not sure if there is a reason for that or not - I can't test it out right now.

It has been a long time since we upsized our databases to SQL Server, so I'm going to see if I can get some other sets of eyes on this question ...
Yes, any table which includes an Identity column requires the dbSeeChanges option on DAO methods.
(So yes, you would include it when executing statements too.)

You would be able to leave the statements as if moving back to a Jet/ACE backend.

It's a great reason for having a single definitive "OpenRecordset" function which you use throughout - as you can then alter the way your recordsets open in one fell swoop.
(Or include it by default even when developing in Jet/ACE... but sooo few people would do that I'd say.)

Other differences?
Well - there's server side work (such as including a TimeStamp column for tables with a floating point or ullable Bit column - but that wouldn't affect your client side application code).
And implemtation differences (for example the "late" returning of an Identity column's value compared to the "early" insertion of a Jet/ACE Autonumber.

So ammending Miriam's earlier code...
Suppose you had:

Set rs = CurrentDb.OpenRecordset("tblFields", dbOpenDynaset, dbSeeChanges)
rs.AddNew
    rs!Field1 = "Blah"
    rs!MyFieldID = 16
    lngNewID = !IDColumn  'Grabbing the Jet Autonumber value
rs.Update

Open in new window


You'd really have to change that.
Set rs = CurrentDb.OpenRecordset("tblFields", dbOpenDynaset, dbSeeChanges)
rs.AddNew
    rs!Field1 = "Blah"
    rs!MyFieldID = 16
rs.Update
rs.Bookmark = rs.LastModified
lngNewID = !IDColumn  'Grabbing the Identity value

Open in new window


The latter has the advantage of working with either Jet/ACE or SQL Server BE's.
But then, so does:
Set rs = CurrentDb.OpenRecordset("tblFields", dbOpenDynaset, dbSeeChanges)
rs.AddNew
    rs!Field1 = "Blah"
    rs!MyFieldID = 16
rs.Update
lngNewID = CurrentDb.OpenRecordset("SELECT @@Identity", dbOpenSnapshot)(0) 'Grabbing the Jet Autonumber value

Open in new window


Of course - there are preferences for adding records through a stored procedure in SQL Server - and returning the identity value therein.  But that requires an entirely different implementation.

Cheers.
Arrghh I can't help myself from mentiong that in the above example, of course, you natrually wouldn't open a recordset of the entire table just to add a record.  It is just an example.
Otherwise you'd really have

Set rs = CurrentDb.OpenRecordset("tblFields", dbOpenDynaset, dbSeeChanges + dbAppendOnly)

Or

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblFields WHERE 0", dbOpenDynaset, dbSeeChanges)

Or both! ;-)

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblFields WHERE 0", dbOpenDynaset, dbSeeChanges + dbAppendOnly)

In theory, you're more sure of an optimisable query by not using a literal criterion.
so instead of
SELECT * FROM tblFields WHERE 0
something like
SELECT * FROM tblFields WHERE IDColumn = {Impossible or unlikely Value}
e.g.
SELECT * FROM tblFields WHERE IDColumn = -1

(But that's by no means a given that -1 isn't going to occur... But it shouldn't unless you, as the developer, have deliberately allowed it to.)
Hi Leigh,

He's got another one here that's falling by the wayside, if you're interested:
https://www.experts-exchange.com/questions/27438144/SQL-Server-Query-empty-in-subform-not-in-Report.html

A query that worked with an Access BE is now returning 0 records with a SQL Back-end (I can't figure out why).
thank you
APD_Toronto,

LPurvis has posted some great information here.  A point split would be more appropriate...

Thanks
sorry i cannot undo
I'll live. ;-)

(Thanks anyway Miriam)
I was going to post in that thread, but got delayed and, upon my return, it seems that the initial suggestion I'd make has already been offered.
i.e. it sounds like not only do you set the recordsource of the form, but that it also has a Master/Child link field set up.
That would explain the difference in results given the, in theory, same implementation and SQL.
(Not much point in my posting there to just re-enforce that's already been offered.)
I know this question is closed, and the experts have weighed in with the two main things:  using dbSeeChanges and .LastModified.

For more information, I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.  It also has more detail on whether to use RowVersion/TimeStamp fields in your SQL Server tables, as mentioned by Leigh (LPurvis) above.

Cheers,
Armen