or if all the fields need to be filled

if Application.CountA(Range("A29:C29")) = 3 then


end if

--
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