尝试将记录添加到SQL数据库时，获取“3146ODBC - 调用失败”错误 [英] Getting '3146ODBC - called failed' error when trying to add a record to a SQL database

查看：187
本文介绍了尝试将记录添加到SQL数据库时，获取“3146ODBC - 调用失败”错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的客户端使用Access 2010作为SQL Server 2012 Express数据库的前端。当他们尝试添加记录时，会收到错误"3146ODBC - 称为失败"。我已经找到了这个问题的解决方案，他们在
Access和SQL上引用了较旧的版本。我的客户端在Windows 7上。

My client is using Access 2010 as a front end to a SQL Server 2012 Express database. When they try to add a record they get the error '3146ODBC - called failed'. I have looked on line fro solutions to this problem and they refer to older versions on Access and SQL. My client is on Windows 7.

添加记录的代码如下。我应该在Windows 7上查找wint Access 2010，SQL Server 2012 Express？

The code for adding a record is below. What should I look for wint Access 2010, SQL Server 2012 Express on Windows 7?

Option Compare Database
Option Explicit

Function LoadData()
    Dim RecSet As DAO.Recordset
    Dim objXLAppln As Excel.Application
    Dim objWBook As Excel.Workbook
    Dim StrPathFile As String, strFile As String, strPath As String
    Dim strBrowseMsg As String, strInitialDirectory As String, strFilter As String
    'show dialogue box
    strBrowseMsg = "Select the EXCEL file:"
    'set directory to load files from
    strInitialDirectory = "C:\Bridge_CIP_Part-A_B\"
    'run strFilter function
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xlsx)", "*.xlsx")

    StrPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
       Filter:=strFilter, OpenFile:=True, _
       DialogTitle:=strBrowseMsg, _
       Flags:=ahtOFN_HIDEREADONLY)

    If StrPathFile = "" Then
       MsgBox "No file was selected.", vbOK, "No Selection"
       Exit Function
    End If
    'Set Excel application object. Critical for macro to run properly. Do not change.
    Set objXLAppln = New Excel.Application
    'Open workbook and worksheet to load data.
    With objXLAppln
        Set objWBook = .Workbooks.Open(StrPathFile)
    End With
    Set RecSet = CurrentDb.OpenRecordset("Part_A-B")
    'Copy data from Excel cells to Access fields
    objXLAppln.Sheets("Main_Tab").Select
    objXLAppln.Range("A3").Select
    With RecSet
    RecSet.AddNew
    RecSet.Fields("CIP_ID").value = objXLAppln.ActiveCell.Offset(1, 1).value
    RecSet.Fields("ProjectName").value = objXLAppln.ActiveCell.Offset(1, 3).value
    RecSet.Fields("BundledID").value = objXLAppln.ActiveCell.Offset(1, 5).value
    RecSet.Fields("BridgeComplex01Name").value = objXLAppln.ActiveCell.Offset(3, 1).value
    RecSet.Fields("BridgeComplex02Name").value = objXLAppln.ActiveCell.Offset(3, 3).value
    RecSet.Fields("BridgeComplex03Name").value = objXLAppln.ActiveCell.Offset(3, 5).value
    RecSet.Fields("BridgeName01").value = objXLAppln.ActiveCell.Offset(5, 1).value
    RecSet.Fields("BridgeNumber01").value = objXLAppln.ActiveCell.Offset(5, 3).value
    RecSet.Fields("BridgeLocation01").value = objXLAppln.ActiveCell.Offset(5, 5).value
    RecSet.Fields("BridgeName02").value = objXLAppln.ActiveCell.Offset(7, 1).value
    RecSet.Fields("BridgeNumber02").value = objXLAppln.ActiveCell.Offset(7, 3).value
    RecSet.Fields("BridgeLocation02").value = objXLAppln.ActiveCell.Offset(7, 5).value
    RecSet.Fields("BridgeName03").value = objXLAppln.ActiveCell.Offset(9, 1).value
    RecSet.Fields("BridgeNumber03").value = objXLAppln.ActiveCell.Offset(9, 3).value
    RecSet.Fields("BridgeLocation03").value = objXLAppln.ActiveCell.Offset(9, 5).value
    RecSet.Fields("BridgeName04").value = objXLAppln.ActiveCell.Offset(11, 1).value
    RecSet.Fields("BridgeNumber04").value = objXLAppln.ActiveCell.Offset(11, 3).value
    RecSet.Fields("BridgeLocation04").value = objXLAppln.ActiveCell.Offset(11, 5).value
    RecSet.Fields("BridgeName05").value = objXLAppln.ActiveCell.Offset(13, 1).value
    RecSet.Fields("BridgeNumber05").value = objXLAppln.ActiveCell.Offset(13, 3).value
    RecSet.Fields("BridgeLocation05").value = objXLAppln.ActiveCell.Offset(13, 5).value
    RecSet.Fields("BridgeName06").value = objXLAppln.ActiveCell.Offset(15, 1).value
    RecSet.Fields("BridgeNumber06").value = objXLAppln.ActiveCell.Offset(15, 3).value
    RecSet.Fields("BridgeLocation06").value = objXLAppln.ActiveCell.Offset(15, 5).value
    RecSet.Fields("BridgeName07").value = objXLAppln.ActiveCell.Offset(17, 1).value
    RecSet.Fields("BridgeNumber07").value = objXLAppln.ActiveCell.Offset(17, 3).value
    RecSet.Fields("BridgeLocation07").value = objXLAppln.ActiveCell.Offset(17, 5).value
    RecSet.Fields("BridgeName08").value = objXLAppln.ActiveCell.Offset(19, 1).value
    RecSet.Fields("BridgeNumber08").value = objXLAppln.ActiveCell.Offset(19, 3).value
    RecSet.Fields("BridgeLocation08").value = objXLAppln.ActiveCell.Offset(19, 5).value
    RecSet.Fields("WorkCategory").value = objXLAppln.ActiveCell.Offset(21, 5).value
    RecSet.Fields("ProblemDefinition").value = objXLAppln.ActiveCell.Offset(24, 0).value
    RecSet.Fields("ProposedSolution").value = objXLAppln.ActiveCell.Offset(27, 0).value
    RecSet.Fields("ProjectJustification").value = objXLAppln.ActiveCell.Offset(30, 0).value
    RecSet.Fields("Initial_CV_Sub").value = objXLAppln.ActiveCell.Offset(59, 5).value
    RecSet.Fields("RightOfWay").value = objXLAppln.ActiveCell.Offset(65, 3).value
    RecSet.Fields("Utilities").value = objXLAppln.ActiveCell.Offset(70, 3).value
    RecSet.Fields("OccProb5").value = objXLAppln.ActiveCell.Offset(75, 1).value
    RecSet.Fields("OccProb10").value = objXLAppln.ActiveCell.Offset(75, 2).value
    RecSet.Fields("OccProb15").value = objXLAppln.ActiveCell.Offset(75, 3).value
    RecSet.Fields("OccProb20").value = objXLAppln.ActiveCell.Offset(75, 4).value
    RecSet.Fields("CostRisk5").value = objXLAppln.ActiveCell.Offset(77, 1).value
    RecSet.Fields("CostRisk10").value = objXLAppln.ActiveCell.Offset(77, 2).value
    RecSet.Fields("CostRisk15").value = objXLAppln.ActiveCell.Offset(77, 3).value
    RecSet.Fields("CostRisk20").value = objXLAppln.ActiveCell.Offset(77, 4).value
    RecSet.Fields("CostChange5").value = objXLAppln.ActiveCell.Offset(80, 1).value
    RecSet.Fields("CostChange10").value = objXLAppln.ActiveCell.Offset(80, 2).value
    RecSet.Fields("CostChange15").value = objXLAppln.ActiveCell.Offset(80, 3).value
    RecSet.Fields("CostChange20").value = objXLAppln.ActiveCell.Offset(80, 4).value
    RecSet.Fields("IndirectCost5").value = objXLAppln.ActiveCell.Offset(84, 1).value
    RecSet.Fields("IndirectCost10").value = objXLAppln.ActiveCell.Offset(84, 2).value
    RecSet.Fields("IndirectCost15").value = objXLAppln.ActiveCell.Offset(84, 3).value
    RecSet.Fields("IndirectCost20").value = objXLAppln.ActiveCell.Offset(84, 4).value
    RecSet.Fields("MOBaseScore").value = objXLAppln.ActiveCell.Offset(91, 1).value
    RecSet.Fields("MOScore5").value = objXLAppln.ActiveCell.Offset(91, 2).value
    RecSet.Fields("MOScore10").value = objXLAppln.ActiveCell.Offset(91, 3).value
    RecSet.Fields("MOScore15").value = objXLAppln.ActiveCell.Offset(91, 4).value
    RecSet.Fields("MOScore20").value = objXLAppln.ActiveCell.Offset(91, 5).value
    RecSet.Fields("RABaseScore").value = objXLAppln.ActiveCell.Offset(92, 1).value
    RecSet.Fields("RAScore5").value = objXLAppln.ActiveCell.Offset(92, 2).value
    RecSet.Fields("RAScore10").value = objXLAppln.ActiveCell.Offset(92, 3).value
    RecSet.Fields("RAScore15").value = objXLAppln.ActiveCell.Offset(92, 4).value
    RecSet.Fields("RAScore20").value = objXLAppln.ActiveCell.Offset(92, 5).value
    RecSet.Fields("SIBaseScore").value = objXLAppln.ActiveCell.Offset(93, 1).value
    RecSet.Fields("SIScore5").value = objXLAppln.ActiveCell.Offset(93, 2).value
    RecSet.Fields("SIScore10").value = objXLAppln.ActiveCell.Offset(93, 3).value
    RecSet.Fields("SIScore15").value = objXLAppln.ActiveCell.Offset(93, 4).value
    RecSet.Fields("SIScore20").value = objXLAppln.ActiveCell.Offset(93, 5).value
    RecSet.Fields("EPBaseScore").value = objXLAppln.ActiveCell.Offset(94, 1).value
    RecSet.Fields("EPScore5").value = objXLAppln.ActiveCell.Offset(94, 2).value
    RecSet.Fields("EPScore10").value = objXLAppln.ActiveCell.Offset(94, 3).value
    RecSet.Fields("EPScore15").value = objXLAppln.ActiveCell.Offset(94, 4).value
    RecSet.Fields("EPScore20").value = objXLAppln.ActiveCell.Offset(94, 5).value
    RecSet.Fields("MaintBaseScore").value = objXLAppln.ActiveCell.Offset(95, 1).value
    RecSet.Fields("MaintScore5").value = objXLAppln.ActiveCell.Offset(95, 2).value
    RecSet.Fields("MaintScore10").value = objXLAppln.ActiveCell.Offset(95, 3).value
    RecSet.Fields("MaintScore15").value = objXLAppln.ActiveCell.Offset(95, 4).value
    RecSet.Fields("MaintScore20").value = objXLAppln.ActiveCell.Offset(95, 5).value
    RecSet.Fields("USBaseScore").value = objXLAppln.ActiveCell.Offset(96, 1).value
    RecSet.Fields("USScore5").value = objXLAppln.ActiveCell.Offset(96, 2).value
    RecSet.Fields("USScore10").value = objXLAppln.ActiveCell.Offset(96, 3).value
    RecSet.Fields("USScore15").value = objXLAppln.ActiveCell.Offset(96, 4).value
    RecSet.Fields("USScore20").value = objXLAppln.ActiveCell.Offset(96, 5).value
    RecSet.Fields("LCBaseScore").value = objXLAppln.ActiveCell.Offset(97, 1).value
    RecSet.Fields("LCScore5").value = objXLAppln.ActiveCell.Offset(97, 2).value
    RecSet.Fields("LCScore10").value = objXLAppln.ActiveCell.Offset(97, 3).value
    RecSet.Fields("LCScore15").value = objXLAppln.ActiveCell.Offset(97, 4).value
    RecSet.Fields("LCScore20").value = objXLAppln.ActiveCell.Offset(97, 5).value
    RecSet.Fields("SJBaseScore").value = objXLAppln.ActiveCell.Offset(98, 1).value
    RecSet.Fields("SJScore5").value = objXLAppln.ActiveCell.Offset(98, 2).value
    RecSet.Fields("SJScore10").value = objXLAppln.ActiveCell.Offset(98, 3).value
    RecSet.Fields("SJScore15").value = objXLAppln.ActiveCell.Offset(98, 4).value
    RecSet.Fields("SJScore20").value = objXLAppln.ActiveCell.Offset(98, 5).value
    RecSet.Fields("SustBaseScore").value = objXLAppln.ActiveCell.Offset(99, 1).value
    RecSet.Fields("SustScore5").value = objXLAppln.ActiveCell.Offset(99, 2).value
    RecSet.Fields("SustScore10").value = objXLAppln.ActiveCell.Offset(99, 3).value
    RecSet.Fields("SustScore15").value = objXLAppln.ActiveCell.Offset(99, 4).value
    RecSet.Fields("SustScore20").value = objXLAppln.ActiveCell.Offset(99, 5).value
    RecSet.Fields("TOBaseScore").value = objXLAppln.ActiveCell.Offset(100, 1).value
    RecSet.Fields("TOScore5").value = objXLAppln.ActiveCell.Offset(100, 2).value
    RecSet.Fields("TOScore10").value = objXLAppln.ActiveCell.Offset(100, 3).value
    RecSet.Fields("TOScore15").value = objXLAppln.ActiveCell.Offset(100, 4).value
    RecSet.Fields("TOScore20").value = objXLAppln.ActiveCell.Offset(100, 5).value
    On Error Resume Next
    'Update Access table
    RecSet.Update
    'check error
    Select Case Err.Number
        'duplicate key
        Case 3022
        'cancel add records
        RecSet.CancelUpdate
        'code to handle other errors
        Case Is <> 0
            MsgBox Err.Number & "" & Err.Description
    End Select
    'enable error handling
    On Error GoTo 0
    End With
    ' Close table "Part_A-B"
    RecSet.Close
    ' Open table "Notes"
    Set RecSet = CurrentDb.OpenRecordset("Notes", dbOpenDynaset, dbSeeChanges)
    'Copy data from Excel cells to Access fields
    objXLAppln.Sheets("Main_Tab").Select
    objXLAppln.Range("A3").Select
    With RecSet
    RecSet.AddNew
    RecSet.Fields("CIP_ID").value = objXLAppln.ActiveCell.Offset(1, 1).value
    RecSet.Update
    End With
    'Close everything
    RecSet.Close
    objWBook.Close SaveChanges:=False
    objXLAppln.Quit
    Set RecSet = Nothing
End Function

推荐答案

在错误处理程序中使用以下代码进行调试：

Debug this with code like this in your error handler:

Dim i As Integer

Dim s As String

For i = 0 To Errors.Count - 1

    s = s&错误（i）。描述& vbCrLf

下一页i

MsgBox s，vbCritical

Dim i As Integer
Dim s As String
For i = 0 To Errors.Count - 1
    s = s & Errors(i).Description & vbCrLf
Next i
MsgBox s, vbCritical

您将从ODBC获得更好的消息。

You will get a much better message from ODBC.

此外，这是一个可怕的数据库设计，包含所有重复组。我相信这是一份不断赠送的礼物。

Also, this is a horrible database design with all the repeating groups. I'm sure that's a gift that keeps on giving.


这篇关于尝试将记录添加到SQL数据库时，获取“3146ODBC - 调用失败”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