Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    In my opinion, it seems like you are not going to have any fun with this one.
    I think you are stuck with the delete and append.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table & memo fields (Access 97, Win 95/98)

    Nope. You're stuck.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    Okay, okay. If Charlotte says it can't be done, then I HAVE to believe it.

    Bummer!

    Cecilia :-)

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    I checked as well, thinking that I had done that in SQL, but it turns out to have been in SQL Server. If you were running Access 2000, you might be able to do it using ANSI SQL, but you can't in Access SQL. If Charlotte says, that's it!
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table & memo fields (Access 97, Win 95/98)

    Ooohh! I never realized I had such awesome credibility! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Would anyone like to lend me money on that basis?? <img src=/S/devil.gif border=0 alt=devil width=15 height=15>
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    Charlotte, you DO have that credibility! Your posts have pulled me from the brink numerous times....

    Unfortunately, I'm at the brink again. I'm still dealing with this. Here's what it is.

    My datasource is DB2. I have zero control over it. "They" did something to it, but won't tell me (it could be an update of some field spec, or updated the drivers, or even update the version of DB2, but I'm not allowed to know about it).

    Before, I pulled over my info & it was fine. But there are three fields that contain comments, which could potentially be rather long. When I was using a make table query, it started cutting those comments off in the middle (because of Access's 255 limit). So I thought if I had the table already made with those fields as memos, then I'd be fine, right?

    Well now I'm getting datatype conversion issues. I've narrowed the cause down to those three fields. I can make a brand new table (with cut-off comments) from the data, but I can't append data to a table with memo fields for those comments. (Well, actually, I can for 26 of the 2,000 records, but I don't want to know why....)

    I'm stuck. And I just know that "they" are back in HQ laughing at me (See, we told you we could stop you from doing your job!!!). Grrr.

    Has anyone, anywhere run up against this? Okay, I really am hoping someone can help me figure out this issue, but even if you can just commiserate with me, it would be appreciated ;-)

    TIA,
    Cecilia :-)

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make table & memo fields (Access 97, Win 95/98)

    <P ID="edit" class=small>(Edited by Cecilia on 10-Sep-02 16:24. Edited to include the actual problem....)</P>Hi All!

    I have a question about making tables....I am connecting to DB2 and pulling down a long text field. I'm using a make table (insert into) to do this, and it automatically creates a 255 text field. Is there any way to make the field a memo field, but still use the make table command?

    I know I could just delete & append records instead of making the table, but where's the fun in that?

    Thanks,

    Cecilia :-)

    More on what exactly is wrong, maybe someone has experienced something similar and knows what to do?

    My datasource is DB2. I have zero control over it. "They" did something to it, but won't tell me (it could be an update of some field spec, or updated the drivers, or even update the version of DB2, but I'm not allowed to know about it).

    Before, I pulled over my info & it was fine. But there are three fields that contain comments, which could potentially be rather long. When I was using a make table query, it started cutting those comments off in the middle (because of Access's 255 limit). So I thought if I had the table already made with those fields as memos, then I'd be fine, right?

    Well now I'm getting datatype conversion issues. I've narrowed the cause down to those three fields. I can make a brand new table (with cut-off comments) from the data, but I can't append data to a table with memo fields for those comments. (Well, actually, I can for 26 of the 2,000 records, but I don't want to know why....)

    I'm stuck. And I just know that "they" are back in HQ laughing at me (See, we told you we could stop you from doing your job!!!). Grrr.

    Has anyone, anywhere run up against this? Okay, I really am hoping someone can help me figure out this issue, but even if you can just commiserate with me, it would be appreciated ;-)

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    Wendell, you have quite some credibility, too....

    We're moving to XP in two months. While this won't help me right now, can you point me to some info about what you're talking about for when we get there?

    Thanks!

    Cecilia :-)

    PS. Loved your commentary on the FE/BE issue!

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    Always happy to commiserate with someone, especially if the BIG IT guys are trying to make their life difficult. In any event, how are you pulling in the data for the DB2 tables? More specifically, are you connecting to them with an ODBC driver? If so you don't really have to import them into Access - you can simply work with them in your application. On the other hand, if you want to modify them for some reason, you probably won't be allowed to, as mainframe people tend to be pretty skittish in that case, and usually only give you read-only permission.

    If you are trying to work with calculated values and so on, you could either just copy the fields that you need using a query and exporting the results to a table, or you could create a table with the appropriate keys, and put only your calculated values in the table, and then do joins between the DB2 linked table and your Access table. Performance in that case is usually pretty ugly if you are dealing with lots of records, but some port in a storm is better than none.

    As to the fields that are giving you problems, is it possible that they have created a binary field of some sort that takes huge amounts of space, such as pictures. Memo fields are limited to 65,536 characters. If they are larger than that, you might try using an OLE Object field instead of a memo field. And that can be done in Access 97. Hope this helps.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    Hi Wendell!

    Thanks for the commisseration! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    >>In any event, how are you pulling in the data for the DB2 tables? More specifically, are you connecting to them with an ODBC driver?
    I am using a pass thru query, then making tables with the data. This way I can change the field names and do small calculated fields (ie. strconv and trim) when I "import" the data.

    It's entirely possible that they've created an ODBC issue for us AGAIN. Problem is, the last time we told them it was the driver, it took them almost a year to realize that we were right and six more months to fix it. At this point, my users (foolishly) think that I can do anything, so it's difficult going back and saying that my prog doesn't work anymore and we have to live without it ;-)

    >>If so you don't really have to import them into Access - you can simply work with them in your application.
    Hmmm. Are you assuming that I'm working in VB? Ugggh, I'm not allowed to use such dirty things as applications, VB, and programming. So my application is Access, unless you're talking about something else, something that could possibly get me past all this?

    >>As to the fields that are giving you problems, is it possible that they have created a binary field of some sort that takes huge amounts of space, such as pictures. Memo fields are limited to 65,536 characters. If they are larger than that, you might try using an OLE Object field instead of a memo field. And that can be done in Access 97. Hope this helps.
    I am wondering if this may be it...Users had complained (in the past) that the fields were too small. Can I import a text field from DB2 into an OLE Object? I guess I've got to try it to find out....

    Thanks for the hints! Let you know which one works....

    Cecilia <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>
    Really like these new smilies!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table & memo fields (Access 97, Win 95/98)

    It sounds as though you are indeed using ODBC to connect to the tables - that was about it in Access 97 until very recently. If that's the case, you should be able to simply link to the tables and then you can run select queries directly from the tables and do things like give aliases to fields if you want to change the names. Pass-through queries certainly have advantages in terms of performance, but they do limit things a fair bit. One thing to test the theory about OLE Object fields is to check the length of fields that are giving you trouble. If you can link, then I think you should be able to do a Len([BadField]) and get a number that tells you how big the field is. An interesting problem!
    Wendell

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table & memo fields (Access 97, Win 95/98)

    Can you link to the DB2 table and see the data in the fields? It's possible that the DB2 fields actually exceed the maximum characters Access supports in a memo field, but I would be very surprised ... unless they're writing novellas in those fields. Can you append a single memo field consistently? If so, one workaround might be to create separate tables for each memo field and append that plus whatever you're using as a key to the separate tables. Once you got them into Access you could deal with getting them into a single table.
    Charlotte

Posting Permissions

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