HMote


What would be the best way to do this Try/Catch

I was trying to check all the records to find the date/hour key but I thought there might be a better/quicker way to check if the record exists...or rather, if the update fails. Would this work Is there a better way to do this or is my thinking wrong Is there a way to check if an update fails...would it throw an exception

Thanks for the help!




Re: Excel VBA - Best way to SQL Insert if SQL Update fails

HMote


Maybe a better question...does VBA have try/catch blocks I can't find any information and I'm getting errors when trying to use these.




Re: Excel VBA - Best way to SQL Insert if SQL Update fails

HMote

I found what I needed...it's On Error instead of Try/Catch.




Re: Excel VBA - Best way to SQL Insert if SQL Update fails

ADG

If your table is indexed you can always open a recordset , set the index, then search for your key field(s). The nomatch property of the seek method tells you where a value is found or not, then you can edit or add the record.

Below is an example from Excel where data is written back to Access when the user changes column AN

Private Sub Worksheet_Change(ByVal Target As Range)
Dim db As DAO.Database
Dim rs, rsAll As Recordset
Dim x, Ono, LineNo As Long
Dim Co, Otype, Osuf As String

If ActiveCell.Column = 40 Then
x = ActiveCell.Row
If ((Me.Cells(x, 3).Value = "OM") Or (Me.Cells(x, 3).Value = "OS")) Then
Set db = DBEngine(0).OpenDatabase("P:\Outstanding Eng orders.mdb")
Set rs = db.openrecordset("Outstanding Orders")
Set rsAll = db.openrecordset("All Orders")
rs.Index = "PrimaryKey"
rsAll.Index = "PrimaryKey"
Co = Me.Cells(x, 1).Value
Ono = Me.Cells(x, 2).Value
Otype = Me.Cells(x, 3).Value
Osuf = Me.Cells(x, 4).Value
LineNo = Me.Cells(x, 5).Value
rs.Seek "=", Co, Ono, Otype, Osuf, LineNo
If Not rs.nomatch Then
rs.Edit
If ActiveCell.Value = False Then rs!capital = False Else rs!capital = True
rs.Update
End If
rsAll.Seek "=", Co, Ono, Otype, Osuf, LineNo
If Not rsAll.nomatch Then
rsAll.Edit
If ActiveCell.Value = False Then rsAll!capital = False Else rsAll!capital = True
rsAll.Update
End If
rs.Close
rsAll.Close
db.Close
Set db = Nothing
Set rs = Nothing
Set rsAll = Nothing
End If
End If
End Sub