Member or Data Member not Found

B

Bill Barber

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
 
A

Allen Browne

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*"
 
B

Bill Barber

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!
 
D

Douglas J Steele

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
 
B

Bill Barber

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
 
D

Douglas J Steele

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)
 
B

Bill Barber

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
 
D

Douglas J Steele

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

Marshall Barton

Bill said:
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.


I think you meantL

Dim tdf As TableDef
 
B

Bill Barber

Marshall;
Been fighting with this all day on four different machines.

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

Set db = CurrentDb

For Each tdf In .TableDefs ' Error line

cnt = cnt + 1
MsgBox cnt

Next tdf

Invalid or unqualified Reference is still the message.

Do you have any more ideas?

Thanks Again

Bill
 
D

Douglas J. Steele

I missed the fact that you DID say where the error was occurring. Obviously
you've removed the reference to ADO.

Marsh noticed what I missed: your declaration for tdf is incorrect.
 
D

Douglas J. Steele

Bill: Are you copying-and-pasting your code, or are you retyping it into
your questions?

You're back to not having With db... End With in this code.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top