ODBC Call Failed - SQL Server db 1

[JeffTullin]

I have distributed an MDE file that reads and writes to an SQL Server database using an ODBC DSN.

Recently, a user reports that while SQL Svr is updating a table I need to work on, operations in the MDE fail.
My error handlers fire up and report 'ODBC - Call Failed'

I suspect this is down to locking issues - is there a way to make Access wait? (I found otions to change number of retries from 2 to 10, and options for refresh interval, but these don't seem to help)

Otherwise, in general terms, has anyone else encountered this kind of problem/have any advice?

Jeff

 

[rookery]

Jeff I'm far from an expert on this issue but have you trawled thru' the myriad of other simialr posts on this site. You may find an answer in there.

Moreover, I've been experiencing the dame problem just recently and in my experience this is a very general error message. I'm sure I read somewhere that it was a connectivity issue but that is not ALWAYS the case.

It could be down to an ODBC timeout in the query. Set this to 0 from the default 60 and this may help. Additionally it could be that you're trying to put the wrong datatype into a field or the field doesn't accept Nulls when trying to update. Also remember that SQL tables add the Autonumber (if you're using the facility) only once you update; the opposite is true of Access where the Autonumber is generated as soon as you enter the new record.

Good luck.

 

[bboffin]

This is a common problem when using ODBC to access SQL Server tables with Access.

We have gradually eliminated the problem by using SQL Stored Procedures whenever we need to do updates and inserts. Within the SP the update is performed within a transaction.

You may also be unaware of the way in which SQL Server escalates its locks over a period of time. It will start with a page level lock and then move to a table level lock if the lock is not resolved quickly and eventually to a full database lock. See the SQL docs for a better explanation.

We also found that updating or inserting data by form can produce unexpected locks. For example if you start to modify a row in a detail section on the screen then that row gets locked until you commit the change. It's very easy to switch away to another application and leave the row uncommitted. This may quickly lead to an ODBC error as SQL's locking gets escalated.

You can see what locks are in place using Enterprise Manager and looking at the Management\Current Activity folder for the required Server. This should tell you which tables are causing the locks and if you include the APP parameter in the ODBC Connection string (use a different one for each application) you can tell which application is causing the lock as well.

Hope that helps.

 

[JeffTullin]

Lots to chew over there, many thanks.

 

[faeryfyrre]

Hi,
I had this same problem today.

I have very recently had my Access 97 database backend upsized to SQL Server 7. During the process my old Primary Key (PK) fields whose datatype was Autonumber were imported into SQL as normal number datatypes, while still maintaining the 'Required' property.

I was getting the 'ODBC -- call failed' error because i wasn't assigning a value to my PK field - expecting SQL Server to assign these values for me - but instead the query was attempting to assign a NULL to a 'Required' field. Once i'd worked this out I wailed on the DBA who did the conversion for not maintaining my table design during conversion. He fixed all my Autonumber fields and now, All is now good in the Universe.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"