ODCB Call Failed - Run T Error 21472117887 (80040e21) (1 Viewer)

Geirr

Registered User.
Local time
Today, 10:06
Joined
Apr 13, 2012
Messages
36
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 so
me 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:

jleach

Registered User.
Local time
Today, 05:06
Joined
Jan 4, 2012
Messages
308
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.
 

Geirr

Registered User.
Local time
Today, 10:06
Joined
Apr 13, 2012
Messages
36
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.
 

jleach

Registered User.
Local time
Today, 05:06
Joined
Jan 4, 2012
Messages
308
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.
 

Geirr

Registered User.
Local time
Today, 10:06
Joined
Apr 13, 2012
Messages
36
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

Registered User.
Local time
Today, 10:06
Joined
Apr 13, 2012
Messages
36
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.
 

Users who are viewing this thread

Top Bottom