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.openrecords et(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 2 2497
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.openrecords et(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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: MNC |
last post by:
I'm using Access2002, and can't seem to get an updateable recordset going
:-(
What am I doing wrong, here's the code. The form's controls are not locked,
the recordset type is Dynaset (changing to Dynaset inconsistent updates does
not work), I'm allowing edits, and I'm at a loss ...
Please help!
Option Compare Database
|
by: Gerry Abbott |
last post by:
Hi all,
I having some confusing effects with recordsets in a recent project.
I created several recordsets, each set with the same number of records, and
related with an index value.
I create a table and add the index value and a value/s from each recordset
in turn, into a temporary table, which I used to create a report. I created
this...
|
by: j.mandala |
last post by:
Someone is trying to run my Access 2002 database under Access 2003. He
has had a number of problems:
1) i used the MSComCt2.ocx for it's Date and Time picker. I can't find
it under 2003. Do I need to send it to them?
2) I have a function to fill a table with values, that store the page
and column numbers of a display of staff members. I...
|
by: alex_peri |
last post by:
Hello All,
I am having problems with sorting a recordset by fields in Access. I
have a table with three columns called ID, SNo and Time and would like
to sort the records by Time. I would like to sort them in the actual
database and not just Order them with SQL. I have included the code I
wrote below. Please bear in mind that I am very new to...
|
by: corepaul |
last post by:
I am fairly new to Access and I have a problem trying to use bookmarks with
a recordset.
I have a recordset dimensioned as,
Dim rstFoodDesc As ADODB.Recordset ' recordset
Dim bMark As Variant ' variable to store bookmark
I open the recordset with the statements,
| |
by: sneal |
last post by:
A little background: We have an Access 2002 based user interface to our
SQL Server 2000 database. The interface is a tabbed form with two of
the tabs containing a subform. Data is pulled from the SQL Server
using several stored procedures, one sproc for the main form and an
additional sproc for each subform. All data is pulled from the SQL...
|
by: kjvt |
last post by:
Based on a prior posting, I've written a function to convert a
recordset to a dataview. The first call to the function for a given
recordset works perfectly, but the second call always returns a
dataview with a count = 0. Can someone explain why and how I might
work around this problem?
Here is the code for my function:
Public Shared...
|
by: PW |
last post by:
Hi,
I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset open
and a database open isn't a good idea, right?
Thanks,
-paul w
|
by: Yarik |
last post by:
Hello,
Here is a sample (and very simple) code that binds an Access 2003 form
to a fabricated ADO recordset:
' Create recordset...
Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset
' Append one or more fields...
Call rs.Fields.Append("Number", adInteger)
|
by: Oko |
last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access
2002.
One of the reports within the DB uses data that is Dynamic and cannot
be stored on the SQL Server. To resolve this, I have created an
ADODB.Recordset in the reports OPEN event, built the necessary records
inside of it, and then bound the report to this newly...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |