Re: Member or Data Member not Found

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Why did you remove the DAO. qualifiers?

You don't say, but I'm assuming that it's dying on Set rst =
db.OpenRecordset("SELECT * FROM HrsRate")

Microsoft introduced a new data access method in the late 90s known as ADO.
Access 97 and previous only had DAO in them. By default, Access 2000 and
2002 only references ADO, but you can add a reference to DAO. (Microsoft
came back to their senses with Access 2003, and both references are there by
default).

While DAO and ADO are different, they happen to both have a Recordset object
in them. When you don't qualify the declaration, Access loops through all of
the references in the application and takes the first one it finds.
Unfortunately, ADO is higher in the list of references than DAO, so it
assumes an ADO recordset. You're trying to use DAO, therefore you get a type
mismatch. You must disambiguate as Dim rst As DAO.Recordset. (To guarantee
an ADO recordset, you'd use Dim rst As ADODB.Recordset). The complete list
of objects which exist in both the DAO and ADO models is Connection, Error,
Errors, Field, Fields, Parameter, Parameters, Property, Properties and
Recordset.

Realistically, we've been spoiled in Access by not having to explicitly type
everything. It's always a good idea to be explicit.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bill Barber" <BillBarber@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:82D933EC-BB30-4D49-AB09-60F1BE762593@xxxxxxxxxxxxxxxx
Douglas;
This has been a lot tougher than it should be! I changed the
code and now I get a Type mismatch!

Dim db As Database
Dim rst As Recordset
Dim tdf As TableDefs

Set db = CurrentDb

With db
For Each tdf In .TableDefs ' Type mismatch on this line.


Next tdf
End With

What in the world did Microsoft change? This was working for the last 4
years?

Thanks Again

Bill


"Douglas J Steele" wrote:

And now my turn to apologize. In concentrating on the missing With db...
End
With construct, I missed the fact that you're using the object name,
..TableDef, when it needs to be the collection name, .TableDefs (s at
the
end)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bill Barber" <BillBarber@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C87BA6A-BFFD-4708-804E-A70A47CE4BEB@xxxxxxxxxxxxxxxx
Douglas;

Sorry I goofed, I do have the With DB and End With in my
code.
I just forgot to type it in with my code example.

Thanks

Bill

"Douglas J Steele" wrote:

You can only use .TableDef in conjunction with a With statement that
defines
a database object.

You need

Set rst = db.OpenRecordset("SELECT * FROM HrsRate")

With db
For Each tdf In .TableDef

Next tdf
End With



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bill Barber" <BillBarber@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:4F11E192-F7E9-4709-99B8-16D176F904D0@xxxxxxxxxxxxxxxx
Allen;
Thanks for assisting me!

Management made us reinstall Access 2000, just too many
problems
upgrading! The DAO 3.6 is the Reference being used.

I am trying to isolate the problem, but I am having trouble
understanding why it fails so differently going from computer to
computer.

I included the update of where I am at this minute.

Now I get invalid or unqualified Reference on the .TableDef
below!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As Field

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM HrsRate")

For Each tdf In .TableDef




Next tdf

Any more ideas? And, thanks for the help!

"Allen Browne" wrote:

Choose References from the Tools menu. You already have a
reference
to
the
DAO library (else the code would fail on the first line), but
check
that
it
is DAO 3.6. Referencing an old 2.5/3.5 compatibility library
sometimes
worked in 2000 and failed on update to A2002.

It could also be a Name AutoCorrect error. Make sure these check
boxes
are
unchecked under:
Tools | Options | General
For an explanation of why:
http://allenbrowne.com/bug-03.html

Whether or not you reset the Name AutoCorrupt boxes,
compact/repair
the
database:
Tools | Database Utilities

If the issue remains, we need to determine whether the failure
is
tdf.Name
or the call to Left() - possibly a naming clash. To determine
this,
try:
Debug.Print tdf.Name
or replace the line with:
If tdf.Name Like "AlphaErrors*"

--
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.

"Bill Barber" <BillBarber@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:BC2E11CD-278F-4656-832F-4E79F5F7AE92@xxxxxxxxxxxxxxxx
I updated from Access 2000 to 2002 and now I get this error on
something
that
has been working for years.

Here is the code!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As TableDefs

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM
AlphaErrors")

With db
For Each tdf In .TableDefs
If Left(tdf.Name, 11) = "AlphaErrors" Then
'
Error
Here Member or Data Member not Found On tdf.Name!
MsgBox "Found Error Table"
End If
Next tdf
End With

Does anyone know what I have to change to get this to work?

Thanks a Lot

Bill











.

Relevant Pages

  • Re: Opening a SQL Server table from within MS Access
    ... Dim rst As ADODB.Recordset ... Depending on what you're going to do with the recordset, ... Unlike a DAO recordset, the default for an ADO recordset ... >I created a Microsoft Data Access Project in MS Access V11. ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset not recognising .FindFirst
    ... Library) and DAO (Microsoft DAO 3.6 Object Libary) object models. ... Dim rst As DAO.Recordset ... with the object to be sure you were getting an ADO recordset, ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset not recognising .FindFirst
    ... Recordset is an object in both the ADO (Microsoft ActiveX Data Objects 2.1 ... Library) and DAO (Microsoft DAO 3.6 Object Libary) object models. ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset method
    ... Database is a DAO object. ... set a reference to Microsoft DAO 3.6 Object Library, ... "Dim dbs As Database" would have raised an error, but when you did so, you ...
    (comp.databases.ms-access)
  • Re: Defining Prodecures
    ... Just a guess, John, but what if you were to explicitly assign RecordsetClone ... Dim rst As DAO.Recordset ... "Microsoft DAO 3.6 Object Library" ...
    (microsoft.public.access.formscoding)