Solved

ODBC Call Failed when writing from MS Access to SQL Server Tables

Posted on 2014-07-25
3
1,804 Views
Last Modified: 2014-07-26
Hi all,

Following code creates a ODBC --Call Failed error. Works on some machines / fails on others - all on same network/same server.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim CurrentDate As Date

CurrentDate = Date

   Set dbs = CurrentDb
   strSQL = "SELECT * FROM dbo_usrClockCards"
   Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

   rst.AddNew
   rst!userID = Me.txtUserID
   rst!ClockDate = CurrentDate
   rst!ClockTime = Format(Now, "yyyy-mm-dd hh:mm:ss")
   rst!ClockTypeID = 1
   rst.Update
   rst.Close

Set rst = Nothing
Set dbs = Nothing
0
Comment
Question by:SeanNij
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40219896
Add some additional error handling as show here (enumerate the errors collection):

How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/kb/161288

 to make sure your getting all the errors.  usually the first is a generic one (3146), which doesn't tell you anything.

Once you have those, you usually have a good idea of what's causing the problem.

Jim.
0
 
LVL 3

Expert Comment

by:Leo Alexander
ID: 40220542
You write that it works on some machines and not others... same version of Access? Have you tried to do a compile and/or repair and compact?

Also,

dbo_usrClockCards

Is the ODBC connection setup properly for the above table? i.e. are you able to open that table from access within the Navigation Pane?
0
 
LVL 1

Author Closing Comment

by:SeanNij
ID: 40222222
Jim, Thanks.

Sent me in the right direction. Got some other nebulous "ODBC Optional Feature not implemented" message. Turns out to be field type on the SQL Tables.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question