By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Recordset problem with Access FE and SQL BE

P: n/a
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

Jan 3 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jan 3 '07 #2

P: n/a

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

Jan 3 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.