Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    49

    Unanswered: Storing Large Amounts of text in Memo Format

    I am trying to insert large amounts of text into a db with one of the columns being the datatype Memo.


    It seems when someone submits it, it returns


    f༑Ā硰


    Any ideas?

  2. #2
    Join Date
    Nov 2005
    Posts
    49
    Alrighty....I now think it has something to do with my submit command/insert sql query I got going on. I edited the DB manually and it shows up fine. But when I hit the submit button I setup it doesn't insert fine.

    here is the sql insert


    Private Sub cmdshiftmorning_Click()
    DoCmd.RunSQL "INSERT INTO shiftsummary ( day, morning, morningsup) VALUES ( [txtday], [txtmorning], [cmbmorningsup]);"

    MsgBox "Enjoy your day "
    DoCmd.Close
    Any suggestions?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Memo field

    When I run an insert into a memo field, I first declare something as a variant, copy the memo I'm inserting to the variant, and then use the variant name in the insert statement. I found that sometimes the memo can be cut off to 255 characters unless I do this.

    Also, if you're using SQL Server as a backend, you might want to consider adding in a TimeStamp field to that table which may also solve the problem.
    Last edited by pkstormy; 01-08-06 at 16:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2005
    Posts
    49
    How would adding the timestamp field solve my problem?

    My problem is that the text being inserted it not being inserted properly. For instance, if I try and insert Joe said, "hello". It won't show up because the "'s are messing up the insert command.

  5. #5
    Join Date
    Nov 2005
    Posts
    49
    I did what you said about inserting it as a veriable and I get the following error

    Run Time error '3075'
    Syntax error (missing operator) in query expression "<large amount of text goes here>"

    I am using
    DoCmd.RunSQL "INSERT INTO shiftsummary ( day, morning, morningsup) VALUES ( [txtday], '" & [txtentry] & "', [cmbmorningsup]);"
    to insert it.

  6. #6
    Join Date
    Nov 2005
    Posts
    49
    By using
    "" & txtentry & ""
    it pastes & txtentry & into the db.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look in the help system for escape / escaping characters. The problem is that you are wanting to include quote marks in your data stream, which is always an issue.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Memo Field

    You can try:


    DoCmd.RunSQL "INSERT INTO shiftsummary ( day, morning, morningsup) VALUES ( [txtday], """ & [txtentry] & """, [cmbmorningsup]);"

    I'm not a 100% sure why the timestamp works but I know that when I added it to the SQL Server table, some of the issues I had with inserting Memo fields went away. Someone else might have more info on this.

    In regards to dim a variable as a variant, you could try:
    dim vartxtentry as variant
    vartxtentry = txtentry
    DoCmd.RunSQL "INSERT INTO shiftsummary ( day, morning, morningsup) VALUES ( [txtday], """ & vartxtentry & """, [cmbmorningsup]);"

    But I'm not sure where you're grabbing txtentry from.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Memo and TimeStamp

    We had some problems with some insert statements where we would get an error or strange returns when running the function. Someone on the site suggested adding in a timestamp field the table and it worked great. I think the timestamp field is a quick fix for SQL Server to handle some of the wierd things that seem to come up.

    It's easy to add to the table (just add a new field and call it: TimeStampField with a data type = TimeStamp.) It's worth a shot to test out if it's your problem verses writing lines and lines of code.

    Don't forget to refresh your linked tables once it's been added.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •