At wits end - code breaks even with OnError Resume Next

[RD]

There seems to be absolutely nothing I can do to keep this damn thing
from breaking.

I even rebuilt the database, C&Ping the code into NotePad and C&Ping
it into a new module.

Break on Unhandled Errors is Ticked.

I have an Error handler.

I've handled the exact error being generated.

I've tried ignoring errors (Resume Next does nothing).

Background:
I've built this db that goes to other Access dbs, enumerates their
tables, queries, forms, etc. and writes their names and some
properties into various tables.

In these various external dbs, some tables, queries, etc. have
descriptions and some don't. Because the idea is to analyze these
other dbs, I want those descriptions if they're there. The problem
arises when the code hits a query that doesn't have a description. It
generates a Run-time Error 3270: Property not found. And I can't get
it to stop breaking.

There are hundreds of queries and most of them have descriptions.

At best my rep rides on this ...

at worst, my job.

A little help here?

Thanks,
RD

 

[Ken Snell [MVP]]

Can you post the code that is failing?

 

[Guest]

Unfortunately I don't have an idea as to why your code is breaking with the
error handler, but there is another way that you can go through the
properties without generating the error if that helps.

If you want most or all of the object properties, you can just loop through
the properties collection and write the object name, property name and the
property value to a log table. Later you can query out specific properties
if you like.

If you just want a few properties, you can use an if statement within your
loop to check to see if property name is equal to one of the properties you
are interested in, and if so log the prop name and value. Or, you could use
a select case statement to log the value in an appropriate field if you
wanted to do it more as a flat file.

Using any of those methods, you shouldn't run into errors because you'll
never be asking for a property that doesn't exist, you'll just be checking to
see if the property name is equal to one or more strings.

HTH, Ted Allen

 

[RD]

Sure. You want the whole module or just the area it's failing?

Also, I don't think the problem is with the code. I think it's with
Access. Although, if there's a work around, I'm good with that.

Thanks,
RD

 

[Ken Snell [MVP]]

Let's start with the procedure (sub or function) that is showing the "break"
and identify the code step on which the break is occurring.

 

[RD]

Let's start with the procedure (sub or function) that is showing the "break"
and identify the code step on which the break is occurring.

Ok. Here is the Function. The variables are declared up in the
Declarations section. References are:
VBA
Access 10 Object Library
OLE Automation
DAO 3.6
Office 10 Object Library

Look in the section labeled GetQueries:. I've put an asterisk on the
line that breaks. It actually gets through about 80 or 90 queries
before it breaks.


Function EnumObjects(mPath As String)

On Error GoTo ERH

