ADOX.Catalog

[Guest]

I am writing a small Access 2000 application. I have an Access 2000 book that
uses a class type named "ADOX.Catalog" that I want to use in my application.
However I get an error when I try to use the code:

Sub RunParameterQuery(datStart As Date, datEnd As Date)
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim tempcat As ADOX.Catalog

Set tempcat = New ADOX.Catalog
tempcat.ActiveConnection = CurrentProject.Connection

Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Select * from JOCWCStudent " & _
"Where Date Between ? and ?"
cmd.CommandType = adCmdText

tempcat.Views.Append "qryWeekly", cmd
tempcat.Views.Refresh

Set rst = cmd.Execute(Parameters:=Array(datStart, datEnd))

rst.Close

Set tempcat = Nothing
Set rst = Nothing
Set cmd = Nothing

End Sub

The piece of code that gives me an error is the statement:

Dim tempcat As ADOX.Catalog

The error message itself is in a small Window and says:

Compile error
User defined type not found

I know what the error message means, it is not recognizing the ADOX.Catalog
data type. This data type is used in my Access 2000 book repeatedly. I tried
using a zero and a capital "O". It gave me an error both ways. I could not
find this class type in the object viewer. However, it used repeatedly in the
text.

I am using MS Access 9.0.2720

Is the "ADOX.Catalog" a valid type? It is used in my text repeatedly. Can
anybody help me?

 

[Allen Browne]

From a code window, choose References on the Tools menu.

Check the box beside:
Microsoft ADO Ext 2.x for DDL and Security
The Catalog belongs to that library.

 

[peregenem]

cmd.CommandText = "Select * from JOCWCStudent " & _
"Where Date Between ? and ?"
cmd.CommandType = adCmdText

tempcat.Views.Append "qryWeekly", cmd

This object is not a VIEW, it is a PROCEDURE. Try

tempcat.Procedures.Append "qryWeekly", cmd

 

[RoyVidar]

Sorry for being a bit direct - but this stuff with altering stored
queries,
isn't that a wee bit DAO and querydef thingies?

It seems the purpose of this routine is to run some dynamic sql,
why add to the bloat by adding/altering a stored query?

Why not just either execute the the dynamic sql through the
command object, or have a stored query, that you open through
ADO, and pass the parameters?

I think, for neither of those, there should be any need for ADOX,
unless you wish to have the query resolve parameters from
forms in the 2000 version.


Gregory Poulo wrote in message

 

[peregenem]

Sorry for being a bit direct - but this stuff with altering stored
queries,
isn't that a wee bit DAO and querydef thingies?

ADO vs DAO is a lifestyle choice. ADO makes more sense to newbies who
haven't had to use DAO, I feel. Also, DAO has fundamental design
flaws e.g. must set recordset and database to Nothing, must be done in
a certain order, etc.

It seems the purpose of this routine is to run some dynamic sql,
why add to the bloat by adding/altering a stored query?

Why not just either execute the the dynamic sql through the
command object, or have a stored query, that you open through
ADO, and pass the parameters?

Stored procs are superior: more secure (sql injection), better control
(WITH OWNERACCESS OPTION), better design (logic in one place = easier
to maintain), better for users (just call the proc!), less travelling
down the wires (just the proc name and the parameter values, not the
whole sql text). They don't cause bloat like data do!

 

[RoyVidar]

Should we perhaps back up a little here?

Lets investigate the initial code. It might be just my
eyesight being a bit bad, but to me it seems this code
is doing the following things:

1 - Initializing a command object with a dynamic sql string
2 - attempting to store the dynamic sql of the command object
as a stored query (views collection)
3 - execute the command object - which executes the dynamic
sql, not the stored query, returning a recordset

