By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,276 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,276 IT Pros & Developers. It's quick & easy.

A wrapper for DAO! Well, almost...

P: n/a
To All,

First, some of your replies to me have been posted through
DevelopersDex, and these are NOT posted on USENET for the world to
see. DevelopersDex appears to be trying to hijack USENET, though there
may be some more benign explanation that escapes me. If you want the
world to see your replies, then you should post them directly to
USENET. However, I'll see them either way.

After years of frustration with DAO, it dawned on me that it might be
possible to write a reasonable wrapper for DAO to hide the mess! I did
just that and attached it below. It sort of works enough for my
application, but fixing its remaining problems would help a LOT.

The problems are explained in comments contained therein. Mostly they
have to do with interfacing with the OpenRecordset method. Apparently,
OpenRecordset doesn't handle optional arguments in any
reasonable/classical way. Where the arguments are themselves passed as
optional from the wrapper, it appears that it may be necessary to code
some large number of OpenRecordset usages with all possible
combinations of arguments. Then, there is the problem of what to put
into the arguments that you don't care about, but which lead an
argument that you wish to include. Apparently these MUST be stated,
but to illustrate the depth of the problem, the first argument, the
"type" argument, defaults to various values depending on a variety of
poorly documented things. Oops, I think I just stepped in some of the
mess that I was trying to hide!

Basically, if I can get this to work smoothly, this provides a
classical VB (not VBA) file-like interface, while exposing the
recordset object for random positioning, EOF detection, and other
important things.

Note that enough is working for the little test routine to run, but
NOT enough to recognize the vbReadOnly option.

PLEASE, all suggestions for improvement would be GREATLY appreciated.
................................

' modDAOinterface is Coypright 2004 by The Richfield Family
' with all rights reserved.

Option Compare Text
Option Explicit
Option Base 1

Const MaxFiles = 10
Const MaxFields = 10

Dim QueryOpen(MaxFiles) As Boolean ' Has it been opened?
Public QueryFile(MaxFiles) As Recordset ' The recordset.
Dim FieldName$(MaxFiles, MaxFields) ' Field nnames of
ParamArry Args.
Dim db(MaxFiles) As Database
Dim qry(MaxFiles) As QueryDef
Dim tbl(MaxFiles) As TableDef

' Queries and subforms are very strange things in Access, often
deciding not
' to evaluate fields or compute expressions unless they are displayed
on the
' screen or are used to modify other database objects.
' This throws a monkey wrench into complex functions with beneficial
side
' effects that may not be apparent to the database engine.
' To overcome this, these simple routines have been implemented to
read a
' query or table as though it were a sequential file.
' This forces "dumb" operation where everything is read and evaluated.

Sub OpenQuery(QueryName$, File#, Optional Database As Variant, _
Optional Mode As Variant, Optional Access As Variant, _
Optional Locks As Variant, Optional SQL As Variant, _
Optional Fields As Variant)

' Mode, Access, and Locks, are inop pending more info on
OpenRecordset.

Dim Arg& ' The number of the argument being
considered.
Dim FieldNum& ' Ordinal number of the field.
Dim RetVal As Variant

On Error GoTo Error_Handler

