Type mismatch in 2007 but not in 2003 version

[Guest]

I have the following code in a vba code attached to a form:

Dim rst As Recordset
Dim dbs As Database
Dim whatif As String

Set dbs = CurrentDb
whatif = "select * from PersonalDNC where HomePhone = '" & ThisPhone & "'"
Set rst = dbs.OpenRecordset(whatif)


When I run this 2003 mdb in access 2007 this works. When I convert this to
a 2007 database and then run it, I get a "Type Mismatch" error. would I need
to put [ ] around HomePhone. PersonalDNC is a linked table that points to a
2003 mdb. Would I need to convert the linked mdb to a 2007 also. I do not
have 2007 but my client does. I am only able to test this on their site.

Thank you

Bob

 

[Allen Browne]

Bob, this is probably a matter of references.

The DAO library has a Recordset object, and so does the ADO library. If the
database uses both libraries, the Recordset will be derived from the one
listed first under Tools | References (from the code window.) You could
therefore get the wrong one, and so a type mismatch.

To solve the problem, be specific about the library you want:
Dim rst As DAO.Recordset

More info:
Solving problems with library references
at:
http://allenbrowne.com/ser-38.html

 

[Guest]

Thank you. I did change it to DAO.Recordset and it is working. It looks
like the order or default was cahnged between 2003 and 2007. Did I miss this
as a "Conversion issues"?

Bob, this is probably a matter of references.

The DAO library has a Recordset object, and so does the ADO library. If the
database uses both libraries, the Recordset will be derived from the one
listed first under Tools | References (from the code window.) You could
therefore get the wrong one, and so a type mismatch.

To solve the problem, be specific about the library you want:
Dim rst As DAO.Recordset

More info:
Solving problems with library references
at:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have the following code in a vba code attached to a form:

Dim rst As Recordset
Dim dbs As Database
Dim whatif As String

Set dbs = CurrentDb
whatif = "select * from PersonalDNC where HomePhone = '" & ThisPhone &
"'"
Set rst = dbs.OpenRecordset(whatif)


When I run this 2003 mdb in access 2007 this works. When I convert this
to
a 2007 database and then run it, I get a "Type Mismatch" error. would I
need
to put [ ] around HomePhone. PersonalDNC is a linked table that points to
a
2003 mdb. Would I need to convert the linked mdb to a 2007 also. I do
not
have 2007 but my client does. I am only able to test this on their site.

Thank you

Bob

 

[Douglas J. Steele]

I'd guess that the ADO reference had been removed from the Access 2003
application.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you. I did change it to DAO.Recordset and it is working. It looks
like the order or default was cahnged between 2003 and 2007. Did I miss
this
as a "Conversion issues"?

Bob, this is probably a matter of references.

The DAO library has a Recordset object, and so does the ADO library. If
the
database uses both libraries, the Recordset will be derived from the one
listed first under Tools | References (from the code window.) You could
therefore get the wrong one, and so a type mismatch.

To solve the problem, be specific about the library you want:
Dim rst As DAO.Recordset

More info:
Solving problems with library references
at:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have the following code in a vba code attached to a form:

Dim rst As Recordset
Dim dbs As Database
Dim whatif As String

Set dbs = CurrentDb
whatif = "select * from PersonalDNC where HomePhone = '" & ThisPhone
&
"'"
Set rst = dbs.OpenRecordset(whatif)


When I run this 2003 mdb in access 2007 this works. When I convert
this
to
a 2007 database and then run it, I get a "Type Mismatch" error. would
I
need
to put [ ] around HomePhone. PersonalDNC is a linked table that points
to
a
2003 mdb. Would I need to convert the linked mdb to a 2007 also. I do
not
have 2007 but my client does. I am only able to test this on their
site.

Thank you

Bob

 

[AnandaSim]

Thank you. I did change it to DAO.Recordset and it is working. It looks
like the order or default was cahnged between 2003 and 2007. Did I miss this
as a "Conversion issues"?

For years, Microsoft was forsaking DAO for ADO. DAO was Jet specific,
ADO encompassed more data sources. Then MS invented ADO.NET which is
their new data access model. They have moved on from ADO, it is no
longer their favourite. In the meantime, Access 2007 was given a new
gift of an enhanced DAO, so I notice DAO is now the default data
access model reference.

For years, we have been more explicit - we have been doing

Dim db as dao.database
Dim rst as dao.recordset

so that we explicitly force the issue regardless of the priority of
models.

HTH
Ananda

 

[Jamie Collins]

For years, Microsoft was forsaking DAO forADO. DAO was Jet specific,ADOencompassed more data sources. Then MS inventedADO.NET which is
their new data access model. They have moved on fromADO, it is no
longer their favourite. In the meantime, Access 2007 was given a new
gift of an enhanced DAO, so I notice DAO is now the default data
access model reference.

