By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,460 Members | 1,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,460 IT Pros & Developers. It's quick & easy.

SQL Error

100+
P: 102
Background: Front end MS Access 2010/VBA; Back end MS SQL Server 2008

I have a situation where one SQL table (dbo_BDItems) is being read, some data is being extracted and placed in a second table (dbo_BDBudgets).

I am getting this Error# 3146
ODBC--call failed. It happens on the .Update statement.

Can anyone tell me where I am making a mistake? Thanks
Expand|Select|Wrap|Line Numbers
  1. dim FY as string
  2. FY = "30"
  3. Dim rs As DAO.Recordset
  4. Dim db As Database
  5. Set db = CurrentDb
  6. Set rs = CurrentDb.OpenRecordset("dbo_BDItems", dbOpenDynaset, dbSeeChanges)
  7.  
  8. Dim rs2 As DAO.Recordset
  9. Dim db2 As Database
  10. Set db2 = CurrentDb
  11. Set rs2 = CurrentDb.OpenRecordset("dbo_BDBudgets", dbOpenDynaset, dbSeeChanges)
  12.  
  13.  
  14. If rs.RecordCount <> 0 Then
  15.    rs.MoveFirst
  16.    While Not rs.EOF
  17.  
  18.           With rs2
  19.             .AddNew
  20.                .Fields("FY") = FY
  21.                .Fields("Item") = rs.Fields("Item")
  22.             .Update
  23.           End With
  24.  
  25.  
  26.     rs.MoveNext
  27.  Wend
  28. End If
Mar 20 '15 #1

✓ answered by jforbes

  • Have you attempted to perform a similar Insert Query from with in Access and From within SSMS (SQL Server Management Studio)?
  • Have you debug.print-ed FY and rs.Fields("Item") to see what they are?
  • Are you attempting to supply a value for an Identity Column. Sometimes SQL doesn't like that unless you turn off (or on, can't remember the syntax) Identity Insert Flag. Probably won't error on you, but it's possible.

I don't usually use RecordSets to insert new records into SQL. I tend to use CurrentDB.Execute. Here is an example, maybe it will help:
Expand|Select|Wrap|Line Numbers
  1. Private Sub addSchedule()    
  2.     Dim sSQL As String
  3.     ...
  4.     sSQL = "INSERT INTO Calendar (CalendarDate, Type) VALUES (#" & sCurrent & "#, " & iType & ")"
  5.     Call executeSQL(sSQL)
  6. End Sub
  7.  
  8. Public Sub executeSQL(ByRef sSQL As String)
  9. On Error GoTo ErrorOut
  10.     CurrentDB.Execute sSQL, dbFailOnError + dbSeeChanges
  11. ExitOut:
  12.     Exit Sub
  13. ErrorOut:
  14.     ...
  15.     Call MsgBox("Could not execute SQL: " & vbCrLf & vbCrLf & Err.Description)
  16.     Resume ExitOut
  17. End Sub

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
On line 19, you need to fix the = FY. You don't have a variable declared as that, so I'm guessing that it is a field in your first recordset.
Mar 20 '15 #2

100+
P: 102
I actually do have a variable set up. It is not shown in this code. Sorry.
Mar 20 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Okay. Do you have access to SQL Server Management Studio? If so, make sure that both the FY and Item fields have the appropriate data types. For example, if FY is a number field (int, smallint, decimal, etc.) then this will fail because you are passing a string. If the Item field in dbo_BDItems is bigger than the Item field in dbo_BDBudgets, then it will fail.
Mar 20 '15 #4

100+
P: 102
they are the same type and size
Mar 20 '15 #5

jforbes
Expert 100+
P: 1,107
  • Have you attempted to perform a similar Insert Query from with in Access and From within SSMS (SQL Server Management Studio)?
  • Have you debug.print-ed FY and rs.Fields("Item") to see what they are?
  • Are you attempting to supply a value for an Identity Column. Sometimes SQL doesn't like that unless you turn off (or on, can't remember the syntax) Identity Insert Flag. Probably won't error on you, but it's possible.

I don't usually use RecordSets to insert new records into SQL. I tend to use CurrentDB.Execute. Here is an example, maybe it will help:
Expand|Select|Wrap|Line Numbers
  1. Private Sub addSchedule()    
  2.     Dim sSQL As String
  3.     ...
  4.     sSQL = "INSERT INTO Calendar (CalendarDate, Type) VALUES (#" & sCurrent & "#, " & iType & ")"
  5.     Call executeSQL(sSQL)
  6. End Sub
  7.  
  8. Public Sub executeSQL(ByRef sSQL As String)
  9. On Error GoTo ErrorOut
  10.     CurrentDB.Execute sSQL, dbFailOnError + dbSeeChanges
  11. ExitOut:
  12.     Exit Sub
  13. ErrorOut:
  14.     ...
  15.     Call MsgBox("Could not execute SQL: " & vbCrLf & vbCrLf & Err.Description)
  16.     Resume ExitOut
  17. End Sub
Mar 20 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.