Problems running Access 2003 app in Access 2007 environment

[polaris]

Hello all...

I wrote an Access 2003 app that's been running OK for years. Now I'm trying to run it in our new server (Windows Server 2008) which has Access 2007. The app seems to run fine, except that when the VB code tries to run a query (using SQL syntax) I'll get error messages ("Type Mismatch", or it will ask for a field that's already there). Oddly, I cut-and-pasted the SQL line from the code, and created a new query with it, and the query runs just fine! So the problem is specific to running the query from VB... :-\

The code goes something like this (I simplified the SQL line a lot):

Dim qry As Recordset, sql As String

sql = "SELECT tblPremises.Licensees, tblMunicipalities.MunicipalityName, tblReps.Sales_rep_desc WHERE (((tblReps.Sales_rep_desc)=" & Chr(34) & Forms!frmPremiseSelection!cmbRep & Chr(34) & "));"

Set qry = CurrentDb().OpenRecordset(sql) <== Error #13 (Type Mismatch) occurs here

As I said, If I cut and paste the sql line and create a query, runs fine... So it's not a problem w/ the SQL syntax, or any "true" type mismatch....

I checked the VB References and I have checked all the same ones I have checked in Access 2003... What could be causing the queries to fail if ran them from VB in Access 2007...?? Please help...!

 

[boblarson]

Change this:
Dim qry As Recordset

to this:
Dim qry As DAO.Recordset

It is that non explicit reference to a recordset (because it could be an ADO or DAO recordset) which is giving you the error because the ADO reference is probably higher than the DAO reference. It is best to be explicit in your declarations.

 

[TimW]

I dont know if Bob solved your problem but i would write the sql like this.
Dim qry As Recordset, sql As String

sql = "SELECT tblPremises.Licensees, tblMunicipalities.MunicipalityName, tblReps.Sales_rep_desc WHERE (((tblReps.Sales_rep_desc)='" & Forms!frmPremiseSelection!cmbRep & "'));"
Assuming that the data you are looking for is a string!

Just a guess...
Merry Christmas

 

[polaris]

Thank you so much for your reply, Bob and Tim... Apologies for not having replied earlier... I became swamped at work in December and early January and I had to postpone looking into this issue... I'll try Bob's suggestion shortly.

Tim, yes, it's looking for a string. I just got used to do the " & chr(34) & " to add the quotes, good to know there's another quicker way.

Cheers to both!

Paul

 

[boblarson]

I just got used to do the " & chr(34) & " to add the quotes, good to know there's another quicker way.

I almost always use the Chr(34) method over the single quotes. The reason? There are many times when the data will have a single quote in it (like Bill's Plumbing, Ed's Cleaners, etc.) so that way you don't have a problem. It is rare for data to have double quotes.