error handling, stepping over a section of code

[CES]

all,
I'm trying to figure out how Access implements error handling. The function below has two possible areas where there can be an error.
First the delete table section could cause an error if the table doesn't already exist, secondly, the function should throw an error when it attempts to execute the create table cmd.Execute because the SQL statement has been remarked out.
If I add "On Error Resume Next" to the Delete Table section and the table actually does exist, the error thrown by the missing SQL statement in the create table section will not throw a error, which I would want it to do.
So my dilemma is, how do I tell the function to step over a specific section of code, but advised me of any subsequent errors.
I appreciate any help, Thanks in advance. -- CES

Public Function fnMakeTable(tableName As String, field As String) As String

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

'Deleat Table - I don't want to know about any errors
strSQL = "DROP TABLE " & tableName
cmd.CommandText = strSQL
cmd.Execute
strSQL = ""

' Create Table - I do want to know about any errors in this section
'strSQL = "CREATE TABLE " & tableName & "(" & field & ")"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow

End Function

 

[Marco Pagliero]

all,
I'm trying to figure out how Access implements error handling.
The function below has two possible areas where there can
be an error. First the delete table section could cause an error
if the table doesn't already exist, secondly, the function
should throw an error when it attempts to execute the create
table cmd.Execute because the SQL statement has been
remarked out.

With "on error goto 0" you can return to the normal situation after "on
error resume next"
Alternatively you can test the value of the system variable ERR after
every line where an error is possible.
ERR is the number (zero if no error happened), ERROR$(ERR) is the text.


'Deleat Table - I don't want to know about any errors
on error resume next
strSQL = "DROP TABLE " & tableName
cmd.CommandText = strSQL
cmd.Execute
strSQL = ""

One possibility: --------------------------
' Create Table - I do want to know about any errors in this
section
on error goto 0
'strSQL = "CREATE TABLE " & tableName & "(" & field & ")"
cmd.CommandText = strSQL
cmd.Execute

Another possibility: --------------------------
' Create Table - I do want to know about any errors in this
section
'strSQL = "CREATE TABLE " & tableName & "(" & field & ")"
cmd.CommandText = strSQL
cmd.Execute
if ERR then
MsgBox "Error number was: " & ERR & " Error was: " &
ERROR$(ERR)
end if

' refresh database window
Application.RefreshDatabaseWindow

End Function

Greetings
Marco P

 

[Wei Lu [MSFT]]

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.