Question Data dissapearing from field in recordset

[Babbage]

Hi there all!

Ive a strange one someone may be able to throw some light on.....

We have a recordset, and every time we access a certain field in that recordset, the data in that field dissapears/null's

The data is sourced from a linked table (odbc) to an ms sql 2005 server. We've tried recreating the field, copy the database and creating a new copy of the field, relinking our tables, refreshing our tables, copying the data into temporary variables, you name it. That particular field has a death wish!



Here has a better description and video showing whats happening

Anyone any ideas what might cause that / how we might fix it?

Many thanks

Martin

 

[DCrake]

Simple Software Solutions

Can you post a little more code so we can get a better picture.

 

[Babbage]

Theres nothing much more to see really. So many variations of trying this have been tried. But anyway.... (A couple of names have been changed in the code for security reasons )

Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
 
Dim conPubs As Connection
Set conPubs = wrkODBC.OpenConnection("", , , ODBCConnectionString)
 
Set psrcrs = conPubs.OpenRecordset("SELECT * FROM tablename WHERE id = " & id, dbOpenSnapshot)
 
...
 
While Not psrcrs.eof
  [COLOR=darkred] *** reference psrcrs!OtherFields here - they work fine[/COLOR]
[COLOR=darkred]  *** reference psrcrs!ProcessDescription here - the dissapearing value as shown[/COLOR]
    psrcrs.movenext
Wend

The same code works elsewhere fine. The same code accessing another table works fine. Variations of the code all have the same issue when accessing the field. The order you access the field in question compaired to the other fields in the recordset make no difference.

 

[DCrake]

Simple Software Solutions

Are you expecting to get more than one record in your recordset?

If so, is the data missing for the one field for all records?

 

[Babbage]

The same thing happens for every record in the recordset

First time you look at that field when viewing a record, its value dissapears!

(and as shown by the video, you don't even need to have any code access the variable to do it, access can do it all by itself )

Martin

 

[ByteMyzer]

Congratulations! You have encountered another bug... er, undocumented feature!

When you open a DAO.Recordset in Access to an ODBC Connection/Workspace where the data source is MS SQL Server 2005, and one of the returned fields in the recordset is a memo field (SQL Server field type ntext), and you attempt to enumerate the field in question more than once on a single record, the field data becomes invisible to your VBA session!

There are two ways to overcome this quirk:

1) Reset the DAO recordset's bookmark. Ex.

Debug.Print rs.Fields("MyMemoField")
rs.Bookmark = rs.Bookmark
Debug.Print rs.Fields("MyMemoField")

However, this becomes cumbersome to code everywhere you may encounter a Memo field, and not performance efficient.

2) Use an ADO connection and recordset. Ex.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
    .ConnectionString = "Provider=sqloledb;" _
        & "Data Source=myServerAddress;" _
        & "Initial Catalog=myDataBase;" _
        & "User Id=myUsername;" _
        & "Password=myPassword;"
    .CursorLocation = adUseServer
    .Open
End With

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM myTable;", cn, _
    adOpenForwardOnly, adLockReadOnly, adCmdText

Debug.Print rs.Fields("myMemoField")
Debug.Print rs.Fields("myMemoField")

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Where MS Sql Server as a database back-end is concerned, with Access as a front-end, ADO offers greater advantages, from a performance standpoint. ADO has less trouble translating the SQL Server field types, and with a server-side cursor, data operations are considerably faster.

 

[Babbage]

Sir - that is spot on information, may thanks

I thought (hoped) it was a bug, and wasn't just me going mad!

Many thanks for the code snippet to.

Martin