Now - I find this a bit peculiar - why would one have a
routine to both create a stored query (which isn't used),
then return a recordset through executing the dynamic
sql the query is based on?

I have not yet found a need to alter SQL of neither stored
queries, nor views or SPs at run time and in my humble
opinion etc such should be avioded! Should I be forced to
do so, then I'm sure I would not alter a stored query,
then execute dynamic SQL - I would then execute the query
(or view, or SP,) - else, why on earth bother creating one?
I think also I would have put them in separate methods,
then check for existance (openschema method, perhaps), and
if not, create it first - but still have creation and
execution in separate methods.

But I would usually create stored queries, or SPs in
"design view", then pass the parameters when executing
them, not create the query, or recreate/alter the sql
of the query every time (and then execute the dynamic
sql in stead)

So my comment on "DAO stuff", is related to what seems to
be a fascination among a lot of a lot of developers using
DAO, altering the sql of stored queries through the
querydef object - this is one of the, in my view, bad
habits one should not bring when starting to use ADO.

And I couldn't disagree more on the "lifestile" thingie,
DAO and ADO are simply just libraries, or methods if you
like, used for manipulating ones datasource. Getting
religious over it, doesn't make sence. Just choose the
appropriate tool for the task at hand.

To bring in one fact. DAO is faster than ADO on nearly
all operations on Access tables, and therefore, some of
the developers I really look up to, might even recommend
usage of DAO over ADO for all development against Jet.
That also makes sence, not just because it is often
significantly faster - but also since form recordsets of
all versions are DAO, unless specificly assigned ADO (which
frankly, is a hassle in 2000) or one is using an ADP.

I'm not sure where the SP stuff entered the discussion,
but since it is brought up. I don't see the question
relating to SPs at all, it relates to getting a
recordset through a dynamic SQL string, utilizing the
command object. In the process of returning this
recordset, the dynamic SQL is attemtped saved
(appended to) as a member of the the views collection
of the catalog object. This I tried to address as not
being necessary.

Adressing SP versus dynamic SQL, I claim that invoking
the parameters collection of the command object, even
the way it is performed on dynamic sql in this sample,
is as good as executing an SP with regards to injection
attacks. Executing dynamic sql, as in this sampele,
hovewer does not take advantage of the execution plan
used by stored queries/SPs.

Not that it's very relevant, but through the usage of
currentproject.connection, the prefix on the name of the
view/procedure, the lack of cast/convert on the date
parameters in the string is what makes me thinkg this
might be Access, not SQL-server (stored queries vs
views/SPs), but again, that's probably not that
relevant.

So to reiterate - why mess around with creating or
changing the sql of a stored query, and then end up
with executeing some dynamic sql?

BTW - this code will fail the second time, if the
stored query isn't deleted first.

May I ask a question? Do you not explicitly close and
release your ADO objects? When doing so, do you not do
so in the order of dependency? Do you not check for
instance the .state and .editmode properties of
recordsets? Not that I use much DAO, but I'd be
interested in knowing what flaws exists.

(e-mail address removed) wrote in message

 

[peregenem]

why mess around with creating or
changing the sql of a stored query, and then end up
with executeing some dynamic sql?

We seem to be in complete agreement about the general usage of procs,
dynamic SQL and DAO vs ADO

Yeah, the code is clearly not production code. Creating a PROCEDURE and
then ignoring it to use the dynamic sql doesn't make sense. I figure
the OP is experimenting.

I'm not sure where the SP stuff entered the discussion

From the OP I was pointing out the SQL as defined is a PROCEDURE

(yes, a _Jet_ PROCEDURE), so even though he has Appended it to the
Views collection, it will appear in the Procedures collection (I guess
this is why he was using tempcat.Views.Refresh and wondering where it
has gone!) BTW Jet stored procs don't have execution plans and are not
compiled.

To bring in one fact. DAO is faster than ADO on
nearly all operations on Access tables

Thanks. In my experience, code maintenance is more significant than
run-time performance. But I will bear your comments in mind should
there come an occasion when the most optimized ADO solution is not
performing to requirements.

Do you not explicitly close and
release your ADO objects?

I close connections, yes, but that's all. Releasing VB variables,
including ADO variables, is the job of VBA's garbage collector. ADO
objects do not have to be explicitly released for them to be cleaned up
unless there is good reason for forcing ealy release e.g. memory
management. Also note that ADO is a flat structure so releasing objects
in order would not apply anyhow.

Not that I use much DAO, but I'd be
interested in knowing what flaws exists.

From a google groups post:

Matt Curland's book, Advanced
VB (p110): "[DAO provides] another example of poor teardown code. DAO
has Close methods that must be called in the correct order, and the
objects must be released in the correct order as well (Recordset before

Database, for example). This single poor object model behavior has led
to the misconception that VB leaks memory unless you explicitly set all

the local variables to nothing at the end of a function. This is a
completely false notion in a well-designed object model. VB can clear
the variables faster at the End Sub line than you can from code, and it

checks the variables even if you explicitly release your references.
Any effort you make is duplicated."