Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC Run-Time Error Handling Help
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 1004 - General ODBC Error | Excel Programming | |||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch" | Excel Programming | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Error handling with a handling routine | Excel Programming |