Results 1 to 14 of 14
  1. #1
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    Access 2003 problem - more than 255 fields
    I have a problem with my Access database that I use with WebMerge.

    Currently, I have one large table with details of each artist on my site. With all of the information I have for each artist, and with queries making up even more information, and another wealth of information to add for each artist, this will push the number of fields per artist to over 255, the limit with Access.

    Obviously, normalizing the database would allow me to add far more information but then using a query, I would still have over 255 fields.

    Is this an impossible situation?

  2. #2
    I like traffic lights
    Join Date
    January 18th, 2005
    Location
    Southern hemisphere - away from Fukushima
    Posts
    2,936
    combine some of the fields using a delimiter like pipe, than split them up again later using split().

  3. #3
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    I never realised you could do that! So, if I had three fields "Official_name","Official_url" and "Official_desc" which could be "Radiohead","hxxp://www.radiohead.com/" and "Official site for Radiohead.", would I instead use one field called "Official" and that field would contain "Radiohead|hxxp://www.radiohead.com/|Official site for Radiohead."?

    Would this be in the original table or would I have to split the table and then combine the fields in a query?

  4. #4
    I like traffic lights
    Join Date
    January 18th, 2005
    Location
    Southern hemisphere - away from Fukushima
    Posts
    2,936
    Split the table? You've lost me.

    Until then, you and I were on the same page.

  5. #5
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    I mean, would joining the fields be something I do in the actual table or in a query?

    If it's something I don't do in the original table, I'm going to have the same problem with too many columns.

    I just need a little bit more information before I get going with it.

  6. #6
    I like traffic lights
    Join Date
    January 18th, 2005
    Location
    Southern hemisphere - away from Fukushima
    Posts
    2,936
    Exactly. You've answered your own question.

  7. #7
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    Could you expand on 'split()'? Is this something to do with Javascript or VB? How and when would I use this before I pass it through WebMerge?

    Thanks.

  8. #8
    I like traffic lights
    Join Date
    January 18th, 2005
    Location
    Southern hemisphere - away from Fukushima
    Posts
    2,936
    Ah Sorry. I'm a LAMP guy. I'm used to having easy ways to do things.

  9. #9
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    Okay, thanks for the er, help.

  10. #10
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    Well, I've been giving this a lot of thought and I can't see a better way around the problem than this:

    Combine the fields "Official_name","Official_url" and "Official_desc" ("Radiohead","hxxp://www.radiohead.com/" and "Official site for Radiohead.") into one memo field "Official" and include all the information in the three fields like this: "<a href="hxxp://www.radiohead.com/"><b>Radiohead</b></a> - Official site for Radiohead."

    As long as WebMerge is fine processing a field containing HTML into HTML then I'm fine.

    Not pretty and certainly not a simple solution but unless anybody can think of anything better (please? lol) then that's the way to go.

  11. #11
    Affiliate Manager
    Join Date
    January 18th, 2005
    Location
    Los Angeles, California
    Posts
    1,913
    If Access doesn't support the number of fields you need it sounds like maybe it's time to find another database that supports what you need.

    I'm not sure if FileMaker Pro allows more than 255 fields, but it's a snap to learn and worth looking into. A 30-day free trial version is available from the vendor at filemaker.com.
    Richard Gaskin
    Developer of WebMerge: Publish any data feed on any site
    http://www.fourthworld.com

  12. #12
    Member MarkJH's Avatar
    Join Date
    January 18th, 2005
    Posts
    68
    I don't think that Filemaker Pro would be powerful enough to cope with the size of tables that my database uses. 8,000 records with 200+ fields would probably make it choke.

    I'm not certain but I think it may also have a limit of only 90 fields.

  13. #13
    Affiliate Manager
    Join Date
    January 18th, 2005
    Location
    Los Angeles, California
    Posts
    1,913
    Quote Originally Posted by MarkJH
    I don't think that Filemaker Pro would be powerful enough to cope with the size of tables that my database uses. 8,000 records with 200+ fields would probably make it choke.

    I'm not certain but I think it may also have a limit of only 90 fields.
    From the technical specs at http://filemaker.custhelp.com/cgi-bi...i=&p_topview=1

    Number of fields/columns per record: 256 million total fields over life time of file.

    I assume "over the life time of the file" means adding and deleting fields. I haven't worked with FMP in some years, but IFAIK it can handle far more than 200 fields, and a few orders of magnitude more than 8k records.
    Richard Gaskin
    Developer of WebMerge: Publish any data feed on any site
    http://www.fourthworld.com

  14. #14
    Member
    Join Date
    January 18th, 2005
    Location
    Australia
    Posts
    118
    Can you use multiple tables by splitting the current one? Access allows 2048 tables, each with 255 fields. Then run a query and export to the tab delimited file that WM wants.

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Problem with If and null fields
    By tvsmvp in forum WebMerge (Fourthworld.com)
    Replies: 2
    Last Post: August 16th, 2006, 12:28 PM
  2. Replies: 1
    Last Post: February 9th, 2005, 12:49 PM
  3. $3,255.12 BettyMills Sale...
    By Victor Hanna in forum BettyMills
    Replies: 3
    Last Post: December 22nd, 2004, 08:59 AM

Posting Permissions

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