Update/Insert into An Access2000 Memo field 
Author Message
 Update/Insert into An Access2000 Memo field

I'm having difficulty inserting into a memo field in Access2000 from VB6
code.  Is there a particular syntax for inserting into a memo field.  I get
an

Run-time error '-2147217900 (80040e14)':
Syntax error in UPDATE statement.

now if I remove just the portion of the statement that inserts into the memo
field it works fine.  But if I try to insert into the memo field the above
error appears. I'm using MDAC 2.51 and the Jet 4.0 provider. Below is my
UPDATE statement in case there is an error I just can't see.

**The fields Check, Retry and Special are all memo fields in Access2000. The
other fields insert fine if I remove those three.

strSQL = "UPDATE tblSkill SET Name = '" & varSkillDetail(1) & _
         "', Ability = '" & varSkillDetail(2) & _
         "', Trained = " & varSkillDetail(3) & _
         ", ArmorCheck = " & varSkillDetail(4) & _
         ", Check = '" & varSkillDetail(5) & _
         "', Retry = '" & varSkillDetail(6) & _
         "', Special = '" & varSkillDetail(7) & _
         "', Source = '" & varSkillDetail(8) & _
         "', PageSource = '" & varSkillDetail(9) & _
         "' WHERE ID = " & varSkillDetail(0)

Jason



Tue, 25 Nov 2003 22:26:56 GMT  
 Update/Insert into An Access2000 Memo field
As a simple note. This works when using an ODBC driver.  Not when using the
Jet 4 provider.


Quote:
> I'm having difficulty inserting into a memo field in Access2000 from VB6
> code.  Is there a particular syntax for inserting into a memo field.  I
get
> an

> Run-time error '-2147217900 (80040e14)':
> Syntax error in UPDATE statement.

> now if I remove just the portion of the statement that inserts into the
memo
> field it works fine.  But if I try to insert into the memo field the above
> error appears. I'm using MDAC 2.51 and the Jet 4.0 provider. Below is my
> UPDATE statement in case there is an error I just can't see.

> **The fields Check, Retry and Special are all memo fields in Access2000.
The
> other fields insert fine if I remove those three.

> strSQL = "UPDATE tblSkill SET Name = '" & varSkillDetail(1) & _
>          "', Ability = '" & varSkillDetail(2) & _
>          "', Trained = " & varSkillDetail(3) & _
>          ", ArmorCheck = " & varSkillDetail(4) & _
>          ", Check = '" & varSkillDetail(5) & _
>          "', Retry = '" & varSkillDetail(6) & _
>          "', Special = '" & varSkillDetail(7) & _
>          "', Source = '" & varSkillDetail(8) & _
>          "', PageSource = '" & varSkillDetail(9) & _
>          "' WHERE ID = " & varSkillDetail(0)

> Jason



Wed, 26 Nov 2003 02:39:16 GMT  
 Update/Insert into An Access2000 Memo field
Jason,

One thing to check. Is the memo field defined with
    "Allow Zero Length" = NO

If this is the case, then you will get an error every time the UPDATE
tries to set the memo field to "" .

Richard.

On Fri, 8 Jun 2001 10:26:56 -0400, "Jason Frederick"

Quote:

>I'm having difficulty inserting into a memo field in Access2000 from VB6
>code.  Is there a particular syntax for inserting into a memo field.  I get
>an

>Run-time error '-2147217900 (80040e14)':
>Syntax error in UPDATE statement.

>now if I remove just the portion of the statement that inserts into the memo
>field it works fine.  But if I try to insert into the memo field the above
>error appears. I'm using MDAC 2.51 and the Jet 4.0 provider. Below is my
>UPDATE statement in case there is an error I just can't see.

>**The fields Check, Retry and Special are all memo fields in Access2000. The
>other fields insert fine if I remove those three.

>strSQL = "UPDATE tblSkill SET Name = '" & varSkillDetail(1) & _
>         "', Ability = '" & varSkillDetail(2) & _
>         "', Trained = " & varSkillDetail(3) & _
>         ", ArmorCheck = " & varSkillDetail(4) & _
>         ", Check = '" & varSkillDetail(5) & _
>         "', Retry = '" & varSkillDetail(6) & _
>         "', Special = '" & varSkillDetail(7) & _
>         "', Source = '" & varSkillDetail(8) & _
>         "', PageSource = '" & varSkillDetail(9) & _
>         "' WHERE ID = " & varSkillDetail(0)

>Jason



Wed, 26 Nov 2003 12:20:49 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Updating data to a memo field using SQL UPDATE

2. Append memo field to a different memo field

3. Memo fields - problems reporting 2 memo fields

4. insert large data into memo field problem

5. inserting text to specific location into memo field

6. Insert a line break into a memo field?

7. Insert line break in memo field w/ code

8. inserting html code into access memo-field

9. Insert SQL with memo fields does not work

10. Using ADO Command object and Access PROCEDURE to insert Memo/Binary fields

11. Insert Text into a memo field using SQL

12. Problem updating memo field

 

 
Powered by phpBB® Forum Software