Solved

Using datatype Text (SQLServer) or PM (Access) with ADO

Posted on 1999-01-15
8
327 Views
Last Modified: 2013-12-25
After I have inspected the value of a column with datatype PM (or Text in SQLServer) using an ADO recordset the value vanishes into thin air!

Ex:
    dim varA as variant, varB as variant

    varA=adoRS("PMField")
    'varA now contains data
    varB=adoRS("PMField")
    'varB now contains NULL

Also, if I reference the value of a PM-column that is located AFTER a column of another type, before I reference the other column, the values of the column before my PM-column is reset.

Ex: If the table looks like this:

   PM1    Int1    Int2    PM2

   If I reference PM2 before I reference Int1 or Int2 the
   values in Int1 and Int2 become zero.


Help me, anyone...?
0
Comment
Question by:anderse
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:cognition
ID: 1499078
You will need to change your code to :

varA=adoRS("PMField")
varB=varA

If the data that is held in the text field is very large, then you will also need to investigate the use of chunks. See article Q180368 in the MS knowledge base.

You will even get this problem if you use the instant watch, to view the value before it is assigned to the variable. It also happens in RDO.

0
 

Author Comment

by:anderse
ID: 1499079
The first example I made was just that, an example, and was as such only entered to make my problem more clear.

I also need to know why accessing a later column of type PM erases the values in the previous, unaccessed columns.
0
 

Expert Comment

by:tato
ID: 1499080
xcuse me what is ADO?
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499081
I have always thought it was a bug in rdo, and now in ado, so perhaps it is a bug in SQL Server.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:anderse
ID: 1499082
What did you think was a bug? PM datatypes erasing the values of previous columns or the fact that you can only 'touch' a PM field once?
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499083
I assume the problems are related. I have only seen the problem where you can only touch a PM/TEXT field once, and have always programmed round it.

I don't know what PM is (other than equivalent to TEXT), so I imagine the problem is with rdo/ado.

If you use the getchunk methods, then the problem MIGHT not occur.

TATO
ADO is activeX data objects, RDO is remote data objects. DAO is Data access objects. They are VB objects that allow you to manipulate databases.

0
 

Author Comment

by:anderse
ID: 1499084
I guess the best thing to do is to, as you say, program around it. But I still feel uneasy about it... Mysteriously loosing data isn't the funniest of situations..

Thanks for the help.
0
 
LVL 3

Accepted Solution

by:
cognition earned 100 total points
ID: 1499085
I hope that I have answered your question !
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vba find the last empty column 10 88
message box in access 4 41
Visual Studio search word table and return Cell index 8 48
VB 6 error 5 in windows 10 but not in XP 7 39
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now