Openrecordset Error 3146
Contents |
question and get tips
Run Time Error 3146 Odbc Call Failed Access
& solutions from a community of 418,616 IT Pros run time error 3146 access & Developers. It's quick & easy. ODBC Call error 3146 P: n/a Jimbo
Run Time Error 3146 Odbc Call Failed Access 2010
I have an access 97 database linked to sql server..I click update on a form and get an error message: Run-time error 3146 access vba runtime error 3146 ODBC call failed The debugger points to this line: MyTable.Update In this segment of code: Private Sub Update5_Click() Dim MyDB As Database, MyTable As Recordset, MyTmpTable As Recordset, Criteria As String, NewID As Long Set MyDB = DBEngine.Workspaces(0).Databases(0) Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET, dbSeeChanges) Set MyTmpTable = MyDB.OpenRecordset("tmp_RockDx5", DB_OPEN_DYNASET) MyTable.AddNew MyTable.Update MyTable.Move 0, MyTable.LastModified NewID = MyTable![Dx5ID] Me![HoldNewDxID] = NewID If DCount("*", "Dx5Current") > 0 Then Me![HoldOldDxID] = Me![Dx5Current].Form![Dx5ID] 'Put end date on the current Axis 5 'record Me![Dx5Current].Form![EndDate] = Date DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD 'Copy in data from the current Axis 5 'record to the new record, since it is 'likely to be similar Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET) holdnum = Forms![DxSummary]![HoldNewDxID] Criteria = "[Dx5ID] = " & holdnum MyTable.FindFirst Criteria MyTable.Edit MyTable![CurrentGAF] = Me![Dx5Current].Form![CurrentGAF] MyTable![HighestGAFLastYear] = Me![Dx5Current].Form![HighestGAFLastYear] MyTable.Update End If Me.Visible = False Me![Dx5Current].Requery 'This is necessary in order to release the 'record, so that it can be edited if the 'user uses the Cancel button on the add-edit 'form Criteria = "[Dx5ID] = Forms![DxSummary]![HoldNewDxID]" DoCmd.OpenForm "AddDx5", , , Criteria, A_EDIT MyTmpTable.Close MyTable.Close Forms![AddDx5]![ClientID] = Forms![LogIn]![ActiveClient] Forms![AddDx5]![StaffID] = Forms![LogIn]![Active
Jean Guest Hi, Below is our code, we are running MSSQL 2000 with SP2, MSACCESS 2002. This application is converted from MSACCESS 97/MSSQL 7.0. We ran into the error "ODBC Call Failed, 3146" at the last line of the below statement, and don't know what caused the error. The select query "qryTotalBatchAmount" ran just fine if we executed in Query design, but not inside the code. ======================================= Dim varReturn As Variant Dim rs As Recordset Dim https://bytes.com/topic/access/answers/206736-odbc-call-error-3146-a qdf As QueryDef If IsNull(Me.cboBatchID) Then MsgBox "You Must Select a Batch to Proceed", vbCritical, "Select a Batch" Exit Sub End If Set qdf = gdb1.QueryDefs("qryMemberPostedPaymentsCount") qdf.Parameters("IDX") = Me.cboBatchID Set rs = qdf.OpenRecordset() If rs!CountIDX > 0 Then MsgBox "Records for this batch have previously been posted." _ & vbCrLf & "Please Contact Your System Administrator!", _ http://www.pcreview.co.uk/threads/when-calling-openrecordset-odbc-call-failed-3146-error-appears.1144293/ vbCritical, "Process Aborted!!" GoTo cmdPostExit End If Set qdf = gdb1.QueryDefs("qryTotalBatchAmount") qdf.Parameters("lngBatchID") = Me.cboBatchID Set rs = qdf.OpenRecordset() =============================================== Any suggestion is appreciated. Thanks Jean Jean, Nov 11, 2003 #1 Advertisements Ron Weiner Guest Try changing the dim to unambigiously use the DAO library: Dim rs As DAO.Recordset Dim qdf As DAO.QueryDef and just before: Set qdf = gdb1.QueryDefs("qryTotalBatchAmount") qdf.Parameters("lngBatchID") = Me.cboBatchID Set rs = qdf.OpenRecordset() add: rs.close set qdf = nothing set rs = nothing to explicitly close and un-set the qdf and rs variables before attempting to use them again. Hope this helps! Ron W "Jean" <> wrote in message news:... Hi, Below is our code, we are running MSSQL 2000 with SP2, MSACCESS 2002. This application is converted from MSACCESS 97/MSSQL 7.0. We ran into the error "ODBC Call Failed, 3146" at the last line of the below statement, and don't know what caused the error. The select query "qryTotalBatchAmount" ran just fine if we executed in Query design, but not inside the code. ==========
visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can http://www.dbforums.com/showthread.php?1676072-ODBC-Error-3146-on-rs-update post: click the register link above to proceed. To start viewing messages, https://www.experts-exchange.com/questions/21212090/Run-Time-error-3146-ODBC-Call-failed.html select the forum that you want to visit from the selection below. Results 1 to 6 of 6 Thread: ODBC Error 3146 on rs.update Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode error 3146 Switch to Threaded Mode 02-22-12,20:56 #1 SHARON.CHAPMAN7 View Profile View Forum Posts Registered User Join Date Aug 2011 Posts 11 Unanswered: ODBC Error 3146 on rs.update Hi, Using MS Access, I have a form that is based on a table called Motion_Imagery. In the VBA of this code I want to take two fields on the form and add time error 3146 the data in those two fields to the last record that was added to table Online_Books. When I run my code I get the error when it gets to rs.update, here is my code: Set db = CurrentDb() Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset, dbSeeChanges) rs.MoveLast rs.Edit rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR rs![CTRL_ID] = Me.CTRL_NBR rs.Update Can you tell me what the problem is? Is this possible to do? Thanks, Sharon Reply With Quote 03-04-12,08:34 #2 canupus View Profile View Forum Posts Registered User Join Date Jul 2004 Location South Dakota Posts 267 Sharon-- Any luck on this yet? One thing you can try is to change from a dynaset recordset to a table recordset by using dbOpenTable as the second paramater in OpenRecordset. Just shooting in the dark. Also, careful about using MoveLast as that will move to the last record in the table but not necessarily the last record that was added. The best way would be to use some type of TimeStamp field and then query out the record with the latest
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Run-Time error 3146 ODBC - Call failed Want to Advertise Here? Solved Run-Time error 3146 ODBC - Call failed Posted on 2004-11-18 Visual Basic Classic 1 Verified Solution 9 Comments 38,847 Views 1 Ratings Last Modified: 2013-12-25 I'm getting this error Run-Time error 3146 ODBC - Call failed when i try to run this code. I dont know what i'm doig wrong. The code worked until i changed the SQL query. I have pasted both, the difference is the first SQL query. pLease help. Original Working Code. Sub Fill_Invoice_Info() Dim InvRS As Recordset Dim RDRS As Recordset Dim qdf As QueryDef Set qdf = db.CreateQueryDef("") qdf.Connect = "odbc;dsn=SQLServer;uid=UsersGroup;pwd=password;database=nicabm;" qdf.ReturnsRecords = True SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Invoice.PCode, Invoice.BadgeName, Invoice.Leadership " SQL2 = "FROM Invoice INNER JOIN Participants ON Invoice.InvID = Participants.InvID;" qdf.SQL = SQL1 & SQL2 Set InvRS = qdf.OpenRecordset(dbOpenSnapshot) SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.transdate) AS MinOftransdate " SQL2 = "FROM Participants INNER JOIN InvoiceTransaction ON Participants.InvID = InvoiceTransaction.invid " SQL3 = "GROUP BY Participants.CustID, Participants.InvID;" qdf.SQL = SQL1 & SQL2 & SQL3 Set RDRS = qdf.OpenRecordset(dbOpenSnapshot) For i = 2 To NumOfPart + 1 CurrCust = MT.Cells(i, CustIDCol).Value Criteria = "CustID = """ & CurrCust & """" InvRS.FindFirst Criteria MT.Cells(i, InvStart).Value = InvRS.Fields(3).Value 'Field(3) is BadgeName If Not (IsNull(InvRS.Fields(4).Value)) Then 'Field(4) is Leadership Council If InvRS.Fields(4).Value = 1 Then MT.Cells(i, LCCol).