SQL Error

[sc5502]

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

dim FY as string
FY = "30"
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("dbo_BDItems", dbOpenDynaset, dbSeeChanges)

Dim rs2 As DAO.Recordset
Dim db2 As Database
Set db2 = CurrentDb
Set rs2 = CurrentDb.OpenRecordset("dbo_BDBudgets", dbOpenDynaset, dbSeeChanges)


If rs.RecordCount <> 0 Then
   rs.MoveFirst
   While Not rs.EOF
   
          With rs2
            .AddNew
               .Fields("FY") = FY
               .Fields("Item") = rs.Fields("Item")
            .Update
          End With
          
   
    rs.MoveNext
 Wend
End If

[Seth Schrock]

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.

[sc5502]

I actually do have a variable set up. It is not shown in this code. Sorry.

[Seth Schrock]

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.

[sc5502]

they are the same type and size

[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.Execu te. Here is an example, maybe it will help:

Private Sub addSchedule()    
    Dim sSQL As String
    ...
    sSQL = "INSERT INTO Calendar (CalendarDate, Type) VALUES (#" & sCurrent & "#, " & iType & ")"
    Call executeSQL(sSQL)
End Sub

Public Sub executeSQL(ByRef sSQL As String)
On Error GoTo ErrorOut
    CurrentDB.Execute sSQL, dbFailOnError + dbSeeChanges
ExitOut:
    Exit Sub
ErrorOut:
    ...
    Call MsgBox("Could not execute SQL: " & vbCrLf & vbCrLf & Err.Description)
    Resume ExitOut
End Sub