The reality is there are cases where the rows are blank and that's okay. I
still want to Insert the other rows that aren't blank. I didn't include all
the code but the range is quite a few more rows. On just about every
occasion, I will have fewer than the total number of rows with data. Is it
possible to create specific user-defined messages for various ODBC errors?
Thanks in advance

"Tom Ogilvy" wrote:

Why not just avoid the error by checking if the cells have data. If they do,
then do the update. If not, don't.

If Application.CountA(Range("A29:C29")) 0 then

' code to write the data

End if

as an example.
--
Regards,
Tom Ogilvy


"MikeElectricUtility" wrote:

Hi, I have a Procedure that inserts data from an Excel Range into an Access
table. If there are rows without data in the Range, I get a Run-Time Error
'-2147467259(80004005) because a text field in Access can't be a zero-length
string. No big deal! I don't want to insert blank rows anyway. I am trying
to override the error. I thought I could use Application.ODBCErrors to get
the error number. Please show me how to ignore error '-2147467259(80004005).
Thanks in advance

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Set MyCn = Nothing
Set MyCn = New ADODB.Connection
'On Error GoTo ErrorHandle 'I want to be able to bypass certain ODBC Errors

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=Z:\El Dorado Springs Inventory.mdb"

SQLStr = "INSERT INTO [Inventory Export]" _
& "VALUES('" & Worksheets("InvExport").Range("A29").Value & "','" _
& Worksheets("InvExport").Range("B29").Value & "','" _
& Worksheets("InvExport").Range("C29").Value & "')"
MyCn.Execute SQLStr

SQLStr = "INSERT INTO [Inventory Export]" _
& "VALUES('" & Worksheets("InvExport").Range("A30").Value & "','" _
& Worksheets("InvExport").Range("B30").Value & "','" _
& Worksheets("InvExport").Range("C30").Value & "')"
MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing
Exit Sub

ErrorHandle:
If Application.ODBCErrors.Value = 80004005 Then
Err.Clear
Resume Next
Else
MsgBox "An Error Has Occured on the Export, Check [Inventory Export]
Table in " _
& "Access to Ensure All Data was Transferred"
Resume Next

End If

End Sub