Results 1 to 11 of 11

Thread: Field Limit?

  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Field Limit?

    I have been trying to import a table from a Lotus Notes database into Access through ODBC. I have had no problem until I found that a field that shows up in Notes is not showing up as a field in Access. Access is showing 297 fields available when I know there are more. Any ideas how I can access these additional fields? (I did not create the original database so I cannot alter it)

    Also, is there some kind of field limit in Access 2003?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Posts
    75
    From Access help:
    Table specifications

    Attribute Maximum
    Number of characters in a table name 64
    Number of characters in a field name 64
    Number of fields in a table 255
    Number of open tables 1,024. The actual number may be less because of tables open internally by Microsoft Access.
    Table size 1 gigabyte
    Number of characters in a Text field 255
    Number of characters in a Memo field 65,535 when entering data through the user interface;
    1 gigabyte when entering data programmatically.
    Size of an OLE Object field 1 gigabyte
    Number of indexes in a table 32
    Number of fields in an index 10
    Number of characters in a validation message 255
    Number of characters in a validation rule 2,048
    Number of characters in a table or field description 255
    Number of characters in a record (excluding Memo and OLE Object fields) 2,000
    Number of characters in a field property setting 255

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmmmmmm

    i dont know if there is a hard field limit but there should be!
    A is supposed to be a relational database: it's a bit sad to think of one table with 297 fields.

    3*99=297 + 2 Foreign Key fields is (to SQL) the same: 3 related tables will fix the problem?

    .....and are you really really really sure you have 297 fields that belong in the same ("atomic", "normalised") table?

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by izyrider
    mmmmmmmm

    i dont know if there is a hard field limit but there should be!
    A is supposed to be a relational database: it's a bit sad to think of one table with 297 fields.

    3*99=297 + 2 Foreign Key fields is (to SQL) the same: 3 related tables will fix the problem?

    .....and are you really really really sure you have 297 fields that belong in the same ("atomic", "normalised") table?

    izy
    I'd be happy to split the table into 3 related tables. However, the problem I'm having is Access (when importing through ODBC) doesn't even SEE more than 297 fields (when there is MORE than 297 fields). It just doesn't even show the remaining fields.

    For my Access database I am pulling the information from a Lotus Notes database that I did not create nor do I have the access to alter it. It is a poorly designed database that does not need to contain 297+ fields. But for me to even attempt to correct their wrongs I need to at least be able to access the entire database.

    Norm
    Last edited by norm801; 01-30-04 at 15:16.

  5. #5
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20
    maybe you don't have permission to view the hidden fields in Lotus Notes.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well ...siblimsos told you the story... 255 max


    do multiple imports


    i don't know notes, but the workaround in .XLS is to export as .CSV and import ///Advanced/ "ignore field" for the other 200 during the import.

    repeat * 3 with different ignores (but keep your putative primary key!!)

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This may sound like a stupid question but, do you know (or can you find out) how many columns there are in the LN table? If so you might be able to run multiple queries retrieving XXX number of columns at a time ...

  8. #8
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by M Owen
    This may sound like a stupid question but, do you know (or can you find out) how many columns there are in the LN table? If so you might be able to run multiple queries retrieving XXX number of columns at a time ...
    That seems like a reasonable idea. The only problem I can think of is the fact that I have already tried to import a field using SQL. This field is not showing up as an option when I try to import using ODBC and does import when I try to create a simple SQL statement but I know it exists.

    So I'm not sure if it would let me call up any columns past the 297th.

  9. #9
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    "Hidden fields"

    It has been a few years since I've used Lotus Notes but I did know it well enough to get some certifications on it. What I can tell you is that the security was pretty robust and could be controlled at the field level.

    Can you get or do you have administrator access to that database?

    AFA the issue of normalization is concerned (someone mentioned it earlier), Notes is not a relational platform. So all those lovely ideas about atomic data and normalized tables are generally meaningless. In fact, it doesn't surprise me that you have too many fields in a table because a lot of Notes developers were not trained in database architecture and design.

    I used to fudge things some to try to approximate relational design in Notes but that runs counter to the design model of Notes so it's ugly and a pain in the behind to do. People who hadn't used other database models, often didn't even try.

    The bad news: if you can't get a copy of the administrator's or some other certificate that has full access to that .ndf file, you may be not be able to pull that data out. The good news: if you can see that data as a particular user, I would think you'd be able to get the data out connected to the database as that user. I'm pretty sure you'll need to have the certificate so that it can authenticate. (This is where being rusty may have me on the wrong/old track.)

    BTW, there are tools designed to connect between Notes and RDMS's. I'll see if I can't dig up my notes and try to find out what they're called.

    Magee
    Last edited by mageem; 01-31-04 at 22:32.

  10. #10
    Join Date
    Jan 2004
    Posts
    100

    Re: "Hidden fields"

    Originally posted by mageem
    It has been a few years since I've used Lotus Notes but I did know it well enough to get some certifications on it. What I can tell you is that the security was pretty robust and could be controlled at the field level.

    Can you get or do you have administrator access to that database?

    AFA the issue of normalization is concerned (someone mentioned it earlier), Notes is not a relational platform. So all those lovely ideas about atomic data and normalized tables are generally meaningless. In fact, it doesn't surprise me that you have too many fields in a table because a lot of Notes developers were not trained in database architecture and design.

    I used to fudge things some to try to approximate relational design in Notes but that runs counter to the design model of Notes so it's ugly and a pain in the behind to do. People who hadn't used other database models, often didn't even try.

    The bad news: if you can't get a copy of the administrator's or some other certificate that has full access to that .ndf file, you may be not be able to pull that data out. The good news: if you can see that data as a particular user, I would think you'd be able to get the data out connected to the database as that user. I'm pretty sure you'll need to have the certificate so that it can authenticate. (This is where being rusty may have me on the wrong/old track.)

    BTW, there are tools designed to connect between Notes and RDMS's. I'll see if I can't dig up my notes and try to find out what they're called.

    Magee
    I'm pretty certain I do have access to the missing fields. I have been able to access the other 297 fields and, in Access, the list just stops alphabetically when I get past field 297. So I don't think it's a rights issue. If you knew of other tools that would help this situation that would be great.

    Norm

  11. #11
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Hey Norm!

    The tools are called "Connectors" in Notes parlance.

    Here's the link to IBM's Lotus Notes content with a chart for connectors. You can download a trial once you determine which one is the right one for you.

    http://www.lotus.com/products/produc...256d9e0059f55f). Click on "Downloads" at left to get the software. If the page doesn't pull up (their links are hinky when you cut-and-paste), search for "EI Connector Support Matrix"

    Let me know if it's all greek to you. I haven't read through the entire list (busy, busy) but it looks like it had the connectors for ODBC OLEDB, Access, SQL Server, etc..

    Magee
    Last edited by mageem; 02-03-04 at 23:40.

Posting Permissions

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