Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-04-2017, 11:45 AM   #1
Geirr
Newly Registered User
 
Join Date: Apr 2012
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Geirr is on a distinguished road
ODCB Call Failed - Run T Error 21472117887 (80040e21)

Hi All.

I've run into a problem which I need helt to solve:
Run-Time error '-2147217887 (80040e21)
ODBC--call failed.

Background: Using Access 2016 (FE), SQL Server 2012. (BE)
Linked tables, and using 'dsnless' hardcoded connection, with SQL Authentication.

The error message occur on table update with the .update fuction, and the debuger points out the line with .update as the problem in program.
But it seems that this occur on nested level, like: (without the WHERE)
rsTable1.Open "SELECT * FROM Table1",,adOpenStatic, adLockOptimistic
strTemp = Table1.Field1rs
Table2.Open "Selcet... etc where Table2.Field1 =" & [strTemp] etc...
Do While Not rsTable2.EOF
rsTable2!Field2 = rsTable1!Field1
.....
rsTable2.Update
rsTable2.MoveNext
Loop
rsTable2.Close
rsTable1.Close

(Please check the Editor Mode in upper right corner to see the code with Indent)

The realy odd part is the Table2 updates, but when finished I got the error message above. And the rsTable2.Update are emphasize in Yellow.
It's also very frustrating that this code works some places, and some places not. So far, I've seen this where the update are nested in two or more levels like the proc above.

Of course, the involved procedures works fine with access backend.

I hope someone can help with this! Please excuse my lousy english....

Best regards,
Geirr.


Last edited by Geirr; 12-04-2017 at 11:55 AM.
Geirr is offline   Reply With Quote
Old 12-04-2017, 12:00 PM   #2
jleach
Newly Registered User
 
Join Date: Jan 2012
Location: New York, New York
Posts: 117
Thanks: 6
Thanked 23 Times in 23 Posts
jleach will become famous soon enough
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Check the ODBC reported error:

Code:
?DAO.Errors(0)
(also check 1, 2 etc., but usually Error(0) is the only one, and the one we're after)

That should give you the error reported by the server itself, which is a whole lot more help than the VBA-specific error.
__________________
- Jack D. Leach - Dymeng Services
jleach is online now   Reply With Quote
Old 12-04-2017, 12:35 PM   #3
Geirr
Newly Registered User
 
Join Date: Apr 2012
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Geirr is on a distinguished road
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Hi there!

Can:
Item not found in this collection.
.. be the answer you want? if not, please excuse my stupid question on where should I put the code - ?DAO.Errors(0)

Geirr.

Geirr is offline   Reply With Quote
Old 12-04-2017, 12:38 PM   #4
jleach
Newly Registered User
 
Join Date: Jan 2012
Location: New York, New York
Posts: 117
Thanks: 6
Thanked 23 Times in 23 Posts
jleach will become famous soon enough
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Put it in your error handler. I think you'll want to check the Number and Description:

Code:
Function YourFunction()
  On Error Goto Err_Proc

  ' your code here

Exit_Proc:
   Exit Function
Err_Proc:
   MsgBox DAO.Errors(0).Number & ": " & DAO.Errors(0).Description
End Function
Naturally you'll want to fill the handler out a bit better than that, but that's the idea.
__________________
- Jack D. Leach - Dymeng Services
jleach is online now   Reply With Quote
Old 12-04-2017, 01:13 PM   #5
Geirr
Newly Registered User
 
Join Date: Apr 2012
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Geirr is on a distinguished road
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Sorry for wasting your time.

I forgot to inform the most important (maybe) that I am am usng ado all over the project. I'm little familiare with errorhandling, but I haven't use it on ado connection before. If you have version for ado, I would be glad, because i couldn't find a verison I could use quicly (google...)

Geirr.
Geirr is offline   Reply With Quote
Old 12-06-2017, 04:10 AM   #6
jleach
Newly Registered User
 
Join Date: Jan 2012
Location: New York, New York
Posts: 117
Thanks: 6
Thanked 23 Times in 23 Posts
jleach will become famous soon enough
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Here's an example found by googling "VBA ADO Errors":

https://support.microsoft.com/en-us/...from-ado-in-vb

There should be enough to get you going. It's the same basic concept.

hth
__________________
- Jack D. Leach - Dymeng Services
jleach is online now   Reply With Quote
The Following User Says Thank You to jleach For This Useful Post:
Geirr (12-06-2017)
Old 12-06-2017, 02:14 PM   #7
Geirr
Newly Registered User
 
Join Date: Apr 2012
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Geirr is on a distinguished road
Re: ODCB Call Failed - Run T Error 21472117887 (80040e21)

Hi.

Thank you Jleach for your time.

The case is that I am using a 'dsnless' solution with the connection with sql server authetication hardcoded in the program. And I believed this would be ok when using standard recodset def and open, like:
Dim rsTable As ADODB. Recordset
Set rsTable = New ADODB.Recordset
rsTable.Connection = CurrentProject.Connection
rsTable.Open "SELECT * FROM.....", , adOpenStatic, adLockOptimistic

But your hint to fetch the ado eror, forced me to think again on the ADODB.Connection.

So the code:
Dim ADOConn As New ADODB,Connection
ADOConn.ConnectionString = "Standard conn string with sql server aut."
ADOConn.Open

And then:
rsTable.Open "SELECT...",ADOConn, adOpenStatic, adLockOptimistic
...works perfect! Now I getting the real error, and so far they are easy to trace. For example adding a new record and forgetting to set a value in bit field wich requires a value....

Take care!
Geirr.


Geirr is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question odbc--call failed error Joe8915 General 0 06-08-2011 08:18 AM
ODBC - call failed error phamyh Tables 5 02-13-2009 07:15 AM
[SOLVED] ODBC Call Failed error Tiggerr77 General 0 01-13-2005 01:53 PM
ODBC call Failed Error dupati1 General 4 10-07-2003 06:45 AM
[SOLVED] ODBC--call failed error drskcpride General 5 08-28-2003 05:06 AM




All times are GMT -8. The time now is 04:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World