Recordset problem with Access FE and SQL BE

[Bobby]

Hello,

I have an application which uses a SQL server 2003 Back end and an
Access 2003 front end.

I am trying to populate a recordset in the FE with data from an ODBC
linked table to the BE. Here is my code:

dim dbs as database, rstList as DAO.RecordSet, sqlString as string
set dbs = currentDB

sqlString = "Select field1, field2 FROM table WHERE field1 = 12345"
set rstList = dbs.openrecordset(sqlString, dbOpenDynaset)

I get an error which says "Runtime Error 4146 - ODBC Call failed". This
works fine if I use an Access BE.

I assume that the problem is to do with the DAO.recordset bit, and that
I'm not connecting correctly to the SQL server BE. Do I need to use a
connection string? Surely I've already connected to SQL when I opened
by application, so I don't understand why it should be a problem. Any
suggestions??

Thanks in advance

Colin

[Rick Brandt]

Bobby wrote:

Hello,

I have an application which uses a SQL server 2003 Back end and an
Access 2003 front end.

I am trying to populate a recordset in the FE with data from an ODBC
linked table to the BE. Here is my code:

dim dbs as database, rstList as DAO.RecordSet, sqlString as string
set dbs = currentDB

sqlString = "Select field1, field2 FROM table WHERE field1 = 12345"
set rstList = dbs.openrecordset(sqlString, dbOpenDynaset)

I get an error which says "Runtime Error 4146 - ODBC Call failed".
This works fine if I use an Access BE.

I assume that the problem is to do with the DAO.recordset bit, and
that I'm not connecting correctly to the SQL server BE. Do I need to
use a connection string? Surely I've already connected to SQL when I
opened by application, so I don't understand why it should be a
problem. Any suggestions??

Can you open the table link directly?
Can you edit data from the link?
Have you tried dbOpenSnapshot?

If the link works then I see nothing wrong with what you have there providing
the link allows edits. If not, then opening a dynaset type of Recordset might
not be allowed. I use code like that all the time.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bobby]

Rick Brandt wrote:

>
Can you open the table link directly?
Can you edit data from the link?
Have you tried dbOpenSnapshot?

If the link works then I see nothing wrong with what you have there providing
the link allows edits. If not, then opening a dynaset type of Recordset might
not be allowed. I use code like that all the time.

Thanks again Rick. You have indirectly guided me to the problem. I put
a debug in after the line sqlString = "Select field1, field2 FROM
table WHERE field1 = 12345", and then got the value of sqlString from
the immediate window.

I copied the result into a new query and tried running it. This gave me
the following error:

"[Microsoft][ODBC SQL Server Driver][SQL Server] The text, ntext, and
image data types cannot be compared or sorted except when using IS NULL
or LIKE operator. (#306)"

On investigating, it turns out that field1 is a memo field, so I can't
say "...WHERE field1 = 12345". The following simple change now works:

sqlString = "Select field1, field2 FROM table WHERE field1 like 12345"

Thanks again for your help,

Colin