cannot open dao recordset for table with SSMA_timestamp

#21

David-W-Fenton wrote:

Quote:

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

David-W-Fenton wrote:
"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:ivq3db$jpk$1 (AT) dont-email (DOT) me:

David-W-Fenton wrote:
I don't believe that an Access developer should be using ADO
for anything at all, except the tiny number of things that ADO
can do and DAO can't.

Like this one? :-)

I don't know what you're referring to.

Umm, the reason this thread began? The inability of DAO to open a
recordset containing a timestamp field? :-)

But that's not really true. I have an Access app running against SQL
Server 2008 R2 and it opens recordsets with timestamps (all the
tables have timestamps in them). Or, I assume it does.
In general, I
don't use recordsets for operations that would benefit from
including the timestamp field, so I'd likely not include it.

Frankly, I'd question the need to use a DAO recordset in a fashion
that requires the timestamp.

So would I but it's not really relevant. The OP stated that he did not
really nead the timestamp field. He got it (or attempted to get it) because
he used selstar instead of naming the fields he wanted to retrieve,
discovering in the process that DAO refused to open the recordset.

Here's the snip from the original post:

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

#22

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in
news:j07mbr$cq8$1 (AT) dont-email (DOT) me:

Quote:

The OP stated that he did not
really nead the timestamp field. He got it (or attempted to get
it) because he used selstar instead of naming the fields he wanted
to retrieve, discovering in the process that DAO refused to open
the recordset.

To me, using SELECT * is pilot error, so this is really not
something I'm going to worry about.

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

#23

On Jul 20, 6:44*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

James A. Fortune wrote:
On Jul 13, 6:38 am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
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.

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

See:

http://groups.google.com/group/micro...msg/e88f75345b...

It appears that it is all done seamlessly without any action by the
developer. *I agree that users don't need to see the TimeStamp/
RowVersion value. *It looks like all the fields will be checked for
changes if that field is missing.

Again, the white paper is talking about behaviour with linked tables:
"This paper focuses on the challenges encountered by Office Access
developers who rely on the Office Access (Jet) database engine to connectto
SQL Server over ODBC. The most common way this is done is by creating linked
tables in Office Access that use the SQL Server ODBC driver to connect to
tables in SQL Server databases."

I may be wrong but I do believe that all of that none of that happens when
using DAO to open or update a recordset. I'm not sure how it could if DAO
bombs when opening a recordset containing a timestamp field, as occurs to
the OP.

I see what you're talking about now. I've never used anything other
than a link or an ADODB command to access SQL Server data, so I've
never encountered the field type mismatch you've discovered when using
DAO. I also have not tried to use a DAO based command string to
connect with SQL Server. Therefore I have no experience on which to
base a hypothesis. Perhaps Microsoft never envisioned anyone trying
to use DAO in that way. None of the documentation I've read by them
has ever suggested such a usage of DAO.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

cannot open dao recordset for table with SSMA_timestamp

comp.databases.ms-access comp.databases.ms-access

Re: cannot open dao recordset for table with SSMA_timestamp - 07-20-2011 , 05:54 PM

Re: cannot open dao recordset for table with SSMA_timestamp - 07-22-2011 , 04:12 PM

Re: cannot open dao recordset for table with SSMA_timestamp - 07-22-2011 , 05:40 PM