|
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 |
ODBC Run-Time Error Handling Help
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 |
ODBC Run-Time Error Handling Help
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 |
ODBC Run-Time Error Handling Help
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 |
ODBC Run-Time Error Handling Help
for i = 29 to 36
if application.CountA(Worksheets("InvExport").Cells(i ,1).Resize(1,3) = 3 then SQLStr = "INSERT INTO [Inventory Export]" _ & "VALUES('" & Worksheets("InvExport").Cells(i,"A").Value & "','" _ & Worksheets("InvExport").Cells(i,"B").Value & "','" _ & Worksheets("InvExport").Cells(i,"C").Value & "')" MyCn.Execute SQLStr End if Next i I doubt there is anything you can do with ODBC errors since the are not produced by Excel. Easiest would be to put ON Error Resume Next at the top of your code and run it against a file that you know will cause an error. If it is suppressed, then you can use an errorhandler. I inclination is that it will not be suppressed. -- Regards, Tom Ogilvy "MikeElectricUtility" wrote: 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 |
ODBC Run-Time Error Handling Help
I ended up changing the If statement to:
If Application.Worksheets("InvExport").Cells(i, "A").Value < "" Then and it worked Great. Thanks so much because it also streamlined my code from having to have a separate SQL statement for each row!! "Tom Ogilvy" wrote: for i = 29 to 36 if application.CountA(Worksheets("InvExport").Cells(i ,1).Resize(1,3) = 3 then SQLStr = "INSERT INTO [Inventory Export]" _ & "VALUES('" & Worksheets("InvExport").Cells(i,"A").Value & "','" _ & Worksheets("InvExport").Cells(i,"B").Value & "','" _ & Worksheets("InvExport").Cells(i,"C").Value & "')" MyCn.Execute SQLStr End if Next i I doubt there is anything you can do with ODBC errors since the are not produced by Excel. Easiest would be to put ON Error Resume Next at the top of your code and run it against a file that you know will cause an error. If it is suppressed, then you can use an errorhandler. I inclination is that it will not be suppressed. -- Regards, Tom Ogilvy "MikeElectricUtility" wrote: 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 |
| All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com