Open a table locally in Access

  • Thread starter Thread starter Patrick Dixon
  • Start date Start date
P

Patrick Dixon

It appears to me that Access 2002 has a defect. I am
trying to do about the simplest thing, open an existing
table from a local VBA module. In other words, I have an
Access mdb file with a table "test" and a
module "testmod". All I want to do is open the table and
see what's in it. I have years of VBA/Access exprience
and have used this more times than I can count.

I am getting a "type mismatch" error on the "set test
= .. " statement. The code is below. Can someone tell me
what I am doing wrong, or tell me who at MicroSoft I can
complain to?

Sub testmod()

Dim test As Recordset

Set test = CurrentDb.OpenRecordset("txall_copy")

While Not test.EOF
MsgBox ("Member " & test.Fields(1).Value & "")
test.MoveNext
Wend
End Sub
 
Patrick Dixon said:
It appears to me that Access 2002 has a defect. I am
trying to do about the simplest thing, open an existing
table from a local VBA module. In other words, I have an
Access mdb file with a table "test" and a
module "testmod". All I want to do is open the table and
see what's in it. I have years of VBA/Access exprience
and have used this more times than I can count.

I am getting a "type mismatch" error on the "set test
= .. " statement. The code is below. Can someone tell me
what I am doing wrong, or tell me who at MicroSoft I can
complain to?

Sub testmod()

Dim test As Recordset

Set test = CurrentDb.OpenRecordset("txall_copy")

While Not test.EOF
MsgBox ("Member " & test.Fields(1).Value & "")
test.MoveNext
Wend
End Sub
Click to expand...

Make sure you have a reference set to the Micrtosoft DAO 3.6 Object
Library (this reference is not set by default with Access 2002), and
then declare your recordset as

Dim test As DAO.Recordset

to avoid confusion with the ADO Recordset object.
 
Thanks for trying, but this time I get "User-defined type
not defined" on the DIM statement. Here is the code I
modified per your recommendation. Let me know if you have
another idea.

Sub testmod()

Dim test As dao.Recordset

Set test = CurrentDb.OpenRecordset("txall_copy")

While Not test.EOF
MsgBox ("Member " & test.Fields(1).Value & "")
test.MoveNext
Wend
End Sub
 
Patrick Dixon said:
Thanks for trying, but this time I get "User-defined type
not defined" on the DIM statement. Here is the code I
modified per your recommendation. Let me know if you have
another idea.

Sub testmod()

Dim test As dao.Recordset

Set test = CurrentDb.OpenRecordset("txall_copy")

While Not test.EOF
MsgBox ("Member " & test.Fields(1).Value & "")
test.MoveNext
Wend
End Sub
Click to expand...

I think you didn't add the reference to Microsoft DAO 3.6 Object Library
that I told you about. While in the VBA Editor environment, click
Tools -> References..., locate the reference in the list, and put a
check mark in the box next to it.
 
That did the trick, thanks. I suppose I can wonder why
this isn't selected by default; I never had to do this
before, and I've done a whole lot of VBA/Access work over
the years.

Patrick Dixon
 
Patrick Dixon said:
That did the trick, thanks. I suppose I can wonder why
this isn't selected by default; I never had to do this
before, and I've done a whole lot of VBA/Access work over
the years.
Click to expand...

Up until Access 2000, the reference was set by default. When MS came
out with Access 2000, they were pushing ADO as the new access method for
all types of databases, so they made that the default -- despite the
fact that DAO is more powerful and more efficient for Jet databases. I
think they have restored the default DAO reference in Access 2003, but I
don't know because I haven't installed that version yet.
 
FYI

Yes, in Access 2003 the DAO is automatically selected, but I was in the same boat as Patrick, until I read these messages. I too have been programming in Access for years and was stunned when my OpenRecordset statement generated this error! By the way, the Help documentation continues to provide old code that no longer runs. Are there any release note that can help those of us who have been programming in previous versions of Access

Thanks

Christine
 
Back
Top