Can't create DAO recordset in a new base, but old bases work fine.

[David Llewellyn]

I have Access 2002 under XP SP2 (both French, both up to date according to
Windows Update, same libraries for old and new bases). My old bases work
fine, but for two days now when I create a new base, it behaves strangely:

1. the new base has 6 tables with names like MSysAccessObjects, which I have
never seen before,

2. It gives an "Error 13 - Incompatibilité de type" when I try to create a
DAO recordset. The code is:
db = Currentdb()
chSQL = "SELECT Parms.Symbol_File_Path FROM Parms;"
Set R = db.OpenRecordset(chSQL, dbOpenDynaset) - this gives the error.
Note that the SQL was copied from a query which works fine.

3. I created a dropdown list populated from a table containing names of
small gif files (i.e. small character strings). At first it worked, but
suddenly when I click on an element, nothing happens.

In many years of using Access, I have never had these kinds of problems. I
have looked everywhere I can think of, but I have not yet found the cause of
the problem.
What have I done to cause this? And where do I look to fix it?

 

[Robert Morley]

1. It sounds like you've just turned on viewing hidden/system objects.
Simply go into Tools, Options, and in the View tab, de-select Hidden Objects
and System Objects.

2. Did you just recently convert to Access 2002, by any chance? This is
normal behaviour in new databases, since DAO is no longer selected by
default, ADO is selected instead. Hit Alt-F11 to switch to the VBA window,
then click on Tools, References, and add in Microsoft DAO. You may want to
remove ADO as well; that's up to you.

3. Not sure. Probably best to post your code, though it could be related
to number 2. Fix that, and see if the dropdown starts working.


Rob

 

[Douglas J. Steele]

If all you do is add the DAO reference, you'll also need to disambiguate all
references to things such as DAO recordsets, fields, etc:

Dim R As DAO.Recordset

rather than simply

Dim R As Recordset

This is because Recordset is an object in both the DAO and ADO models, and
since the reference to ADO will be higher in the list than the one to DAO,
simply using Recordset will result in an ADO recordset (If you want to be
explicit, you'd use Dim R As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Robert Morley]

Woops, my oversight. I knew that, and meant to mention it, but it didn't
make it to my fingers.


Rob

 

[Aaron Kempf]

DAO hasn't been included with Office, Windows or MDAC for a decade

move to ADO kid

 

[Aaron Kempf]

I think that it's easier to just get rid of DAO

 

[Aaron Kempf]

DAO has been depecrated; you should be usign ActiveX Data Objects

 

[David Llewellyn]

Gentlemen, many thanks for your prompt and informative replies. I have a much
clearer view of the system now, particularly about avoiding conflicts between
DAO and ADO objects. Now, it works.

This leads to the question of Access 2002 defaults: can these be changed, so
that I get the config. I want when I start a new base?
--
David Llewellyn

Woops, my oversight. I knew that, and meant to mention it, but it didn't
make it to my fingers.


Rob

 

[Douglas J. Steele]

The only way that I'm aware of is to have an empty database set up how you
want it, and always create new databases by copying that one.

 

[Aaron Kempf]

stfu kid

DAO is obsolete; and it always will be!