Type Mismatch

[Scott Burke]

dim db as database
dim rs as recordset
set db = currentdb()
set rs = db.openrecordset("Import UMC")

The error happens here. "set rs = db.openrecordset("Import UMC")"
The "Import UMC" table exist.
It is empty.

Why is this a Type Mismatch (Run Time Error 13) ???????

Scott Burke

 

[John Spencer]

I would guess it is the space in the name.

Try
set rs = db.openrecordset("[Import UMC]")

 

[Roger Carlson]

I looks to me as if you do not have a Reference set to DAO. (If you don't
know what this means, look here:
http://www.rogersaccesslibrary.com/References.htm. If you *do* have a
reference set, then explicitly state the object source:

dim db as DAO.database
dim rs as DAO.recordset


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ofer]

Try to fefine the Dao first

dim db as Dao.database
dim rs as Dao.recordset
set db = currentdb()
set rs = db.openrecordset("Import UMC")

 

[Scott Burke]

OK... I have written many of code that does NOT have the DAO. prefix.
But in this case adding the DAO. makes it work.

My guess is that a Reference is set in the other programs but that reference
is not set in this program??

Have the default "Active" reference changed since Access97?

Scott Burke

Hey John. Your ideal did not work. Access tried to use the "[]" as part of
the table name.

 

[Roger Carlson]

More likely the ONLY reference set in the other programs is DAO. Whereas
you probably have a reference set to BOTH DAO and ADO in this program. The
confusing thing is that both DAO and ADO have a Recordset object. If your
ADO reference comes first in the list, a recordset declared without a prefix
will become an ADO recordset. But then you try to stuff a DAO recordset
into it, hence the Type-Mismatch.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

OK... I have written many of code that does NOT have the DAO. prefix.
But in this case adding the DAO. makes it work.

My guess is that a Reference is set in the other programs but that reference
is not set in this program??

Have the default "Active" reference changed since Access97?

Scott Burke

Hey John. Your ideal did not work. Access tried to use the "[]" as part of
the table name.

Try to fefine the Dao first

dim db as Dao.database
dim rs as Dao.recordset
set db = currentdb()
set rs = db.openrecordset("Import UMC")

 

[Scott Burke]

Thanks Roger. I will have to lookout for that.

Scott Burke

More likely the ONLY reference set in the other programs is DAO. Whereas
you probably have a reference set to BOTH DAO and ADO in this program. The
confusing thing is that both DAO and ADO have a Recordset object. If your
ADO reference comes first in the list, a recordset declared without a prefix
will become an ADO recordset. But then you try to stuff a DAO recordset
into it, hence the Type-Mismatch.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

OK... I have written many of code that does NOT have the DAO. prefix.
But in this case adding the DAO. makes it work.

My guess is that a Reference is set in the other programs but that reference
is not set in this program??

Have the default "Active" reference changed since Access97?

Scott Burke

Hey John. Your ideal did not work. Access tried to use the "[]" as part of
the table name.

Try to fefine the Dao first

dim db as Dao.database
dim rs as Dao.recordset
set db = currentdb()
set rs = db.openrecordset("Import UMC")

--
\\// Live Long and Prosper \\//
BS"D


:

dim db as database
dim rs as recordset
set db = currentdb()
set rs = db.openrecordset("Import UMC")

The error happens here. "set rs = db.openrecordset("Import UMC")"
The "Import UMC" table exist.
It is empty.

Why is this a Type Mismatch (Run Time Error 13) ???????

Scott Burke

 

[Douglas J. Steele]

Have the default "Active" reference changed since Access97?

Yes they have. Allen Browne details what they are at the bottom of
http://www.allenbrowne.com/ser-38.html

However, he neglects to point out that neither Access 2000 or Access 2002
set the reference to DAO by default (and, of course, Access 97 didn't have a
reference to ADO). In Access 2003, the reference to ADO is higher in the
sequence than the reference to DAO, so it takes precedence if you don't
disambiguate.

 

[Scott Burke]

So higher up the list the Reference the more important it is.
That sounds like the answear.
We had three different programers here. I willing to bet that the
reference order is different in this program than what I am use to.

Scott Burke

 

[Douglas J Steele]

While reference order will usually solve the problem, the real answer is
disambiguation.

Rather than Dim rs As Recordset, be explicit: Dim rs As DAO.Recordset or Dim
rs As ADODB.Recordset.

 

[Scott Burke]

I agree.
What happen was this. I have a program called "Tree Pad". It is
a digital notebook. When I write or find a function that I like. I copy it
to "Tree pad". I got functions going back to Access97. All the fuctions in
"Tree Pad" worked. Well I copied a function from "Tree Pad" into program
X.MDB. I did not write X.MDB but I am making changes. The code did not
work. That was the reason for this thread. I did not understand why code
that worked before would not work now.

Now I know.
Thank you everyone, for your help.
Scott Burke