CurrentDB

[Robert]

Contrary to MS documentation I cannot get a reference to
CurrentDB in an adp file.

1. I have set a reference to DAO 3.6 (as MS documentation
states) I even moved it above the ADO reference, I even
removed the ADO reference...no luck

2. I get "Object variable or with variable not set"
(error 91) when I run the following code. I get the error
on the last line (Debug.Print)

Sub test()
Dim db As DAO.Database

Set db = CurrentProject.Application.CurrentDb
Debug.Print db.Name
End Sub

Any help would be greatly appreciated.

 

[Van T. Dinh]

You don't have CurrentDb in ADP. Remember with ADP, the database resides in
the MS-SQL Server.

 

[Bob McClellan]

Closest thing would probably be:
CurrentProject.Connection

hth bob mcclellan

 

[Robert]

Well I think, there are actually two databases the .adp
file and the .mdf/.ldf files.

I guess my point is I am trying to accomplish DAO (Access
specific operations) like listing all reports or forms
(which are .adp specific objects). Anyway Microsoft
documantation seems to indicate this is possible, the
following excerpt is taken from the VBA help file for the
topic CurrentDB:

"Note In Microsoft Access the CurrentDb method
establishes a hidden reference to the Microsoft DAO 3.6
Object Library in a Microsoft Access database (.mdb). If
you want to use the CurrentDb method in an Access project
(.adp) you must set a permanent reference to the DAO 3.6
Object library in the Microsoft Visual Basic Editor."

Is this incorrect?

 

[Robert]

Bob,

Thanks I have taken a close look at that path, but I am
trying to accomplish DAO (Access specific operations)
like listing all reports or forms (which are .adp
specific objects). Anyway Microsoft documantation seems
to indicate this is possible, the following excerpt is
taken from the VBA help file for the topic CurrentDB:

"Note In Microsoft Access the CurrentDb method
establishes a hidden reference to the Microsoft DAO 3.6
Object Library in a Microsoft Access database (.mdb). If
you want to use the CurrentDb method in an Access project
(.adp) you must set a permanent reference to the DAO 3.6
Object library in the Microsoft Visual Basic Editor."

Is this incorrect?

 

[Lyle Fairfield]

Well I think, there are actually two databases the .adp
file and the .mdf/.ldf files.

I guess my point is I am trying to accomplish DAO (Access
specific operations) like listing all reports or forms
(which are .adp specific objects). Anyway Microsoft
documantation seems to indicate this is possible, the
following excerpt is taken from the VBA help file for the
topic CurrentDB:

"Note In Microsoft Access the CurrentDb method
establishes a hidden reference to the Microsoft DAO 3.6
Object Library in a Microsoft Access database (.mdb). If
you want to use the CurrentDb method in an Access project
(.adp) you must set a permanent reference to the DAO 3.6
Object library in the Microsoft Visual Basic Editor."

Is this incorrect?

Typically one does NOT use DAO in an ADP, unless one sets a reference to it
for some particular reason (I have never had such a reason). While your quote
is correct, it isn't of much more relevance to an ADP than, say, a quote
about C++, oranges, or Reese Witherspoon.

Generally one uses the Application Object and the CurrentProject Object as
parent objects in ADPs.

One might list all reports in an ADP as follows:

(air code)

Dim ar As AccessObject
For Each ar In CurrentProject.AllReports
MsgBox ar.Name
Next ar

When we move to ADPs, we largely put DOA behind us.

 

[Robert]

Lyle,

Thanks for the code snippet, I suppose I will have to
look at the ADO alternatives. I was tasked to convert a
lenghty code module (from .mdb to .adp) for a client,
that makes liberal use of DAO (CurrentDB to be specific)
And since MS documentation said it could work I figured
this would be a "quick-and-dirty" way to do it, but alas
MS documentation is not always correct.

Anyway I definately tend to agree with your statement of
leaving DAO behind (for the .adp not at all convinced
for .mdb).

Thanks for your input.

Robert

 

[Van T. Dinh]

I agree completely with what Lyle wrote (except for the
DOA at the end).

For the records, CurrentDb does not exist in ADP since ADP
is not really a "database". Just to be sure, I tested
exactly as per Help and CurrentDb did not work on 2
different PCs. Also, I remember MichKa posted previous
that he was at the meeting when Microsoft Access engineers
were discussing this and they decided NOT to include
CurrentDb in ADP.

HTH
Van T. Dinh
MVP (Access)

 

[Robert]

Van,

Thanks for confirming this (I also had tested this on two
PCs and get the same result...nothing)

I just wanted to be sure since MS help did say it was
possible.

Also a pretty good artice about "is DAO dead"?

http://sqlserveradvisor.com/doc/05515

 

[Van T. Dinh]

All technologies will be superseded eventually. There will be millions of
databases with DAO code that need to be supported for a number of years to
come yet (I have just discovered that a client of my still has a working
DBase IV application written about 13 years ago) so it certainly good to
know DAO.

 

[Lyle Fairfield]

All technologies will be superseded eventually. There will be millions
of databases with DAO code that need to be supported for a number of
years to come yet (I have just discovered that a client of my still has
a working DBase IV application written about 13 years ago) so it
certainly good to know DAO.

Clipper?
FoxPro?
FoxBase?
DBase III?

I've been through them all.

Twenty or more years ago there existed a database for the Commodore C64
called Oracle. (Yes, "Oracle"!). Should I brush up my skills with it?

DAO was fine in its time. And, the latest version is quite powerful. I
seldom see this new power noted, used or discussed. Examples are:
Constraints,
CREATE GROUP, CREATE USER / DROP,
CREATE PROCEDURE / DROP,
CREATE VIEW / DROP,
GRANT / REVOKE,
A more powerful ALTER TABLE statement

While many of these were available before as outcomes of a combination of
Access, VBA and SQL, they are now entrenched (one hopes) in the db engine.

There is no problem with continuing working with and valuing DAO. But
Server Based solutions are becoming cheaper and more common every day. One
can use ODBC and DAO to interact with Server Based Databases, but new
capabilities and new possibilities await those who will pay the price
associated with learning newer technologies for doing so.

One of those technologies is the ADP. For the most part, ADPs use ADO. And
ADPs are, to the best of my knowledge what this newsgroup is about.

As for me, my days with DAO and JET are over, except for trivial one-of
personal lists, orders etc. MSDE - SQL Server and whatever front-end
(Access ADPs, in this case) simply provide too much efficient power for
me; I would not go back to floundering around with JET again.

I have written many thousands of lines of VBA code using the DAO engine,
some of it exemplary and some of it not so exemplary. But it's OVER.

That was then. This is now.

 

[Van T. Dinh]

Hi Lyle

I mentioned it because Robert referred to an article which is about 4 years
old. Things have changed substantially since then.

Personally, if I know for sure the clients only need Access with JET and
have no need to change to proper CS, I still write in DAO (more of my
comfort zone). All other cases (BE different from JET), I use ADO. This,
of course, include ADP application.

You didn't mentioned Omnis (a Mac/Windows-based desktop database software).
I used Omnis-5 and Omnis-7 on both Mac & PC platforms in 1990-1992. I loved
it and it looked a lot more advanced than Access when Access was introduced.
I think they didn't have enough marketing clouts (the Australian exclusive
agent was in Gold Coast, Aust. which was a holiday area, certainly not a
business city like Sydney & Melbourne) and Omnis died. I think there have
been attempts to revive it but it is simply too late now.