Testing upscaling to SQL Server 2009. Running Access 2003.
Accessing SQL server via ODBC
My vba code cannot open DAO recordset that has SSMA_timestamp field.
Works on other tables.

Option Compare Database
Sub test()
Dim db As dao.Database
Dim inrec As dao.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")

'''Set rst = db.OpenRecordset("z_RFil5_tblReport", dbOpenDynaset,
dbSeeChanges) ' this one works - no SSMA_Timestamp field

Set inrec = qdf.OpenRecordset("tbl_Client_Data", dbOpenDynaset,
dbSeeChanges) ' this one fails - has SSMA_Timestamp field

Stop

End Sub



Error is 3421 data type conversion error.

Form which accesses table with SSMA_timestamp seems to work correctly
and allow updates.

The presence or absence of the SSMA_Timestamp seems to be what
distinguishes success or failure.

thoughts?

Bob

BobAlston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:iuq0pb$35t$1 (AT) dont-email (DOT) me:

No such thing.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 7/3/2011 4:05 PM, David-W-Fenton wrote:
Server 2008.

OK. Now that I corrected my typo, any ideas what the problem may be?

Bob

On 7/3/2011 4:51 PM, BobAlston wrote:

I found that this works.


Sub TestADODB()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tbl_client_data ", conn

Stop

End Sub


Not sure why my DAO code fails.

Bob

On 7/3/2011 5:35 PM, BobAlston wrote:

Anyone know why my DAO code did not work yet the ADO code did?

bob

BobAlston wrote:
Probably because DAO has not been updated to handle timestamp data.
Actually, timestamp fields are not intended to be modified by users - why
bother including it in your recordset? They have no information that is
useful to users, and are updated automatically by the database engine when a
row is changed.

I was puzzled by that SSMA thing, so I googled it and found

" ... is generated so that the Migration assistant can detect changes to the
data during the migration. I don't think it is used after migration is
complete, so it should be safe to drop these new columns once you are sure
everything is done."

On Sun, 10 Jul 2011 18:45:45 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Not sure what all is in that quote but TimeStamp aka RowVersion fields
are strongly recommended for use by Access when going against SQL
Server backends. It saves Access from having to examine each field
individually when doing updates to see if someone/thing else has
changed a field in the record.

Tony

--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Tony Toews wrote:
It was pretty much intact ...
.... but incorrect.
You're talking about linked tables, right? When using DAO or ADO to retrieve
data from the backend, it's no longer Access that will be checking the
timestamp column for changes, it is now up to the developer. I think my
point still stands about displaying the field to the user. It is not
intended to be useful to or editable by the end user.

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:ivda4d$qr7$1 (AT) dont-email (DOT) me:

The timestamp field is READ by Jet/ACE to determine whether the
record needs to be refreshed. That is, if the timestamp has changed,
it means somebody edited the record and the new data should be
pulled over. So, it's absolutely CRUCIAL that the timestamp field be
included.

I include a timestamp field in EVERY TABLE in SQL Server databases
where the front end is Access because it makes things much easier.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David-W-Fenton wrote:
Yes, I know what it's for. Why is that relevant to my point about showing
its contents to users? I was assuming the OP was intending to display it in
a form, otherwise why retrieve it in the DAO recordset? Maybe I'm wrong.
Maybe the OP did understand its purpose and intended to store the value in a
variable to be used when the user requested an update.

OK, I think you're responding to the quote you snipped where the author
advocated dropping the column after the migration completed. I should have
snipped that part because I really didn't agree with it. I only intended to
clarify what "SSMA_timestamp" referred to. When I first read the message, I
thought it was a new SQL Server datatype that I hadn't heard about. I didn't
realize it was the name of a field.

I do agree that timestamp columns do come in handy, especially when linked
tables are used. And they even come in handy when ADO is used because ADO
has the ability to utilize the field when making updates, if the developer
uses the proper arguments to enable it to be used.