Another take: back in the mid- to late1990s, Jet was important to
Microsoft: Jet is 'owned' by the SQL Server team, Jet is included in
Microsoft's Universal Data Access movement via ADO, etc. The SQL
Server team put some great new features into Jet -- CHECK constraints,
CASCADE referential actions, etc -- but the Access team didn't match
the features in the Access user interface nor in DAO.

At the end of the 1990s, Microsoft's profession development tools
moved away from COM to be unified as .NET. Consider how successful a
brand ADO classic must have been to retain the name for ADO.NET even
when the A=ActiveX no longer applies. During the same period both ADO
classic and Access goes into 'maintenance only mode'. Microsoft can't
believe their luck: people continue to buy Access in significant
numbers.

Then, for the 2007 version, Access gets a number of enhancements,
including some new multi-value data types. What to do for programmers?
Changing ADO classic is probably not an option due to its continued
'maintenance only mode'; also, convincing its 'owner' that adding non-
scalar types is A Good Thing might be challenging (consider that the
SQL Server team would only permit these new types in a 'private copy'
of Jet). It's my guess that the choice of a new version of DAO
(ACEDAO) to receive the Access 2007 features was probably Hobson's.

When you say "DAO is now the default data access model reference" I
think you mean "the DAO reference now has a higher precedence" and
rightly so because the overwhelming majority of unqualified Recordset
etc instances in Access VBA code are intended to be DAO objects rather
than ADO objects.

Jamie.

--

 

[AnandaSim]

Another take: back in the mid- to late1990s, Jet was important to

Thanks for that take. Always nice to know the goss.

Microsoft: Jet is 'owned' by the SQL Server team, Jet is included in
Microsoft's Universal Data Access movement via ADO, etc. The SQL
Server team put some great new features into Jet -- CHECK constraints,
CASCADE referential actions, etc -- but the Access team didn't match
the features in the Access user interface nor in DAO.

Um, CASCADE ref integrity - wasn't that always there in the
Relationships Windows UI of Access from early days? Or are you
referring to something else?

classic and Access goes into 'maintenance only mode'. Microsoft can't
believe their luck: people continue to buy Access in significant
numbers.

I don't have many stats, just my perception, but Access was
"deprecated" by Microsoft and by IT Managers for a long, long time. It
was a raging success circa Access 2.0 but somewhere along the way, it
got hit by a lot of bad remarks and "big iron" despising the single
tier Jet mechanism compared to their other product - SQL Server.

Then, for the 2007 version, Access gets a number of enhancements,
including some new multi-value data types. What to do for programmers?

I think as a way to "be happy" with Sharepoint (multi-valued fields)
and to "be happy" with Excel 2007 (UI change etc..).

When you say "DAO is now the default data access model reference" I
think you mean "the DAO reference now has a higher precedence" and

Um, that wasn't what I meant. If you create a new blank Access file,
switch over to the VBA IDE, go Tools > References, you should see that
there is only one data access model specified. It was DAO in ACC97,
ADO in 2000, 2002, 2003 and DAO in 2007. You can opt to choose another
data access model or run both with a precedence order.

But by default, only one data access model reference is assigned as
default.

Yes?

Ananda

 

[Jamie Collins]

Um, CASCADE ref integrity - wasn't that always there in the
Relationships Windows UI of Access from early days? Or are you
referring to something else?

Maybe <g>. If so, substitue one of the many other omissions e.g. ON
DELETE SET NULL referential actions (interface), scale and precision
properties for the DECIMAL type (DAO), fixed width character types
(both), etc.

I think as a way to "be happy" with Sharepoint (multi-valued fields)
and to "be happy" with Excel 2007 (UI change etc..).

No one here seems to be admitting to having it near the top of their
wish list <g> nor does it seem to be popular now that it is a reality.
I've certainly no intention of using it myself but I wonder if we
should be pointing people to multi-value types when they ask "Is there
a Concatenate aggregate function...?" rather than giving them a VBA
function which violating First Normal Form using a scalar data type.
Any thoughts?

Um, that wasn't what I meant. If you create a new blank Access file,
switch over to the VBA IDE, go Tools > References, you should see that
there is only one data access model specified. It was DAO in ACC97,
ADO in 2000, 2002, 2003 and DAO in 2007. You can opt to choose another
data access model or run both with a precedence order.

But by default, only one data access model reference is assigned as
default.

Yes?

IIRC I got a reference to both ACEDAO and ADO (accdb file format) with
Access 2007 out of the box. I can't confirm right now because I'm in
the process of rebuilding my XP machine but I'll check back later. BTW
I hope you realized I wasn't trying to twist your words

Jamie.

--