Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Fields.Count is always 1

Discussion in 'Information Technology' started by ah, Jul 28, 2006.

  1. ah

    ah
    Expand Collapse
    Guest

    Hi,
    For some reason, Recordset object is not working any more in VBA code in
    Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    Recordset/Query I make has only one field. Even TableDef object has only one
    field, althroug, when I open the table it in Access UI, it looks OK.
    I tried, latest JET and Access updates, re-installing the Office,
    re-registering DAO, ...
    I have no more ideas. Anyone?
     
  2. Loading...

    Similar Threads Forum Date
    Should One Always Tell The Truth? Sikh Sikhi Sikhism Sep 20, 2016
    Getting Something Off Your Chest Always Makes You Feel Better Hard Talk May 15, 2016
    Secret to married bliss - the wife is always right! Love & Marriage Dec 20, 2009
    FAQs for Students If Sikhs can't cut their hair, why isn't it always down to their ankles? Questions and Answers Aug 17, 2009
    Always a winner - that's Darsh Singh Sikh Youth Mar 5, 2008

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Tell us more about this. Never seen it.

    Local tables (in this mdb), or attached tables (what kind?)

    Does looping through the fields work? Test code:
    http://allenbrowne.com/func-06.html

    Any other versions of Access installed?

    Secured database?

    Whenever Access gets confused, it's worth checking to make sure the Name
    AutoCorrect boxes are unchecked under:
    Tools | Options | General
    Then compact/repair.

    Does this happen in other databases as well?

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "ah" <admirhodzic@hotmail.com> wrote in message
    news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    > Hi,
    > For some reason, Recordset object is not working any more in VBA code in
    > Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    > Recordset/Query I make has only one field. Even TableDef object has only
    > one field, althroug, when I open the table it in Access UI, it looks OK.
    > I tried, latest JET and Access updates, re-installing the Office,
    > re-registering DAO, ...
    > I have no more ideas. Anyone?
     
  4. ah

    ah
    Expand Collapse
    Guest

    access97 is installed. it works fine. VB6 code works OK if I reference DAO
    3.51, but, the same code fails with Fields.Count=1 if DAO 3.6 is referenced.


    ok. for testing, I have made a new database with Acc2003, plain with all
    defaults, un-references ADO, made one table with three fields and one module
    with test procedure just to open a recordset on that one table and it fails.
    If fact, there's no error message, just
    Debug.Print r!f1, r!f2, r!f3
    prints the same value, value of the first field.


    compact/repair didn't help. Name AutoCorrect option is unchecked.

    regards


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%2384pSLLoGHA.4192@TK2MSFTNGP04.phx.gbl...
    > Tell us more about this. Never seen it.
    >
    > Local tables (in this mdb), or attached tables (what kind?)
    >
    > Does looping through the fields work? Test code:
    > http://allenbrowne.com/func-06.html
    >
    > Any other versions of Access installed?
    >
    > Secured database?
    >
    > Whenever Access gets confused, it's worth checking to make sure the Name
    > AutoCorrect boxes are unchecked under:
    > Tools | Options | General
    > Then compact/repair.
    >
    > Does this happen in other databases as well?
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "ah" <admirhodzic@hotmail.com> wrote in message
    > news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    >> Hi,
    >> For some reason, Recordset object is not working any more in VBA code in
    >> Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    >> Recordset/Query I make has only one field. Even TableDef object has only
    >> one field, althroug, when I open the table it in Access UI, it looks OK.
    >> I tried, latest JET and Access updates, re-installing the Office,
    >> re-registering DAO, ...
    >> I have no more ideas. Anyone?

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    DAO 3.51 is the correct version to use for an Access 97 mdb (JET 3.5.)
    DAO 3.6 is correct for an Access 2003 mdb (JET 4.)

    Sounds like there could be a problem with either your dao360.dll or
    msjet40.dll. In the Immediate Window (Ctrl+G), enter:
    ? References("DAO").FullPath
    You should a response like this:
    c:\program files\common files\microsoft shared\dao\dao360.dll
    Deselect the DAO reference.
    Close the database.
    Unregister the dll:
    regsvr32 /u "c:\program files\common files\microsoft
    shared\dao\dao360.dll"
    Re-register it:
    regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"
    Open the database.
    Select the reference again.

    Locate msjet40.dll (typically in windows\system32.)
    Right-click and choose Properties.
    On the Version tab, you should see:
    4.0.8xxx.0
    The minor verson might start with 9 (on a Win2000 machine), but if it is
    less than 8, get the JET 4 update from:
    http://support.microsoft.com/gp/sp

    If it still fails, test the database on another computer.
    And paste the full code you are using in this test database.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "ah" <admirhodzic@hotmail.com> wrote in message
    news:ulmruDNoGHA.3440@TK2MSFTNGP04.phx.gbl...
    > access97 is installed. it works fine. VB6 code works OK if I reference DAO
    > 3.51, but, the same code fails with Fields.Count=1 if DAO 3.6 is
    > referenced.
    >
    >
    > ok. for testing, I have made a new database with Acc2003, plain with all
    > defaults, un-references ADO, made one table with three fields and one
    > module with test procedure just to open a recordset on that one table and
    > it fails. If fact, there's no error message, just
    > Debug.Print r!f1, r!f2, r!f3
    > prints the same value, value of the first field.
    >
    >
    > compact/repair didn't help. Name AutoCorrect option is unchecked.
    >
    > regards
    >
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:%2384pSLLoGHA.4192@TK2MSFTNGP04.phx.gbl...
    >> Tell us more about this. Never seen it.
    >>
    >> Local tables (in this mdb), or attached tables (what kind?)
    >>
    >> Does looping through the fields work? Test code:
    >> http://allenbrowne.com/func-06.html
    >>
    >> Any other versions of Access installed?
    >>
    >> Secured database?
    >>
    >> Whenever Access gets confused, it's worth checking to make sure the Name
    >> AutoCorrect boxes are unchecked under:
    >> Tools | Options | General
    >> Then compact/repair.
    >>
    >> Does this happen in other databases as well?
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "ah" <admirhodzic@hotmail.com> wrote in message
    >> news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    >>> Hi,
    >>> For some reason, Recordset object is not working any more in VBA code in
    >>> Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    >>> Recordset/Query I make has only one field. Even TableDef object has only
    >>> one field, althroug, when I open the table it in Access UI, it looks OK.
    >>> I tried, latest JET and Access updates, re-installing the Office,
    >>> re-registering DAO, ...
    >>> I have no more ideas. Anyone?

    >>
    >>

    >
    >
     
  6. ah

    ah
    Expand Collapse
    Guest

    thanks for your messages.
    nothing helped. I used your TableInfo function and it always printed
    only the first field of the table.
    Database works fine on another PC.
    I think I'm going for a full windows re-install.

    regards


    Allen Browne wrote:
    > DAO 3.51 is the correct version to use for an Access 97 mdb (JET 3.5.)
    > DAO 3.6 is correct for an Access 2003 mdb (JET 4.)
    >
    > Sounds like there could be a problem with either your dao360.dll or
    > msjet40.dll. In the Immediate Window (Ctrl+G), enter:
    > ? References("DAO").FullPath
    > You should a response like this:
    > c:\program files\common files\microsoft shared\dao\dao360.dll
    > Deselect the DAO reference.
    > Close the database.
    > Unregister the dll:
    > regsvr32 /u "c:\program files\common files\microsoft
    > shared\dao\dao360.dll"
    > Re-register it:
    > regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"
    > Open the database.
    > Select the reference again.
    >
    > Locate msjet40.dll (typically in windows\system32.)
    > Right-click and choose Properties.
    > On the Version tab, you should see:
    > 4.0.8xxx.0
    > The minor verson might start with 9 (on a Win2000 machine), but if it is
    > less than 8, get the JET 4 update from:
    > http://support.microsoft.com/gp/sp
    >
    > If it still fails, test the database on another computer.
    > And paste the full code you are using in this test database.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "ah" <admirhodzic@hotmail.com> wrote in message
    > news:ulmruDNoGHA.3440@TK2MSFTNGP04.phx.gbl...
    > > access97 is installed. it works fine. VB6 code works OK if I reference DAO
    > > 3.51, but, the same code fails with Fields.Count=1 if DAO 3.6 is
    > > referenced.
    > >
    > >
    > > ok. for testing, I have made a new database with Acc2003, plain with all
    > > defaults, un-references ADO, made one table with three fields and one
    > > module with test procedure just to open a recordset on that one table and
    > > it fails. If fact, there's no error message, just
    > > Debug.Print r!f1, r!f2, r!f3
    > > prints the same value, value of the first field.
    > >
    > >
    > > compact/repair didn't help. Name AutoCorrect option is unchecked.
    > >
    > > regards
    > >
    > >
    > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > > news:%2384pSLLoGHA.4192@TK2MSFTNGP04.phx.gbl...
    > >> Tell us more about this. Never seen it.
    > >>
    > >> Local tables (in this mdb), or attached tables (what kind?)
    > >>
    > >> Does looping through the fields work? Test code:
    > >> http://allenbrowne.com/func-06.html
    > >>
    > >> Any other versions of Access installed?
    > >>
    > >> Secured database?
    > >>
    > >> Whenever Access gets confused, it's worth checking to make sure the Name
    > >> AutoCorrect boxes are unchecked under:
    > >> Tools | Options | General
    > >> Then compact/repair.
    > >>
    > >> Does this happen in other databases as well?
    > >>
    > >> --
    > >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    > >> Tips for Access users - http://allenbrowne.com/tips.html
    > >> Reply to group, rather than allenbrowne at mvps dot org.
    > >>
    > >> "ah" <admirhodzic@hotmail.com> wrote in message
    > >> news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    > >>> Hi,
    > >>> For some reason, Recordset object is not working any more in VBA code in
    > >>> Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    > >>> Recordset/Query I make has only one field. Even TableDef object has only
    > >>> one field, althroug, when I open the table it in Access UI, it looks OK.
    > >>> I tried, latest JET and Access updates, re-installing the Office,
    > >>> re-registering DAO, ...
    > >>> I have no more ideas. Anyone?
    > >>
    > >>

    > >
    > >
     
  7. ah

    ah
    Expand Collapse
    Guest

    OK!
    I have found the problem.
    Since my locale settings are not English, DAO 3.6 had some problems
    with it. After installing KB913296 Jet hotfix it works fine.


    ah wrote:
    > thanks for your messages.
    > nothing helped. I used your TableInfo function and it always printed
    > only the first field of the table.
    > Database works fine on another PC.
    > I think I'm going for a full windows re-install.
    >
    > regards
    >
    >
    > Allen Browne wrote:
    > > DAO 3.51 is the correct version to use for an Access 97 mdb (JET 3.5.)
    > > DAO 3.6 is correct for an Access 2003 mdb (JET 4.)
    > >
    > > Sounds like there could be a problem with either your dao360.dll or
    > > msjet40.dll. In the Immediate Window (Ctrl+G), enter:
    > > ? References("DAO").FullPath
    > > You should a response like this:
    > > c:\program files\common files\microsoft shared\dao\dao360.dll
    > > Deselect the DAO reference.
    > > Close the database.
    > > Unregister the dll:
    > > regsvr32 /u "c:\program files\common files\microsoft
    > > shared\dao\dao360.dll"
    > > Re-register it:
    > > regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"
    > > Open the database.
    > > Select the reference again.
    > >
    > > Locate msjet40.dll (typically in windows\system32.)
    > > Right-click and choose Properties.
    > > On the Version tab, you should see:
    > > 4.0.8xxx.0
    > > The minor verson might start with 9 (on a Win2000 machine), but if it is
    > > less than 8, get the JET 4 update from:
    > > http://support.microsoft.com/gp/sp
    > >
    > > If it still fails, test the database on another computer.
    > > And paste the full code you are using in this test database.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "ah" <admirhodzic@hotmail.com> wrote in message
    > > news:ulmruDNoGHA.3440@TK2MSFTNGP04.phx.gbl...
    > > > access97 is installed. it works fine. VB6 code works OK if I reference DAO
    > > > 3.51, but, the same code fails with Fields.Count=1 if DAO 3.6 is
    > > > referenced.
    > > >
    > > >
    > > > ok. for testing, I have made a new database with Acc2003, plain with all
    > > > defaults, un-references ADO, made one table with three fields and one
    > > > module with test procedure just to open a recordset on that one table and
    > > > it fails. If fact, there's no error message, just
    > > > Debug.Print r!f1, r!f2, r!f3
    > > > prints the same value, value of the first field.
    > > >
    > > >
    > > > compact/repair didn't help. Name AutoCorrect option is unchecked.
    > > >
    > > > regards
    > > >
    > > >
    > > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > > > news:%2384pSLLoGHA.4192@TK2MSFTNGP04.phx.gbl...
    > > >> Tell us more about this. Never seen it.
    > > >>
    > > >> Local tables (in this mdb), or attached tables (what kind?)
    > > >>
    > > >> Does looping through the fields work? Test code:
    > > >> http://allenbrowne.com/func-06.html
    > > >>
    > > >> Any other versions of Access installed?
    > > >>
    > > >> Secured database?
    > > >>
    > > >> Whenever Access gets confused, it's worth checking to make sure the Name
    > > >> AutoCorrect boxes are unchecked under:
    > > >> Tools | Options | General
    > > >> Then compact/repair.
    > > >>
    > > >> Does this happen in other databases as well?
    > > >>
    > > >> --
    > > >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > >> Tips for Access users - http://allenbrowne.com/tips.html
    > > >> Reply to group, rather than allenbrowne at mvps dot org.
    > > >>
    > > >> "ah" <admirhodzic@hotmail.com> wrote in message
    > > >> news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    > > >>> Hi,
    > > >>> For some reason, Recordset object is not working any more in VBA code in
    > > >>> Access 2003. I have DAO 3.6 referenced, code compiles OK, but, every
    > > >>> Recordset/Query I make has only one field. Even TableDef object has only
    > > >>> one field, althroug, when I open the table it in Access UI, it looks OK.
    > > >>> I tried, latest JET and Access updates, re-installing the Office,
    > > >>> re-registering DAO, ...
    > > >>> I have no more ideas. Anyone?
    > > >>
    > > >>
    > > >
    > > >
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Thank you very much for posting the solution.

    Hopefully this will help someone else if they are researching the same
    problem.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "ah" <admirhodzic@hotmail.com> wrote in message
    news:1152357363.559411.73630@h48g2000cwc.googlegroups.com...
    > OK!
    > I have found the problem.
    > Since my locale settings are not English, DAO 3.6 had some problems
    > with it. After installing KB913296 Jet hotfix it works fine.
    >
    >> > >>
    >> > >> "ah" <admirhodzic@hotmail.com> wrote in message
    >> > >> news:et3v6CLoGHA.1332@TK2MSFTNGP05.phx.gbl...
    >> > >>> Hi,
    >> > >>> For some reason, Recordset object is not working any more in VBA
    >> > >>> code in
    >> > >>> Access 2003. I have DAO 3.6 referenced, code compiles OK, but,
    >> > >>> every
    >> > >>> Recordset/Query I make has only one field. Even TableDef object has
    >> > >>> only
    >> > >>> one field, althroug, when I open the table it in Access UI, it
    >> > >>> looks OK.
    >> > >>> I tried, latest JET and Access updates, re-installing the Office,
    >> > >>> re-registering DAO, ...
    >> > >>> I have no more ideas. Anyone?
     

Share This Page