Record update run time error3146 ODBC Call Failed

[sacacompany]

Hi I m using the following VBA to add/update/delete records in the table on my SQL server but getting errors on Records. update....Record update run time error3146 ODBC Call Failed


Private Sub Command43_Click()

Dim Records As DAO.Recordset
Dim Records1 As DAO.Recordset
Dim Records2 As DAO.Recordset
'Dim Records3 As DAO.Recordset
Dim dd1 As Date
Dim dd As Date
Dim ssql As String
Dim stax As Variant
Dim RentID As Variant
Dim ERate As Variant

RentID = DLookup("[id]", "[rentagreement]", "[ID] =" & Forms![RentInvoicingGenenration]!RID)

'Set Records2 = CurrentDb.OpenRecordset("select id from rentagreement", dbOpenDynaset, dbAppendOnly)
'Records2.FindFirst "id= '" & Me.RID '"
If RentID > 0 Then

DoCmd.RunSQL "delete * from rentinvoice where rentagreementid = " _
& "Forms![RentInvoicingGenenration]!RID;"

GoTo GenerateInvoice
Else
GoTo GenerateInvoice
End If

GenerateInvoice:
dd = Me!DATEFROM.Value

Set Records = CurrentDb.OpenRecordset("Select * From rentinvoice", dbOpenDynaset, dbAppendOnly)

'Set Records1 = CurrentDb.OpenRecordset("Select salestaxrate " _
& "From tenant " _
& "where (id = " & Forms!Rentinvoicing!Text33 & ") ", dbOpenDynaset, dbAppendOnly)
'STax = DLookup("", "tenant", "id= & Forms![rentinvoicing]![Text33] & ")
stax = DLookup("[salestaxrate]", "[Tenant]", "[ID] =" & Forms![RentInvoicingGenenration]!Text33)
Do While dd <= Me!DATETO.Value
dd1 = DateSerial(Year(dd), Month(dd) + 1, 0)
Records.AddNew
Records!InvNo.Value = dd
Records!Dated.Value = dd
Records!RENTAGREEMENTID.Value = Me!RID.Value ' ?: Me!ID.Value
Records!DATEFROM.Value = dd
Records!DATETO.Value = dd1
Records!AREA.Value = Me!AREASQFT.Value
Records!RENTPERMONTH.Value = Me!mrent.Value
Records!SalesTaxRate.Value = stax
Records!SecurityCharges.Value = Me!Text44.Value
Records!AntenaCharges.Value = Me!antena.Value
'Records!ElectricRate.Value = Me!Text48.Value
Records.Update

dd = DateAdd("d", 1, dd1)
Loop

Set Records = Nothing

MsgBox "Rent Invoices for the period from &dd& to #dd1# Generated Successfully!"

End Sub

 

[June7]

Why is InvNo set to a date value?

 

[sacacompany]

inv No is generated using inv date/month

 

[June7]

Okay, but InvNo and Dated are both being set with dd variable.

 

[sacacompany]

yes it take inv date as date and invno as text based on date

 

[sacacompany]

can you help with error resolution? because the code was running fine when I had my table in MS Access. recently I shifted them to SQL server over LAN and the same code that was running fine started giving errors. Thanks

 

[Minty]

It's probably because there is a missing primary key on the linked table you are trying to update.
You'll need to also add dbSeeChanges to any VBA query code that updates records.

Check that all your tables when you linked them have correctly identified the Primary key.

 

[June7]

I still don't understand why you have two fields with the same value.