ODBC Call Failed

[Jimbo]

I have a form in access 97 that is populated by a query against some
sql server
tables....when ever the query pulls up only one record im fine..however
if it
pulls up more than one record i have to make sure i click the
the navigation arrows through all the records or else I get an odbc
error and all the fields go to #Name? If I click the arrows and scroll
through all the records Im ok..

What is causing this ?

Thanks

[Bri]

Jimbo wrote:

I have a form in access 97 that is populated by a query against some
sql server
tables....when ever the query pulls up only one record im fine..however
if it
pulls up more than one record i have to make sure i click the
the navigation arrows through all the records or else I get an odbc
error and all the fields go to #Name? If I click the arrows and scroll
through all the records Im ok..

What is causing this ?

Thanks

Jimbo,

This could be several things and without some additional info it will be
hard to track down. Some questions:
- After the 'ODBC Call Failed' error is there a second error message? If
so, this will be more specific and more useful for us to know.
- Do your SQL tables have a TimeStamp field and is it included in the
source query to the Form? Without this field, Access can have trouble
tracking edits and locks to this record.
- Is the PK of the SQL table one one field or more than one field. I
have seen problems whith multiple field PKs where one is an Identity
field. If so, then change the PK to be only the Identity field.
- Is the Form source query joining multiple tables?
- Is the query based on Linked ODBC tables or something else?
- Some more detail on the tables and the query itself might shed some
light on the problem.

--Bri

[Jimbo]

- After the 'ODBC Call Failed' error is there a second error message?
no

Q. Do your SQL tables have a TimeStamp field and is it included in the
source query to the Form? Without this field, Access can have trouble
tracking edits and locks to this record.
A. no, but I do have a trigger on one of the underlying sql server
table to track whenver the record has been edited

Q. Is the PK of the SQL table one one field or more than one field.
A. All tables involved have one primary key

Q. Is the Form source query joining multiple tables?
A. Yes..

Q.-Is the query based on Linked ODBC tables or something else?
A. Yes...Sql Server tables
The Query:
SELECT DISTINCTROW evnt_dtl.DetailID, events.[Event type],
events.[Event date], events.[Client ID], evnt_dtl.[Problem ID],
probl_LU.[Problem name], evnt_dtl.[New client status],
evnt_dtl.[Clinical action], evnt_dtl.[Reason for action],
evnt_dtl.[Medication change], evnt_dtl.[Problem-specific comments],
events.[IntervalNote?]
FROM ProblemCategories INNER JOIN ((clients INNER JOIN events ON
clients.[Client ID] = events.[Client ID]) INNER JOIN (probl_LU INNER
JOIN evnt_dtl ON probl_LU.[Problem ID] = evnt_dtl.[Problem ID]) ON
events.[Event ID] = evnt_dtl.[Event ID]) ON
ProblemCategories.CategoryName = probl_LU.[Problem category]
WHERE (((evnt_dtl.[Event ID])=[Forms]![LogIn]![ActiveEventID]))
ORDER BY ProblemCategories.ProblemCategoryID;

Let me know if you need any more info

[Jimbo]

I just noticed something else while playing around here...If I go to
the underlying query and try to run it I get an odbc error....

If I remove these fields from the query:
evnt_dtl.[New client status]
evnt_dtl.[Clinical action]
evnt_dtl.[Reason for action]
evnt_dtl.[Medication change]

It works.....These fields dont seem out of the ordinary though
Heres the definition of the evnt_dtl table from SQL Server:

CREATE TABLE [dbo].[evnt_dtl] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[Event ID] [int] NULL ,
[Problem ID] [int] NULL ,
[New client status] [int] NULL ,
[Clinical action] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Reason for action] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Medication change] [int] NULL ,
[Problem-specific comments] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastModified] [smalldatetime] NULL ,
[LastModTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[LastModStaffID] [int] NULL
) ON [PRIMARY]
GO

[Jimbo]

Ok..heres an update to this...I changed the other text fields to
varchar(8000)...so now access sees them as memo fields...the underlying
query now works without any problems..however the form still sits for
about 10 seconds or so and then everything goes to #Name? if I dont
scroll through the records...really weird

[Bri]

Jimbo wrote:

