Re: access97 & .copyFromRecordset different when using sql server 2005

the sql data type for this field is 'int'
and yes I use ODBC to link to the table from within access
and if I use the string syntax like '999', I get an error "Data type
mismatch in criteria expression."

and if I create a new query, SELECT * FROM qryIPS WHERE location =
999, it works fine

but CopyFromRecordset won't work, even though the recordCount below
has the correct number of records
strSql = "SELECT * FROM qryIPS WHERE location = " &
intLocation(intBook)
Set rst = CurrentDb.OpenRecordset(strSql)
If (Not (rst.EOF)) Then
rst.MoveLast
lngRecCount = rst.RecordCount
rst.MoveFirst
Else
lngRecCount = 0
End If

If (lngRecCount < 1) Then
MsgBox "No data found for this work***"
End If

.range("A2").CopyFromRecordset rst


unless I use 'like' instead of '='



On Feb 4, 7:38 pm, Tom van Stiphout <no.spam.tom7...@xxxxxxx> wrote:
On Mon, 4 Feb 2008 09:39:34 -0800 (PST), Roger

<lesperan...@xxxxxxxxxx> wrote:

Like only works with text fields, so I have to assume "location" is a
text field. Check the data type in SqlServer, not in Access.
Btw, the correct syntax would be:
SELECT * FROM qryIPS WHERE location like '999'
But strangely you're not using wildcards, so the above should be equal
to:
SELECT * FROM qryIPS WHERE location = '999'

When you say "linked table to sql server2005", what middleware are you
using? Odbc?

-Tom.



can anyone explain this ?
originally using access97 with a linked table to an mdb backend, to
create a work*** using
        SELECT * FROM qryIPS WHERE location = 999
and it works fine with either excel97 or excel2003

moved backend mdb tables to sql server2005 and relinked
location field in both cases is a long integer

but the above statement no longer works with either version of excel
when doing
     .range("A2").CopyFromRecordset rst

but SELECT * FROM qryIPS WHERE location like 999, works fine

I don't under why '=' doesn't work ? and 'like' does ?

note, changing the query qryIPS to include the where clause "location
= 999" and just using
  select * from qryIPS, doesn't work either, yet the query gives me
that information I need- Hide quoted text -

- Show quoted text -

.