If QueryOpen(File#) Then _
Call CloseQuery(File#)

If IsMissing(Database) Then
Set db(File#) = CurrentDb()
Else
Set db(File#) = OpenDatabase(CStr(Database))
End If

On Error GoTo TryTable
' The following doesn't seem to work for Jet MDBs.
Set qry(File#) = db(File#).QueryDefs(QueryName$)

On Error GoTo Error_Handler

' Type:=Mode, Options:=Access, LockEdit:=Locks
If IsMissing(SQL) Then
' The following line needs at least one numeric operation.
' The following line gives an error 3210 Invalid Operation.
Set QueryFile(File#) = qry(File#).OpenRecordset(dbOpenTable)
Else
Set QueryFile(File#) = qry(File#).OpenRecordset(CStr(SQL))
End If
GoTo GotIt

TryTable:
Resume NextLine

NextLine:
On Error GoTo Error_Handler
Set tbl(File#) = db(File#).TableDefs(QueryName$)

' Arguments to the following OpenRecordset don't seem to work.
' (Type:=Mode, Options:=Access, LockEdits:=Locks)
If IsMissing(SQL) Then
Set QueryFile(File#) = tbl(File#).OpenRecordset
Else
Set QueryFile(File#) = tbl(File#).OpenRecordset(CStr(SQL))
End If

GotIt:

' Suck in the field names.

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
FieldName$(File#, FieldNum&) = Fields(Arg&)
' The following simply validates the field name here and now.
If Not QueryFile(File#).EOF Then _
RetVal = QueryFile(File#).Fields(FieldName$(File#,
FieldNum&))
Next Arg&

QueryOpen(File#) = True

Exit Sub

Error_Handler:
If Error_Handler("OpenQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub ReadQuery(File#, ParamArray Fields() As Variant)

' Always test for EOF before calling, using:
' If QueryFile(1#).EOF Then
' Call CloseQuery(1#)

Dim Arg&
Dim FieldNum& ' Ordinal number of the field.

On Error GoTo Error_Handler

If Not QueryOpen(File#) Then
Call CloseQuery(File#)
Error 17
End If

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
Fields(Arg&) = QueryFile(File#). _
Fields(FieldName$(File#, FieldNum&)).Value
Next Arg&

QueryFile(File#).MoveNext

Exit Sub

Error_Handler:
If Error_Handler("ReadQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub CloseQuery(File#)

On Error GoTo Error_Handler

QueryFile(File#).Close
Set qry(File#) = Nothing
Set tbl(File#) = Nothing
Set db(File#) = Nothing
QueryOpen(File#) = False

Exit Sub

Error_Handler:
If Error_Handler("CloseQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Function FreeQuery#()

Dim File&

On Error GoTo Error_Handler

For File& = 1 To MaxFiles
If Not QueryOpen(File&) Then
FreeQuery = File&
Exit For
End If
Next

Exit Function

Error_Handler:
If Error_Handler("FreeQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Function

Public Sub TestQuery()

' This is just a stupid test routine.

Dim FileNumber#
Dim Symptom$

FileNumber# = FreeQuery

OpenQuery "Symptoms", File:=FileNumber#, Locks:=dbReadOnly, _
Fields:=Array("Symptom")
ReadQuery FileNumber#, Symptom$
Stop ' and check that the first Symptom was read in OK.
CloseQuery FileNumber#

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Steve,
But . . . ADO isn't good enough? I bailed on DAO a while ago. Most of my
stuff either runs ADODB.cmd.execute (some SQL statememt) or opens an
ADODB.Recordset. And . . . if you are going to force the rdbms to look at
every row of the result then it seems lame to bother with an rdbms. You
would do just as well to open VB data files for sequential access and code
your own search routines.
But since you like pissing money away on pointless tasks--go play with
Oracle's PL/SQL and Oracle Forms 8. Lots of bugs to work around there.

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google.c om...
To All,

First, some of your replies to me have been posted through
DevelopersDex, and these are NOT posted on USENET for the world to
see. DevelopersDex appears to be trying to hijack USENET, though there
may be some more benign explanation that escapes me. If you want the
world to see your replies, then you should post them directly to
USENET. However, I'll see them either way.

After years of frustration with DAO, it dawned on me that it might be
possible to write a reasonable wrapper for DAO to hide the mess! I did
just that and attached it below. It sort of works enough for my
application, but fixing its remaining problems would help a LOT.

The problems are explained in comments contained therein. Mostly they
have to do with interfacing with the OpenRecordset method. Apparently,
OpenRecordset doesn't handle optional arguments in any
reasonable/classical way. Where the arguments are themselves passed as
optional from the wrapper, it appears that it may be necessary to code
some large number of OpenRecordset usages with all possible
combinations of arguments. Then, there is the problem of what to put
into the arguments that you don't care about, but which lead an
argument that you wish to include. Apparently these MUST be stated,
but to illustrate the depth of the problem, the first argument, the
"type" argument, defaults to various values depending on a variety of
poorly documented things. Oops, I think I just stepped in some of the
mess that I was trying to hide!

Basically, if I can get this to work smoothly, this provides a
classical VB (not VBA) file-like interface, while exposing the
recordset object for random positioning, EOF detection, and other
important things.

Note that enough is working for the little test routine to run, but
NOT enough to recognize the vbReadOnly option.

PLEASE, all suggestions for improvement would be GREATLY appreciated.
...............................

' modDAOinterface is Coypright 2004 by The Richfield Family
' with all rights reserved.

Option Compare Text
Option Explicit
Option Base 1

Const MaxFiles = 10
Const MaxFields = 10

Dim QueryOpen(MaxFiles) As Boolean ' Has it been opened?
Public QueryFile(MaxFiles) As Recordset ' The recordset.
Dim FieldName$(MaxFiles, MaxFields) ' Field nnames of
ParamArry Args.
Dim db(MaxFiles) As Database
Dim qry(MaxFiles) As QueryDef
Dim tbl(MaxFiles) As TableDef

' Queries and subforms are very strange things in Access, often
deciding not
' to evaluate fields or compute expressions unless they are displayed
on the
' screen or are used to modify other database objects.
' This throws a monkey wrench into complex functions with beneficial
side
' effects that may not be apparent to the database engine.
' To overcome this, these simple routines have been implemented to
read a
' query or table as though it were a sequential file.
' This forces "dumb" operation where everything is read and evaluated.

Sub OpenQuery(QueryName$, File#, Optional Database As Variant, _
Optional Mode As Variant, Optional Access As Variant, _
Optional Locks As Variant, Optional SQL As Variant, _
Optional Fields As Variant)

' Mode, Access, and Locks, are inop pending more info on
OpenRecordset.

Dim Arg& ' The number of the argument being
considered.
Dim FieldNum& ' Ordinal number of the field.
Dim RetVal As Variant

On Error GoTo Error_Handler

If QueryOpen(File#) Then _
Call CloseQuery(File#)

If IsMissing(Database) Then
Set db(File#) = CurrentDb()
Else
Set db(File#) = OpenDatabase(CStr(Database))
End If

On Error GoTo TryTable
' The following doesn't seem to work for Jet MDBs.
Set qry(File#) = db(File#).QueryDefs(QueryName$)

On Error GoTo Error_Handler

' Type:=Mode, Options:=Access, LockEdit:=Locks
If IsMissing(SQL) Then
' The following line needs at least one numeric operation.
' The following line gives an error 3210 Invalid Operation.
Set QueryFile(File#) = qry(File#).OpenRecordset(dbOpenTable)
Else
Set QueryFile(File#) = qry(File#).OpenRecordset(CStr(SQL))
End If
GoTo GotIt

TryTable:
Resume NextLine

NextLine:
On Error GoTo Error_Handler
Set tbl(File#) = db(File#).TableDefs(QueryName$)

' Arguments to the following OpenRecordset don't seem to work.
' (Type:=Mode, Options:=Access, LockEdits:=Locks)
If IsMissing(SQL) Then
Set QueryFile(File#) = tbl(File#).OpenRecordset
Else
Set QueryFile(File#) = tbl(File#).OpenRecordset(CStr(SQL))
End If

GotIt:

' Suck in the field names.

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
FieldName$(File#, FieldNum&) = Fields(Arg&)
' The following simply validates the field name here and now.
If Not QueryFile(File#).EOF Then _
RetVal = QueryFile(File#).Fields(FieldName$(File#,
FieldNum&))
Next Arg&

QueryOpen(File#) = True

Exit Sub

Error_Handler:
If Error_Handler("OpenQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub ReadQuery(File#, ParamArray Fields() As Variant)

' Always test for EOF before calling, using:
' If QueryFile(1#).EOF Then
' Call CloseQuery(1#)

Dim Arg&
Dim FieldNum& ' Ordinal number of the field.

On Error GoTo Error_Handler

If Not QueryOpen(File#) Then
Call CloseQuery(File#)
Error 17
End If

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
Fields(Arg&) = QueryFile(File#). _
Fields(FieldName$(File#, FieldNum&)).Value
Next Arg&

QueryFile(File#).MoveNext

Exit Sub

Error_Handler:
If Error_Handler("ReadQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub CloseQuery(File#)

On Error GoTo Error_Handler

QueryFile(File#).Close
Set qry(File#) = Nothing
Set tbl(File#) = Nothing
Set db(File#) = Nothing
QueryOpen(File#) = False

Exit Sub

Error_Handler:
If Error_Handler("CloseQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Function FreeQuery#()

Dim File&

On Error GoTo Error_Handler

For File& = 1 To MaxFiles
If Not QueryOpen(File&) Then
FreeQuery = File&
Exit For
End If
Next

Exit Function

Error_Handler:
If Error_Handler("FreeQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Function

Public Sub TestQuery()

' This is just a stupid test routine.

Dim FileNumber#
Dim Symptom$

FileNumber# = FreeQuery

OpenQuery "Symptoms", File:=FileNumber#, Locks:=dbReadOnly, _
Fields:=Array("Symptom")
ReadQuery FileNumber#, Symptom$
Stop ' and check that the first Symptom was read in OK.
CloseQuery FileNumber#

End Sub

Nov 13 '05 #2

P: n/a
Steve,
I replied to this elsewhere and having read my reply became self-conscious
that others may interpret my words as a bit harsher than I intended. I
didn't mean to sound like I was yelling. Still, all that work to fiddle
with "the mess" in DAO seems silly.

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google.c om...
To All,

First, some of your replies to me have been posted through
DevelopersDex, and these are NOT posted on USENET for the world to
see. DevelopersDex appears to be trying to hijack USENET, though there
may be some more benign explanation that escapes me. If you want the
world to see your replies, then you should post them directly to
USENET. However, I'll see them either way.

After years of frustration with DAO, it dawned on me that it might be
possible to write a reasonable wrapper for DAO to hide the mess! I did
just that and attached it below. It sort of works enough for my
application, but fixing its remaining problems would help a LOT.

The problems are explained in comments contained therein. Mostly they
have to do with interfacing with the OpenRecordset method. Apparently,
OpenRecordset doesn't handle optional arguments in any
reasonable/classical way. Where the arguments are themselves passed as
optional from the wrapper, it appears that it may be necessary to code
some large number of OpenRecordset usages with all possible
combinations of arguments. Then, there is the problem of what to put
into the arguments that you don't care about, but which lead an
argument that you wish to include. Apparently these MUST be stated,
but to illustrate the depth of the problem, the first argument, the
"type" argument, defaults to various values depending on a variety of
poorly documented things. Oops, I think I just stepped in some of the
mess that I was trying to hide!

Basically, if I can get this to work smoothly, this provides a
classical VB (not VBA) file-like interface, while exposing the
recordset object for random positioning, EOF detection, and other
important things.

Note that enough is working for the little test routine to run, but
NOT enough to recognize the vbReadOnly option.

PLEASE, all suggestions for improvement would be GREATLY appreciated.
...............................

' modDAOinterface is Coypright 2004 by The Richfield Family
' with all rights reserved.

Option Compare Text
Option Explicit
Option Base 1

Const MaxFiles = 10
Const MaxFields = 10

Dim QueryOpen(MaxFiles) As Boolean ' Has it been opened?
Public QueryFile(MaxFiles) As Recordset ' The recordset.
Dim FieldName$(MaxFiles, MaxFields) ' Field nnames of
ParamArry Args.
Dim db(MaxFiles) As Database
Dim qry(MaxFiles) As QueryDef
Dim tbl(MaxFiles) As TableDef

' Queries and subforms are very strange things in Access, often
deciding not
' to evaluate fields or compute expressions unless they are displayed
on the
' screen or are used to modify other database objects.
' This throws a monkey wrench into complex functions with beneficial
side
' effects that may not be apparent to the database engine.
' To overcome this, these simple routines have been implemented to
read a
' query or table as though it were a sequential file.
' This forces "dumb" operation where everything is read and evaluated.

Sub OpenQuery(QueryName$, File#, Optional Database As Variant, _
Optional Mode As Variant, Optional Access As Variant, _
Optional Locks As Variant, Optional SQL As Variant, _
Optional Fields As Variant)

' Mode, Access, and Locks, are inop pending more info on
OpenRecordset.

Dim Arg& ' The number of the argument being
considered.
Dim FieldNum& ' Ordinal number of the field.
Dim RetVal As Variant

On Error GoTo Error_Handler

If QueryOpen(File#) Then _
Call CloseQuery(File#)

If IsMissing(Database) Then
Set db(File#) = CurrentDb()
Else
Set db(File#) = OpenDatabase(CStr(Database))
End If

On Error GoTo TryTable
' The following doesn't seem to work for Jet MDBs.
Set qry(File#) = db(File#).QueryDefs(QueryName$)

On Error GoTo Error_Handler

' Type:=Mode, Options:=Access, LockEdit:=Locks
If IsMissing(SQL) Then
' The following line needs at least one numeric operation.
' The following line gives an error 3210 Invalid Operation.
Set QueryFile(File#) = qry(File#).OpenRecordset(dbOpenTable)
Else
Set QueryFile(File#) = qry(File#).OpenRecordset(CStr(SQL))
End If
GoTo GotIt

TryTable:
Resume NextLine

NextLine:
On Error GoTo Error_Handler
Set tbl(File#) = db(File#).TableDefs(QueryName$)

' Arguments to the following OpenRecordset don't seem to work.
' (Type:=Mode, Options:=Access, LockEdits:=Locks)
If IsMissing(SQL) Then
Set QueryFile(File#) = tbl(File#).OpenRecordset
Else
Set QueryFile(File#) = tbl(File#).OpenRecordset(CStr(SQL))
End If

GotIt:

' Suck in the field names.

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
FieldName$(File#, FieldNum&) = Fields(Arg&)
' The following simply validates the field name here and now.
If Not QueryFile(File#).EOF Then _
RetVal = QueryFile(File#).Fields(FieldName$(File#,
FieldNum&))
Next Arg&

QueryOpen(File#) = True

Exit Sub

Error_Handler:
If Error_Handler("OpenQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub ReadQuery(File#, ParamArray Fields() As Variant)

' Always test for EOF before calling, using:
' If QueryFile(1#).EOF Then
' Call CloseQuery(1#)

Dim Arg&
Dim FieldNum& ' Ordinal number of the field.

On Error GoTo Error_Handler

If Not QueryOpen(File#) Then
Call CloseQuery(File#)
Error 17
End If

FieldNum& = 0
For Arg& = LBound(Fields) To UBound(Fields)
FieldNum& = FieldNum& + 1
Fields(Arg&) = QueryFile(File#). _
Fields(FieldName$(File#, FieldNum&)).Value
Next Arg&

QueryFile(File#).MoveNext

Exit Sub

Error_Handler:
If Error_Handler("ReadQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Sub CloseQuery(File#)

On Error GoTo Error_Handler

QueryFile(File#).Close
Set qry(File#) = Nothing
Set tbl(File#) = Nothing
Set db(File#) = Nothing
QueryOpen(File#) = False

Exit Sub

Error_Handler:
If Error_Handler("CloseQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Sub

Function FreeQuery#()

Dim File&

On Error GoTo Error_Handler

For File& = 1 To MaxFiles
If Not QueryOpen(File&) Then
FreeQuery = File&
Exit For
End If
Next

Exit Function

Error_Handler:
If Error_Handler("FreeQuery", Err) = acDataErrDisplay Then
On Error GoTo 0

Stop: Resume ' Press [F8] twice to view the problem.

End If

End Function

Public Sub TestQuery()

' This is just a stupid test routine.

Dim FileNumber#
Dim Symptom$

FileNumber# = FreeQuery

OpenQuery "Symptoms", File:=FileNumber#, Locks:=dbReadOnly, _
Fields:=Array("Symptom")
ReadQuery FileNumber#, Symptom$
Stop ' and check that the first Symptom was read in OK.
CloseQuery FileNumber#

End Sub

Nov 13 '05 #3

P: n/a
In response to some off-USENET replies,

1. DAO, ADO, and the other similar solutions all have the same
problem - the coding depends on extensive hidden subtlties that would
be best hidden from all but those who maintain them. What seems to be
needed is a simple wrapper that could easily be adapted to any
underlying DB for those situations where simple sequential access or
something pretty close to that is needed. That was my general goal -
to fill in the gap where queries and subforms fall flat on their face,
as a number of prior postings here of the problems I was having show.
My choice of DAO on only motivated by its ubiquitous availability.

Note that the wrapper could trivially be changed to ADO in a few
minutes - MUCH less effort than changing a bunch of usage sprinkled
throughout a large program.

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question. Suppose hypothyroidism floated to the top but the
patient made no mention of asthma, a common symptom. To resolve this
question, the patient might add something like "I do not have asthma."
to his statement, which would then resolve this issue when his
statement is re-analyzed, and also reduce the score for
hypothyroidism.

Hence, it is really irrelivant that it is written using DAO, or that
its main application is to read queries sequentially.

What IS important is that it can trivially be ported to any DB, and
that it makes reading queries sequentially both simple and readable.
Also, it contains internal checks that most programmers wouldn't
bother "cluttering" up their program with, so that things don't later
get "wound around the axle" for some difficult to determine reason.
Finally, when you go to type in a call to one of these routines, it
displays all of the options right there, horizontally, rather than
hiding them in a nearly infinitely long vertical list that mostly
contains things that you will NEVER EVER use.

Steve Richfield
Nov 13 '05 #4

P: n/a
Well, frankly, Steve, I don't see any "mess" with DAO. It is, in fact,
relatively simple to learn and use -- I've been using it daily, without the
need for any "wrapper" since shortly after Access was first released. Like
almost anything else in computers, to make the best use of it, "you've gotta
know what you're doing".

Databases aren't just "files", and you can't really simplify them to the
point that they seem so without removing all the functionality they provide
that files do not provide. Without some specific examples, I'd have to say
that your stated objections are not things I have encountered.

Seems useless to me to design a new user interface for something that
already has a user interface. To use the full functionality of the
underlying DAO, you'll have to duplicate all the information that DAO
requires. And, with DAO, I can find plenty of information from various
sources -- which will be unlikely with a new interface.

Good luck with your project.

Larry Linson
Microsoft Access MVP

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google.c om...
In response to some off-USENET replies,

1. DAO, ADO, and the other similar solutions all have the same
problem - the coding depends on extensive hidden subtlties that would
be best hidden from all but those who maintain them. What seems to be
needed is a simple wrapper that could easily be adapted to any
underlying DB for those situations where simple sequential access or
something pretty close to that is needed. That was my general goal -
to fill in the gap where queries and subforms fall flat on their face,
as a number of prior postings here of the problems I was having show.
My choice of DAO on only motivated by its ubiquitous availability.

Note that the wrapper could trivially be changed to ADO in a few
minutes - MUCH less effort than changing a bunch of usage sprinkled
throughout a large program.

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question. Suppose hypothyroidism floated to the top but the
patient made no mention of asthma, a common symptom. To resolve this
question, the patient might add something like "I do not have asthma."
to his statement, which would then resolve this issue when his
statement is re-analyzed, and also reduce the score for
hypothyroidism.

Hence, it is really irrelivant that it is written using DAO, or that
its main application is to read queries sequentially.

What IS important is that it can trivially be ported to any DB, and
that it makes reading queries sequentially both simple and readable.
Also, it contains internal checks that most programmers wouldn't
bother "cluttering" up their program with, so that things don't later
get "wound around the axle" for some difficult to determine reason.
Finally, when you go to type in a call to one of these routines, it
displays all of the options right there, horizontally, rather than
hiding them in a nearly infinitely long vertical list that mostly
contains things that you will NEVER EVER use.

Steve Richfield

Nov 13 '05 #5

P: n/a
Actually, both DAO and ADO have messes worth wrapping. Some of them are the
same messes and can be wrapped in the same API to make them somewhat
interchangeable.

To me, the issue is not DAO vs ADO, but the fact that both are designed to be
very general purpose, and make you repeatedly to the same janitorial work to
deal all the layers and objects to do very simple tasks.

Now, I don't make heavy duty wrappers, myself. I make light-weight wrappers
for most every project to handle the tasks I'm doing repetitively in that app,
but I almost always use wrappers these days.

On Thu, 11 Nov 2004 11:20:03 -0500, "Alan Webb" <kn*****@hotmail.com> wrote:
Steve,
I replied to this elsewhere and having read my reply became self-conscious
that others may interpret my words as a bit harsher than I intended. I
didn't mean to sound like I was yelling. Still, all that work to fiddle
with "the mess" in DAO seems silly.

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google. com...
To All,


Nov 13 '05 #6

P: n/a
I don't find it pointless at all to wrap databas interfaces. It's nice to
abstrct away all the kruft. For instance, when I want to use a transaction, I
don't want to deal with setting up the workspace object, reopening the
database in the new workspace after getting its name from CurrentDb, etc. I'd
rather have a clsDbHandler with a method like ...

Public Sub ManageCurrentDb(ByVal UseSeparateWorkspace As Boolean)

I also like to have a clsSelectQueryHandler that automatically gives me a
temporary querydef member, and has a method to generate a recordset for me,
and hold that in another member. The error handling all happens within
generateRecordset, and only has to be coded in one place. All these wrappers
automatically close things and set them to Nothing in the proper order. The
database handler can't go out of scope early because the subordinate handlers
hold a refrence to it.

I find that this strategy saves vast amounts of time. I think the biggest
savings is in the encapsulation of error handling around the most commonly
used database activities.

On Fri, 12 Nov 2004 00:38:44 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
Well, frankly, Steve, I don't see any "mess" with DAO. It is, in fact,
relatively simple to learn and use -- I've been using it daily, without the
need for any "wrapper" since shortly after Access was first released. Like
almost anything else in computers, to make the best use of it, "you've gotta
know what you're doing".

Databases aren't just "files", and you can't really simplify them to the
point that they seem so without removing all the functionality they provide
that files do not provide. Without some specific examples, I'd have to say
that your stated objections are not things I have encountered.

Seems useless to me to design a new user interface for something that
already has a user interface. To use the full functionality of the
underlying DAO, you'll have to duplicate all the information that DAO
requires. And, with DAO, I can find plenty of information from various
sources -- which will be unlikely with a new interface.

Good luck with your project.

Larry Linson
Microsoft Access MVP

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google. com...
In response to some off-USENET replies,

1. DAO, ADO, and the other similar solutions all have the same
problem - the coding depends on extensive hidden subtlties that would
be best hidden from all but those who maintain them. What seems to be
needed is a simple wrapper that could easily be adapted to any
underlying DB for those situations where simple sequential access or
something pretty close to that is needed. That was my general goal -
to fill in the gap where queries and subforms fall flat on their face,
as a number of prior postings here of the problems I was having show.
My choice of DAO on only motivated by its ubiquitous availability.

Note that the wrapper could trivially be changed to ADO in a few
minutes - MUCH less effort than changing a bunch of usage sprinkled
throughout a large program.

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question. Suppose hypothyroidism floated to the top but the
patient made no mention of asthma, a common symptom. To resolve this
question, the patient might add something like "I do not have asthma."
to his statement, which would then resolve this issue when his
statement is re-analyzed, and also reduce the score for
hypothyroidism.

Hence, it is really irrelivant that it is written using DAO, or that
its main application is to read queries sequentially.

What IS important is that it can trivially be ported to any DB, and
that it makes reading queries sequentially both simple and readable.
Also, it contains internal checks that most programmers wouldn't
bother "cluttering" up their program with, so that things don't later
get "wound around the axle" for some difficult to determine reason.
Finally, when you go to type in a call to one of these routines, it
displays all of the options right there, horizontally, rather than
hiding them in a nearly infinitely long vertical list that mostly
contains things that you will NEVER EVER use.

Steve Richfield


Nov 13 '05 #7

P: n/a
Steve,
Now that I have some idea of what you are doing . . . an rdbms is an
inconvenience to you instead of an aid. Your problems with DAO sound like
confusion with relational database concepts and poor database design as much
as they might be with a particular means of connecting to a database.

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google.c om...
In response to some off-USENET replies,

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question.

Nov 13 '05 #8

P: n/a
Steve, neither have I found it necessary nor useful to employ classes with
DAO, but perhaps your method of working with databases leads to more "kruft"
that you need to "abstract away". Our "little desktop database" surprised so
many people, even its vendor, with the broad range and capabilities; there
are so many things you can do with it, that no one's needs are much of a
surprise.

A fellow MVP, Tom Ellison, and I would be constantly at odds if we didn't
each realize that what we do with Access requires very different approaches.
The work that he does requires very, very complex SQL; the work that I
generally do requires only very simple SQL.

Still, after using them just a few times, all the "preliminaries" you
describe just became second nature. I key those in without even having to
think much about it.

Larry Linson
Microsoft Access MVP

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1m********************************@4ax.com...
I don't find it pointless at all to wrap databas interfaces. It's nice to
abstrct away all the kruft. For instance, when I want to use a transaction, I don't want to deal with setting up the workspace object, reopening the
database in the new workspace after getting its name from CurrentDb, etc. I'd rather have a clsDbHandler with a method like ...

Public Sub ManageCurrentDb(ByVal UseSeparateWorkspace As Boolean)

I also like to have a clsSelectQueryHandler that automatically gives me a
temporary querydef member, and has a method to generate a recordset for me, and hold that in another member. The error handling all happens within
generateRecordset, and only has to be coded in one place. All these wrappers automatically close things and set them to Nothing in the proper order. The database handler can't go out of scope early because the subordinate handlers hold a refrence to it.

I find that this strategy saves vast amounts of time. I think the biggest
savings is in the encapsulation of error handling around the most commonly
used database activities.

On Fri, 12 Nov 2004 00:38:44 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
Well, frankly, Steve, I don't see any "mess" with DAO. It is, in fact,
relatively simple to learn and use -- I've been using it daily, without theneed for any "wrapper" since shortly after Access was first released. Likealmost anything else in computers, to make the best use of it, "you've gottaknow what you're doing".

Databases aren't just "files", and you can't really simplify them to the
point that they seem so without removing all the functionality they providethat files do not provide. Without some specific examples, I'd have to saythat your stated objections are not things I have encountered.

Seems useless to me to design a new user interface for something that
already has a user interface. To use the full functionality of the
underlying DAO, you'll have to duplicate all the information that DAO
requires. And, with DAO, I can find plenty of information from various
sources -- which will be unlikely with a new interface.

Good luck with your project.

Larry Linson
Microsoft Access MVP

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google. com...
In response to some off-USENET replies,

1. DAO, ADO, and the other similar solutions all have the same
problem - the coding depends on extensive hidden subtlties that would
be best hidden from all but those who maintain them. What seems to be
needed is a simple wrapper that could easily be adapted to any
underlying DB for those situations where simple sequential access or
something pretty close to that is needed. That was my general goal -
to fill in the gap where queries and subforms fall flat on their face,
as a number of prior postings here of the problems I was having show.
My choice of DAO on only motivated by its ubiquitous availability.

Note that the wrapper could trivially be changed to ADO in a few
minutes - MUCH less effort than changing a bunch of usage sprinkled
throughout a large program.

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question. Suppose hypothyroidism floated to the top but the
patient made no mention of asthma, a common symptom. To resolve this
question, the patient might add something like "I do not have asthma."
to his statement, which would then resolve this issue when his
statement is re-analyzed, and also reduce the score for
hypothyroidism.

Hence, it is really irrelivant that it is written using DAO, or that
its main application is to read queries sequentially.

What IS important is that it can trivially be ported to any DB, and
that it makes reading queries sequentially both simple and readable.
Also, it contains internal checks that most programmers wouldn't
bother "cluttering" up their program with, so that things don't later
get "wound around the axle" for some difficult to determine reason.
Finally, when you go to type in a call to one of these routines, it
displays all of the options right there, horizontally, rather than
hiding them in a nearly infinitely long vertical list that mostly
contains things that you will NEVER EVER use.

Steve Richfield

Nov 13 '05 #9

P: n/a
I don't think we actually use databases much differently, I just think the
fact that I can do something easily is no exuse for doing it repeatedly when i
can abstract it away.

If I can shrink my event handler procedures by 1/2 by abstracting a repetitive
pattern with a wrapper, I have now made the form's code, shorter, clearer,
easier to read, and easier to maintain. Id rather have the code in my forms
and specific modules only have to talk about the specifics, and not repeat 3
object declarations per query, and error handling code that's often as big as
the rest of the code put together, or bigger. Furthermore, the more I can
remove duplication of these details, the more eaily I can change the pattern
later, inserting a query action logging process, for instance.

personally, I recommend removing duplication with prejudice. Twice is enough
duplication to consider abstracting away, and thrice is a code smell that
should be cleaned up. That has nothing to do with DAO or databases in
particular. Any time you repeatedly need to perform a specific type of
activity with a more general system, a wrapper is useful. I even wrap Dir() -
try it, you'll like it.

On Sat, 13 Nov 2004 02:56:45 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
Steve, neither have I found it necessary nor useful to employ classes with
DAO, but perhaps your method of working with databases leads to more "kruft"
that you need to "abstract away". Our "little desktop database" surprised so
many people, even its vendor, with the broad range and capabilities; there
are so many things you can do with it, that no one's needs are much of a
surprise.

A fellow MVP, Tom Ellison, and I would be constantly at odds if we didn't
each realize that what we do with Access requires very different approaches.
The work that he does requires very, very complex SQL; the work that I
generally do requires only very simple SQL.

Still, after using them just a few times, all the "preliminaries" you
describe just became second nature. I key those in without even having to
think much about it.

Larry Linson
Microsoft Access MVP

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1m********************************@4ax.com.. .
I don't find it pointless at all to wrap databas interfaces. It's nice to
abstrct away all the kruft. For instance, when I want to use a

transaction, I
don't want to deal with setting up the workspace object, reopening the
database in the new workspace after getting its name from CurrentDb, etc.

I'd
rather have a clsDbHandler with a method like ...

Public Sub ManageCurrentDb(ByVal UseSeparateWorkspace As Boolean)

I also like to have a clsSelectQueryHandler that automatically gives me a
temporary querydef member, and has a method to generate a recordset for

me,
and hold that in another member. The error handling all happens within
generateRecordset, and only has to be coded in one place. All these

wrappers
automatically close things and set them to Nothing in the proper order.

The
database handler can't go out of scope early because the subordinate

handlers
hold a refrence to it.

I find that this strategy saves vast amounts of time. I think the biggest
savings is in the encapsulation of error handling around the most commonly
used database activities.

On Fri, 12 Nov 2004 00:38:44 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
>Well, frankly, Steve, I don't see any "mess" with DAO. It is, in fact,
>relatively simple to learn and use -- I've been using it daily, withoutthe >need for any "wrapper" since shortly after Access was first released.Like >almost anything else in computers, to make the best use of it, "you'vegotta >know what you're doing".
>
>Databases aren't just "files", and you can't really simplify them to the
>point that they seem so without removing all the functionality theyprovide >that files do not provide. Without some specific examples, I'd have tosay >that your stated objections are not things I have encountered.
>
>Seems useless to me to design a new user interface for something that
>already has a user interface. To use the full functionality of the
>underlying DAO, you'll have to duplicate all the information that DAO
>requires. And, with DAO, I can find plenty of information from various
>sources -- which will be unlikely with a new interface.
>
>Good luck with your project.
>
> Larry Linson
> Microsoft Access MVP
>
>
>
>"Steve Richfield" <go****@smart-life.net> wrote in message
>news:78**************************@posting.google. com...
>> In response to some off-USENET replies,
>>
>> 1. DAO, ADO, and the other similar solutions all have the same
>> problem - the coding depends on extensive hidden subtlties that would
>> be best hidden from all but those who maintain them. What seems to be
>> needed is a simple wrapper that could easily be adapted to any
>> underlying DB for those situations where simple sequential access or
>> something pretty close to that is needed. That was my general goal -
>> to fill in the gap where queries and subforms fall flat on their face,
>> as a number of prior postings here of the problems I was having show.
>> My choice of DAO on only motivated by its ubiquitous availability.
>>
>> Note that the wrapper could trivially be changed to ADO in a few
>> minutes - MUCH less effort than changing a bunch of usage sprinkled
>> throughout a large program.
>>
>> 2. While some uses of the wrapper might be replaced by a sequential
>> file, these tables are also used elsewhere in complex queries, DLookup
>> calls, etc. To give an example:
>>
>> Partway through the process, a sequential pass is made through the
>> "Conditions" table that has the potential symptoms and lab results for
>> every known condition, but ONLY for the language presently in use, so
>> these proper language records are pulled out with a SELECT query to be
>> gone over sequentially. Then, the conditions are assigned scores and
>> re-examined in the order of decresing scores. Some of the highly
>> ranked conditions will probably be lacking any mention of important
>> symptoms in the patient's statement, so a DLookup is performed to
>> extract a question from the "Symptom" table for the MISSING symptom(s)
>> to ask the question. Suppose hypothyroidism floated to the top but the
>> patient made no mention of asthma, a common symptom. To resolve this
>> question, the patient might add something like "I do not have asthma."
>> to his statement, which would then resolve this issue when his
>> statement is re-analyzed, and also reduce the score for
>> hypothyroidism.
>>
>> Hence, it is really irrelivant that it is written using DAO, or that
>> its main application is to read queries sequentially.
>>
>> What IS important is that it can trivially be ported to any DB, and
>> that it makes reading queries sequentially both simple and readable.
>> Also, it contains internal checks that most programmers wouldn't
>> bother "cluttering" up their program with, so that things don't later
>> get "wound around the axle" for some difficult to determine reason.
>> Finally, when you go to type in a call to one of these routines, it
>> displays all of the options right there, horizontally, rather than
>> hiding them in a nearly infinitely long vertical list that mostly
>> contains things that you will NEVER EVER use.
>>
>> Steve Richfield
>


Nov 13 '05 #10

P: n/a
Steve,
Were I asked to build a database in support of an expert system (which is my
best guess at what you are coding, sorry if am wrong) I'd probaby implement
a schema which is essentially a tree. This way intitial criteria put me in
part of the tree and subsequent queries send me further down some branches
until I hit a leaf that is my likliest result. Also, your DLookup is
essentially an outer join and I suspect things would run faster if you
invested some time in rethinking this as pure SQL without embedded function
calls like DLookup.

"Steve Richfield" <go****@smart-life.net> wrote in message
news:78**************************@posting.google.c om...
In response to some off-USENET replies,

1. DAO, ADO, and the other similar solutions all have the same
problem - the coding depends on extensive hidden subtlties that would
be best hidden from all but those who maintain them. What seems to be
needed is a simple wrapper that could easily be adapted to any
underlying DB for those situations where simple sequential access or
something pretty close to that is needed. That was my general goal -
to fill in the gap where queries and subforms fall flat on their face,
as a number of prior postings here of the problems I was having show.
My choice of DAO on only motivated by its ubiquitous availability.

Note that the wrapper could trivially be changed to ADO in a few
minutes - MUCH less effort than changing a bunch of usage sprinkled
throughout a large program.

2. While some uses of the wrapper might be replaced by a sequential
file, these tables are also used elsewhere in complex queries, DLookup
calls, etc. To give an example:

Partway through the process, a sequential pass is made through the
"Conditions" table that has the potential symptoms and lab results for
every known condition, but ONLY for the language presently in use, so
these proper language records are pulled out with a SELECT query to be
gone over sequentially. Then, the conditions are assigned scores and
re-examined in the order of decresing scores. Some of the highly
ranked conditions will probably be lacking any mention of important
symptoms in the patient's statement, so a DLookup is performed to
extract a question from the "Symptom" table for the MISSING symptom(s)
to ask the question. Suppose hypothyroidism floated to the top but the
patient made no mention of asthma, a common symptom. To resolve this
question, the patient might add something like "I do not have asthma."
to his statement, which would then resolve this issue when his
statement is re-analyzed, and also reduce the score for
hypothyroidism.

Hence, it is really irrelivant that it is written using DAO, or that
its main application is to read queries sequentially.

What IS important is that it can trivially be ported to any DB, and
that it makes reading queries sequentially both simple and readable.
Also, it contains internal checks that most programmers wouldn't
bother "cluttering" up their program with, so that things don't later
get "wound around the axle" for some difficult to determine reason.
Finally, when you go to type in a call to one of these routines, it
displays all of the options right there, horizontally, rather than
hiding them in a nearly infinitely long vertical list that mostly
contains things that you will NEVER EVER use.

Steve Richfield

Nov 13 '05 #11

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:im********************************@4ax.com:
If I can shrink my event handler procedures by 1/2 by abstracting
a repetitive pattern with a wrapper, I have now made the form's
code, shorter, clearer, easier to read, and easier to maintain


And, very possibly, much harder to understand for someone coming to
the code later on.

The coder who most often has trouble figuring out the abstractions
in my code is *me*, 6 months or a year after the original coding.
When the code is based on lots of calls to outside procedures, it
can be very difficult for me to figure out what is going on, until
I've gone out and reviewed what is being done by those outside
subroutined.

It's a tradeoff -- and not everyone will come to the same conclusion
about the point at which the loss of immediate clarity is offset by
the maintainability.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #12

P: n/a
"Alan Webb" <kn*****@hotmail.com> wrote in
news:g4********************@comcast.com:
Were I asked to build a database in support of an expert system
(which is my best guess at what you are coding, sorry if am
wrong) I'd probaby implement a schema which is essentially a tree.
This way intitial criteria put me in part of the tree and
subsequent queries send me further down some branches until I hit
a leaf that is my likliest result. Also, your DLookup is
essentially an outer join and I suspect things would run faster if
you invested some time in rethinking this as pure SQL without
embedded function calls like DLookup.


I always consider code that uses a DLookup to be suspect. It has to
be justified.

And a DLookup called in SQL is definitely *very* suspect.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #13

P: n/a
On Sat, 13 Nov 2004 21:08:05 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:im********************************@4ax.com :
If I can shrink my event handler procedures by 1/2 by abstracting
a repetitive pattern with a wrapper, I have now made the form's
code, shorter, clearer, easier to read, and easier to maintain
And, very possibly, much harder to understand for someone coming to
the code later on.


If done poorly or overengineered, yes. I used to be guilty of overengineering
myself, but I've recently gotten very much over it. Done well and with a
strong effort at simplicity and clarity, they generally improve the
readability of the code and do not harm it.

The trick is to use the lightest-weight wrappers that do the job, keep call
stacks shallow, use all the layers of abstraction that actually simplify code,
but only those, only add procedures and layers that are actually used in the
application and actually remove them if they become obsolete, etc.
The coder who most often has trouble figuring out the abstractions
in my code is *me*, 6 months or a year after the original coding.
Agreed. I am fond of quoting a good friend of mine, Sam Livingston-Gray who
says "You + time = somebody else".
When the code is based on lots of calls to outside procedures, it
can be very difficult for me to figure out what is going on, until
I've gone out and reviewed what is being done by those outside
subroutined.
Yes, but if the procedures are very reused, you learn what they're doing the
first few times, and don't have to revisit them again, and if those procedures
are concisely written, they don't take much time to analyse in the first
place.

I have started trying hard to use sufficiently descriptive names for
procedures, that one shouldn't need to look at them to know what they do
unless they are not doing it (a bug).

For instance, say I keep having a need to get a colleciton of the values in a
column of a table. I might define that function as
DaoRecordsetColumnValueList(rst As DAO.Recordset, FieldName As String) As
VBA.Collection. You can tell pretty much everything you need to know just
from the function signature. If I were writing the same thing as a method of
a recordset wrapper class, the DaoRecordset part would be redundant, so I
could just define the method as ColumnValueList(FieldName As String). I might
do it either way depending on whether the application seemed to need a
recordset wrapper.
It's a tradeoff -- and not everyone will come to the same conclusion
about the point at which the loss of immediate clarity is offset by
the maintainability.


I agree. In fact, I come to different conclusions on different days.
Nov 13 '05 #14

P: n/a
David,
Thus my suspician that this isn't really an issue with DAO as much as it is
a poor schema further muddled by ugly abuse of SQL. In the original post
the guy sounded like he was iterating row by row through a table and chasing
related records for each row using DLookup or some other silliness. Without
seeing the database and associated code I'd guess the thing runs slow as the
dickens, chews resources like ravenous pig, and has a tendency to produce
unreliable results. Sometimes we get hired to maintain systems that really
ought to be shut down and this seems like one of them.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...
"Alan Webb" <kn*****@hotmail.com> wrote in
news:g4********************@comcast.com:
Were I asked to build a database in support of an expert system
(which is my best guess at what you are coding, sorry if am
wrong) I'd probaby implement a schema which is essentially a tree.
This way intitial criteria put me in part of the tree and
subsequent queries send me further down some branches until I hit
a leaf that is my likliest result. Also, your DLookup is
essentially an outer join and I suspect things would run faster if
you invested some time in rethinking this as pure SQL without
embedded function calls like DLookup.


I always consider code that uses a DLookup to be suspect. It has to
be justified.

And a DLookup called in SQL is definitely *very* suspect.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #15

P: n/a
> I always consider code that uses a DLookup to be suspect. It has to
be justified.

And a DLookup called in SQL is definitely *very* suspect.

I couldn't agree more. Our friend hasn't created a wrapper around
DAO, he's created a wrapper around the notion of using a database
instead of a text file.
Nov 13 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.