Problem with Memo field in Jet 2.5 Parameter Query 
Author Message
 Problem with Memo field in Jet 2.5 Parameter Query

YOU!  Yes, you the expert.  Please explain this, if you can:

I am using Visual Basic 4.0 16-bit (Enterprise) to access (through Jet
2.5) an MS Access 2.0 database.  In the database, I have an update query
that accepts parameters.  In general, the query's SQL looks like this
(please excuse slight syntax errors, I don't have it right in front of

PARAMETERS [qpID] Long, [qpFoo] Long, [qpMemo] LongText
UPDATE tblBogus SET Foo=[qpFoo], MemoField=[qpMemo] WHERE ID=[qpID]

In the code, I set a QueryDef variable to this query and set all the
parameters and execute it.  The code bombs with an "Invalid Property
Value" error if the value of [qpMemo] is > 255 characters.  Just so
we're on the same page, here's a code snippet:

dim qdTemp as QueryDef

(Fill local variables with values from form, including
long text value)

set qdTemp = MyDB.QueryDefs("qruMyUpdateQuery")
With qdTemp
  !qpID = lLongIDNumber
  !qpFoo = lSomeLongInteger
  !qpMemo = sLargeTextValue   '<-- Bombs here if Len(sLargeTextValue) >
End With

According to VB4 documentation AND Microsoft's TechNet, the LongText
parameter type (in the SQL for the query) should be able to handle 1.2
GB of text.  If I use the Jet "generic" parameter type of "Value"
(PARAMETERS ...,[qpMemo] Value), it still bombs.  If I remove the
PARAMETERS clause (which is optional anyway), it still bombs.

The help for the "Invalid Property Value" error says that a possible
cause is that the Value property of a Parameter object is too long.
Well duh.  But the Value should be able to handle 1.2 GB!  And I'm only
trying, oh, 1000 characters or less.

Now, Mr. or Mrs. Expert, do you know what I've missed?  The
documentation and the execution of this logic seems pretty
straightforward to me.  Where have I goofed?  Please don't suggest other
ways of updating records (i.e. using a RecordSet and .Edit, etc.)  I
need to use a parameter query here and I need to send large text values
through the parameters.  Any solutions?

Thanks in advance for any help!

Michael deFreitas
Whistler Computing, LLC

Mon, 25 Oct 1999 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. ADO 2.5 and Jet 4.0 Parameters

2. Can Jet 2.5 Interrupt a Query?

3. Memo fields - problems reporting 2 memo fields

4. Limit of 255 fields in Jet 2.5

5. Error setting field to Null with Jet 4 (SP5) and ADO 2.5

6. Memo Field Update Problem ADO JET

7. Jet 2.5 Vs. Jet 3.0

8. Jet 1.1 to Jet 2.0 or 2.5

9. Corruption problem of ADO 2.5 against Jet 4 ??

10. Jet Engine 2.5 problems

11. Crystal 4.0/Jet 2.5 Problem - Please help!

12. Crystal 4.0/Jet 2.5 BIG Problem - Please help!


Powered by phpBB® Forum Software