'Get name of database
mPos = InStrRev(mPath, "\")
mLen = Len(mPath) - mPos
myDb = Right(mPath, mLen)

'Open databases without password ... causes error if password
required.
Set db1 = OpenDatabase(mPath, True)
Set db2 = CurrentDb()
GoTo GetTables

GotPassword: 'Open databases with password
pWord = InputBox("Enter password.", "Need Password")
Set db1 = OpenDatabase(mPath, True, True, "MS Access;PWD=" &
pWord)
Set db2 = CurrentDb()

GetTables:
sTag = "GetTables"
myOp = "Tables"
Set tdfs = db1.TableDefs
Set rs = db2.OpenRecordset("Tables", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
i = 0
For i = 0 To db1.TableDefs.Count - 1
If Left(tdfs(i).Name, 4) <> "mSys" Then
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = tdfs(i).Name
.Update
n = n + 1
End With
End If
Next i
rs.Close
Set rs = Nothing
Set tdfs = Nothing
i = 0
n = 0
sTag = ""

GetQueries:
On Error Resume Next
sTag = "GetQueries"
myOp = myOp & ",Queries"
Set qdfs = db1.QueryDefs
Set rs = db2.OpenRecordset("Queries", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
i = 0
For i = 0 To db1.QueryDefs.Count - 1
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = qdfs(i).Name
If Left(UCase(qdfs(i).SQL), 3) = "SEL" Then
.Fields("Type") = "S"
End If
If Left(qdfs(i).Name, 1) = "~" Then
.Fields("Desc") = "Support query."
Else
* .Fields("Desc") = qdfs(i).Properties("Description")
End If
.Fields("SQL") = qdfs(i).SQL
.Update
n = n + 1
End With
Next i
rs.Close
Set rs = Nothing
Set qdfs = Nothing
i = 0
n = 0
sTag = ""

GetForms:
On Error GoTo ERH
sTag = "GetForms"
myOp = myOp & ",Forms"
Set rs = db2.OpenRecordset("Forms", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
For Each frm In db1.Containers("Forms").Documents
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = frm.Name
.Update
n = n + 1
End With
Next frm
rs.Close
Set rs = Nothing
n = 0
sTag = ""

GetReports:
sTag = "GetReports"
myOp = myOp & ",Reports"
Set rs = db2.OpenRecordset("Reports", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
For Each rpt In db1.Containers("Reports").Documents
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = rpt.Name
.Update
n = n + 1
End With
Next rpt
rs.Close
Set rs = Nothing
n = 0
sTag = ""

GetMacros:
sTag = "GetMacros"
myOp = myOp & ",Macros"
Set rs = db2.OpenRecordset("Macros", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
For Each mcr In db1.Containers("Scripts").Documents
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = mcr.Name
.Fields("Desc") = mcr.Properties("Description").Value
.Update
n = n + 1
End With
Next mcr
rs.Close
Set rs = Nothing
n = 0
sTag = ""

GetModules:
sTag = "GetModules"
myOp = myOp & ",Modules"
Set rs = db2.OpenRecordset("Modules", dbOpenDynaset)
If rs.RecordCount = 0 Then
n = 0
Else
rs.MoveLast
n = rs.Fields("idx")
End If
For Each mMod In db1.Containers("Modules").Documents
With rs
.AddNew
.Fields("idx") = n + 1
.Fields("Source_Db") = myDb
.Fields("Name") = mMod.Name
End With
Next mMod
rs.Close
Set rs = Nothing
n = 0
sTag = ""

ExitPoint:
Set db1 = Nothing
Set db2 = Nothing
Exit Function

ERH:

' Catch password error, redirect.
If Err.Number = 3031 Then
Err.Clear
GoTo GotPassword
End If

' Catch "No record" and "Property not found" errors.
If Err.Number = 3021 Then Resume Next
If Err.Number = 3270 Then Resume Next

'Unhandled errors.
str = MyError(sTag)
Select Case str
Case "Yes"
Resume Next
Case "No"
GoTo ExitPoint
Case "Cancel"
MyRollBack
GoTo ExitPoint
Case Else
End Select
GoTo ExitPoint

End Function

 

[RD]

Well that's pretty much what I'm doing.

I go to a db, loop through the tables and write their names and the
name of the source db to a table. Almost none of the tables have
descriptions so I figured I could fill that in later. Then I loop
through the queries and write their names, source db names and
descriptions to a "Queries" table. And so on down the list: Forms,
Reports, etc.

The problem arises when I try to pull in the description of a query
that has no description. It looks to me like if there *is* no
description then the "Description" property never gets created. If
that's the case, I can't figure out how to check without generating
the "Property not found" error. The second I refer to the property,
the error occurs.

But, frankly, that's not really the point. I've done everything in my
power to stop Access from *breaking* to the debugger when it
shouldn't.

I guess my next step will be to try it on a different machine to see
if it's the database or my copy of Access.

 

[Ken Snell [MVP]]

The error that is occurring likely is because you're also trying to set the
value of the field in that statement.

Add a Dim statement:
Dim strTest As String

Change this line:
.Fields("Desc") = qdfs(i).Properties("Description")

to these lines:
strTest = qdfs(i).Properties("Description")
If Err.Number = 0 Then.Fields("Desc") =
qdfs(i).Properties("Description")
Err.Clear

 

[Guest]

Hi RD,

Hopefully Ken's last post will take care of your problem, but I did want to
clarify my earlier post a little because I think you misunderstood it.

Currently, you are looping through your query objects and accessing the
description with the following:

For i = 0 To db1.QueryDefs.Count - 1

Then, you go on to get the description by getting the description property
of the current QueryDef (which may or may not generate an error).

What I was trying to say earlier is that another way to do this would be to
loop through the properties within each query object, and then read the name
of the current property. Something like:

Dim db1 as DAO.Database
Dim qdf as QueryDef
Dim prop as Property

Set db1 = ....

For Each qdf in db1.QueryDefs
For Each prop in qdf.Properties
If Prop.Name = "Description" Then
{Do This}
Endif
Next
Next

Or, you could also use a select case statement in place of the If statement
to handle multiple properties, or you could just log all properties in a
table (if you were interested in them). In any case, you would not have to
worry about generating errors because you would never be trying to access a
property directly if it didn't exist.

-Ted Allen

 

[RD]

I've tried that. I'll try it with your code and see what happens.
I'll get back to you in a bit.

 

[RD]

Heh ... btween my last post and checking the group I stubbed out of my
that function and built a sub to do that very thing. The way I took
this one was to enumerate all the properties of all the queries in
just one of these dbs.

It was a large table.

I'm going to check out Ken's methos and see if it helps.

 

[RD]

Nope. It is as I suspected. The minute the code references the
property it breaks. I don't know how one would go about trying to
find out if a property exists without trying to reference it.

And ... all this is still pretty much beside the point in that it
shouldn't be breaking to the debugger in the first place. Error
handling in this db seems to be non-existent. That is to say that the
db is ignoring all atempts at error handling.

I feel like Yosemite Sam trying to make his camel stop once he's got
it going. Or vice versa.

"When I say whoa ... I mean WHOA!!!"

I think I'm going to have to move this and a few of these source dbs
to a different machine and see if it's just this db or if Access is
some how goofed up.

 

[Dirk Goldgar]

Nope. It is as I suspected. The minute the code references the
property it breaks. I don't know how one would go about trying to
find out if a property exists without trying to reference it.

And ... all this is still pretty much beside the point in that it
shouldn't be breaking to the debugger in the first place. Error
handling in this db seems to be non-existent. That is to say that the
db is ignoring all atempts at error handling.

I feel like Yosemite Sam trying to make his camel stop once he's got
it going. Or vice versa.

"When I say whoa ... I mean WHOA!!!"

I think I'm going to have to move this and a few of these source dbs
to a different machine and see if it's just this db or if Access is
some how goofed up.

Hey, RD. I think it's your use of GoTo in your error-handling code.
You should use Resume to get back into the main logic sequence and
restore the error-handler that was in effect at the time the error was
raised. When an error transfers control to your error-handler at ERH, I
believe that the error-handling that was in effect at that moment is
"popped" off the error-handler stack, and not reinstated unless you use
the Resume statement.

I tested this code:

'----- start example code -----
Sub TestErr()

On Error GoTo ERH

Dim i As Integer

For i = 1 To 5
Err.Raise i, , "Error " & i
AGAIN:
Next i


Exit_Point:
Exit Sub

ERH:
Debug.Print Err.Number, Err.Description
GoTo AGAIN

End Sub
'----- end example code -----

The result of executing TestErr was that "1 Error 1" was printed
in the Immediate window, and then I got a standard Access error dialog
for Run-time error '2': Error 2.

But if I change the lin in ERH from "GoTo AGAIN" to

Resume AGAIN

Then I get no error dialog, just five lines in the Immediate window:

1 Error 1
2 Error 2
3 Error 3
4 Error 4
5 Error 5

In the case of your code, my guess is that you're getting the password
error, and your error-handler is using GoTo to transfer execution back
to the label "GotPassword" -- but now your error-handler is no longer
enabled, because you didn't use Resume.

 

[Ken Snell [MVP]]

Thanks, Dirk. I recall that you once gave me this solution for a similar
problem that I had in a database as well, so I should have seen its use
again here! < g >

 

[Dirk Goldgar]

Thanks, Dirk. I recall that you once gave me this solution for a
similar problem that I had in a database as well, so I should have
seen its use again here! < g >

You remember better than I do, Ken -- I don't remember that at all. And
I wasn't sure about this phenomenon until I tested it. Alas, I think my
"forget rate" has passed my "remember rate".

 

[RD]

DOH!!!! <smacks face with hand, hangs head in shame>

Geez, what a bonehead, newby mistake! I *KNOW* one uses Resume in the
error handler and not GoTo! I just ran through 9 dbs in less than a
minute after changing that one little thing. Not a single break and
all the data checks out.

Big time thanks to Dirk, Ken, Ted and David.

Regards,
RD
(Stupid camel)

 

[Guest]

Yeah, it's a lot of data if you write it all. Often I will code it such that
it uses an If statement to check to see if the prop name is equal to one that
I am interested in, and if so write the data, otherwise just go to the next
prop. The code will take longer than your other method though, which I was
glad to see that you got working on your other post.

-Ted Allen