- After the 'ODBC Call Failed' error is there a second error message?
no
This is unusual, but not impossible. It means that SQL isn't passing the
error it has back to the caller (IE Access).
Q. Do your SQL tables have a TimeStamp field and is it included in the
source query to the Form? Without this field, Access can have trouble
tracking edits and locks to this record.
A. no, but I do have a trigger on one of the underlying sql server
table to track whenver the record has been edited
This might be it. The Timestamp field is very important to Access. I
would recommend adding one in to each of the tables. If this doesn't fix
it then try disabling the Trigger (comment out the code so you can
reactive it if necessary later). The issue here is that the Trigger
might well be letting SQL know that the record is being edited, but what
is telling Access that someone else hasn't edited it while you have been
working on the record.
The Query:
SELECT DISTINCTROW evnt_dtl.DetailID, events.[Event type],
events.[Event date], events.[Client ID], evnt_dtl.[Problem ID],
probl_LU.[Problem name], evnt_dtl.[New client status],
evnt_dtl.[Clinical action], evnt_dtl.[Reason for action],
evnt_dtl.[Medication change], evnt_dtl.[Problem-specific comments],
events.[IntervalNote?]
FROM ProblemCategories INNER JOIN ((clients INNER JOIN events ON
clients.[Client ID] = events.[Client ID]) INNER JOIN (probl_LU INNER
JOIN evnt_dtl ON probl_LU.[Problem ID] = evnt_dtl.[Problem ID]) ON
events.[Event ID] = evnt_dtl.[Event ID]) ON
ProblemCategories.CategoryName = probl_LU.[Problem category]
WHERE (((evnt_dtl.[Event ID])=[Forms]![LogIn]![ActiveEventID]))
ORDER BY ProblemCategories.ProblemCategoryID;
The DISTINCTROW predicate is not supported by SQL Server only the
DISTINCT predicate. Usually, this isn't a problem if you are running an
Access query against SQL. It will try to pass as much of the work to the
server as it can. In queries with multiple joins it will possibly decide
that the server can't do it so it grabs all the data locally and does
the query locally. The DISTINCTROW will also have to be processed
locally. At the least this will cause this query to be extremely slow
and might make it uneditable.

Hmm, new questions. Do you need to edit this or are you just displaying
the results? Do you need DISTINCTROW over DISTINCT? If you can get away
with DISTINCT and don't need to edit it, then I would convert this query
to a Passthrough Query. That would guarentee that it is processed by the
server

Jimbo wrote: I just noticed something else while playing around here...If I go to
the underlying query and try to run it I get an odbc error....
With any additional info or just the generic error?
If I remove these fields from the query:
evnt_dtl.[New client status]
evnt_dtl.[Clinical action]
evnt_dtl.[Reason for action]
evnt_dtl.[Medication change]

It works.....These fields dont seem out of the ordinary though
Heres the definition of the evnt_dtl table from SQL Server:

CREATE TABLE [dbo].[evnt_dtl] ( [Problem-specific comments] [varchar] (8000) COLLATE
While all of the ramblings above might still be of use, this is likely
the cause of your problem. You should use the Text data type for
character data over 255 characters. Access' text data type (VarChar
equivelent) is capped at 255. Access uses Memo data type for larger than
that. The Text data type is the equivelent of the Memo data type.
[LastModified] [smalldatetime] NULL ,
This is also a problem. Access does not have an equivelent to
smalldatetime, change this to datetime.
[LastModTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
Why is this field typed for Unicode when all the others are not? This
isn't a problem just an inconsistancy.

Jimbo wrote: Ok..heres an update to this...I changed the other text fields to
varchar(8000)...so now access sees them as memo fields...the underlying
query now works without any problems..however the form still sits for
about 10 seconds or so and then everything goes to #Name? if I dont
scroll through the records...really weird

As mentioned above, the actual Memo equivelent is Text. I would use that
rather than varchar(8000). The speed issue I delt with above. The
multiple joins and the DISTINCTROW are forcing Access to bring over ALL
of the data in ALL of the tables in the query across the network to be
processed locally.

OK, some more thoughts on the query. If you need to be able to edit
this, then your only option (that I can see) is to create a view on the
server with this query. Include whatever fields are necessary to make
DISTINCT return the equivelent rows as DISTINCTROW. Be sure to include
the Field(s) that would define a unique record. You will need these not
only to make the DISTINCT work, but you will need to select them when
you link to the view from Access (linking to a view prompts you to
select the Unique Field(s)).

Let us know if any of this solved your problems so we can all learn from
this.

HTH
--
Bri

[Jimbo]

Ok I tried everything you suggested and yes...I do need to edit this..
The only thing I havent tried is the timestamp thing...how can I
implement this in an access97 front end sql server backend environment?

[Bri]

Jimbo wrote:

Ok I tried everything you suggested and yes...I do need to edit this..
The only thing I havent tried is the timestamp thing...how can I
implement this in an access97 front end sql server backend environment?

Add a new field to the table, give it a name, pick the type TimeStamp.
Include the Timestamp fields in the query, do not assign them to form
controls.

I think that you might be better off with the View in SQL to speed up
the query. As mentioned the DISTINCTROW and the multiple joins mean that
Access will likely try to bring over the entire tables to do the
processing locally. If there are significant number of records involved
this can be wxtremely slow. I had one query that took almost five
minutes as an Access query that takes less than a second as a view.

Did you change the varchar(8000) to Text and the smalldatetime to
datetime? That is the likely cause of your NAME# problem.

--
Bri

[Jimbo]

I did change it but it didnt help..I did a work around..when the form
first opens I have it automatically run through the entire record set
and then go to the first record...its totally transparent for the user
and the #Name? thing disappeared....