Fields.Count is always 1

[ah]

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?

 

[Allen Browne]

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?

 

[ah]

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]

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.

 

[ah]

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

 

[ah]

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.

 

[Allen Browne]

Thank you very much for posting the solution.

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