ODBC Run-Time Error Handling Help

G

Guest

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
 
G

Guest

or if all the fields need to be filled

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


end if
 
G

Guest

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

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inserting Excel Data Range into Access Table 2
Excel export to Access - Run-time error 1004 6
exporting to excel 21
ADODB SQL help 1
Data from Excel to Access 4
Data from Excel to Access Table 12
Syntax error in INSERT INTO statement 10
Run time error? 6

Top