Microsoft ADO Ext. 2.8 for DDL and Security | Where did it go?

[JK]

Any idea why I can no longer select the the reference "Microsoft ADO Ext. 2.8
for DDL and Security?"

It's not in the list anymore?

 

[John W. Vinson]

Any idea why I can no longer select the the reference "Microsoft ADO Ext. 2.8
for DDL and Security?"

It's not in the list anymore?

What version of Access?

I know that Access 2007 removed the ability to create Workgroup Security (it
will still use it in .mdb databases if it was created with an earlier
version). Perhaps ADOX has been removed as well.

 

[David W. Fenton]

What version of Access?

I know that Access 2007 removed the ability to create Workgroup
Security (it will still use it in .mdb databases if it was created
with an earlier version). Perhaps ADOX has been removed as well.

Why would one ever use any of ADO's ugly stepchildren for
Jet-specific functionality?

 

[Dominic Vella]

Yes, it's because you have put the database onto a Windows-Vista machine.
It likes to remove it from databases, but it never comes back if you use it
back on a Windows-XP machine.

I used ADOX to automatically relink my BackEnd database back to a server
when it's in a Business Network, and then switch it back to my BackEnd test
data when I'm developing. It's obvious that many people do their linking
manually considering how many people do not understand what ADOX does.

The best solution I have so far is this:

Sub TestADOX()
On Error Resume Next
If Application.References("ADOX").IsBroken Then
If FileExists("c:\windows\system32\msadox.dll") Then
Application.References.AddFromFile
"c:\windows\system32\msadox.dll"
Else
Application.References.AddFromFile "c:\program files\common
files\system\ado\msadox.dll"
End If
MsgBox "ADOX Error discovered, probably due to being used in
Windows-Vista." _
& " This database needs to restart." _
& vbCrLf & "Press OK to close this application and then restart
again." _
& vbCrLf & "Please contact the database developer if this error
continues."
DoCmd.Quit
End If
End Sub

The bug is has is that it has to close the database before the problem is
fixed.


I hope this is helpful.


Dominic

 

[Dominic Vella]

Ooops, nearly forgot

Function FileExists(strFile As String) As Boolean
On Error Resume Next
Dim intLength As Integer
intLength = Len(Dir(strFile))
FileExists = (Not Err And intLength > 0)
End Function


Dom

 

[Tony Toews [MVP]]

Why would one ever use any of ADO's ugly stepchildren for
Jet-specific functionality?

Only one reason that I know of.

Fixing AutoNumbers when Access assigns negatives or duplicates
http://www.allenbrowne.com/ser-40.html

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

On Error Resume Next

I'd remove the above line and ignore or msgbox the various errors as
appropriate.

If Application.References("ADOX").IsBroken Then
If FileExists("c:\windows\system32\msadox.dll") Then
Application.References.AddFromFile
"c:\windows\system32\msadox.dll"
Else
Application.References.AddFromFile "c:\program files\common
files\system\ado\msadox.dll"
End If

This assumes the location of those DLLs which almost all the time will
be correct. But what about those few times when they're elsewhere?

Also you can't add references in an MDE.

MsgBox "ADOX Error discovered, probably due to being used in
Windows-Vista." _
& " This database needs to restart." _

But what if the above lines of code don't work because of another
problem of some sort?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Dominic Vella]

Yes Tony, I appreciate all of your comments, but it is the best solution I
have got at the moment. Once upon a time linking databases was simple
enough for any of my non-tech customers, but now that MS-Vista unashamedly
corrupts the ADOX reference, has caused no end of trouble to my business
reputation. Other Vista issues (like date picker on date/time fields) have
caused grief, nearly put me out of business.

And you can't say ADOX is not important, otherwise why was it invented in
the first place if it had no purpose. A small list where ADOX has
advantages over ADO in Office2000:
* Creates new databases
* Supports Decimal data types
* Supports Compression attributes for field data
* Creates and edit saved queries that are accessible only through code
(yes there is more, but I think point is made)

Mind you, it was my fault converting to Vista for my development work when
my customers were still on XP.

Fortunately, my clients are starting to convert fully to Vista, fortunately
now being aware of such issues means I have confident answers.

I would welcome any help on restoring ADOX links when returning to WinXP
environments. I would seriously welcome if someone could provide a better
solution than the code I provided.


Dominic

 

[Brendan Reynolds]

I would welcome any help on restoring ADOX links when returning to WinXP
environments. I would seriously welcome if someone could provide a better
solution than the code I provided.

I haven't personally used it, Dominic, but you might be interested in what
Michael Kaplan says about programmatically fixing references at the
following URL ...

http://www.trigeminal.com/usenet/usenet026.asp?1033

 

[Dominic Vella]

Yes, that's a great read. I particularly think this is heading in the right
direction:
Declare Function DllRegisterServer Lib "<yourlib.dll>" () As Long
It'll take some time before I work out how to make that work for
"msadox.dll". It would be great to find a course that teaches more about
this stuff.

Thanks Brendan

Dominic

 

[David W. Fenton]

Only one reason that I know of.

Fixing AutoNumbers when Access assigns negatives or duplicates
http://www.allenbrowne.com/ser-40.html

Oh, yes, the things that MS purposefully omitted from DAO so that
you'd have to use ADO for certain things. I would hope that with its
own version of Jet, Access can rectify that and fold that handful of
features into DAO so that ADO can be left to die the death it so
richly deserves as an interface to Jet.

 

[david]

Not that I'm particularly a fan of the way Access has been treated,
but that's a bit unfair.

The new features were broken. Autonumbers particularly so.

DAO was maintained for backward compatibility, and new
features were not added to it.

Adding new features breaks things. Particularly, as with Access,
when the new features aren't tested properly, and don't fit with
the original underlying architecture.

So DAO was walled off as deprecated, and they tried not to
break it. I think that was a reasonable approach.

(david)

 

[Larry Linson]

I used ADOX to automatically relink my BackEnd
database back to a server when it's in a Business
Network, and then switch it back to my BackEnd test
data when I'm developing. It's obvious that many
people do their linking manually considering how many
people do not understand what ADOX does.

You can, and many do, relink tables with DAO. There is an example in the
Developer Solutions example database -- it's been moved around a bit on the
Microsoft site, but it's searchable in the Knowledge Base,
http://support.microsoft.com. With some versions of server databases you
have to delete and re-create the link, but you can do that with VBA-DAO
code... "compiling" a database to MDE or ACCDE does not prevent performing
design functions from code, it just makes the "design surface" unavailable
(some of us tend to forget that, or never knew it).

Larry Linson
Microsoft Office Access MVP

 

[Dominic Vella]

Again though, to use DAO you would require to add it as a reference before
it is usable, which then put's it back into the same category as ADOX where
Microsoft could one day decide to switch it off without warning, and in fact
I figured that was the plan when Microsoft introduced ADO anyway.

Actually, this topic has become mundane, hasn't it. I'd rather eat prunes.


Dominic

 

[Larry Linson]

Actually, this topic has become mundane, hasn't it. I'd rather eat
prunes.

Better than one of michka's favorite sayings, "I'd sooner slide down a giant
razor blade into a vat of iodine than... ".

DAO is included in the default references in Access 2003 and 2007. For
"safety's sake", some move it up the list to before the ADO reference, just
in case they slip up and forget to qualify the use of an object that has the
same name in both DAO and ADO.

Even if Microsoft introduced some new access method to replace both ADO and
DAO (called, say, Object Data Doohickey {ODD}), it's likely it would be
implemented so a reference was required.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

Not that I'm particularly a fan of the way Access has been
treated, but that's a bit unfair.

The new features were broken. Autonumbers particularly so.

Yes, the original implementation in Jet 4 of resettable seed values
for Autonumbers was broken. But that has nothing whatsoever to do
with which data interface MS chooses to implement programmatic
resetting of that seed value.

DAO was maintained for backward compatibility, and new
features were not added to it.

Yes, and that turned out to be an incredibly stupid decision on MS's
part, since now DAO is no longer deprecated -- it's now the
preferred interface for interacting with Jet data (as it *should*
have been all along).

Adding new features breaks things. Particularly, as with Access,
when the new features aren't tested properly, and don't fit with
the original underlying architecture.

Eh? This is an interface to a feature in the Jet database engine.
Where you put programmatic control of that has *nothing* to do with
the actual underlying architecture -- it's just an interface.

So DAO was walled off as deprecated, and they tried not to
break it. I think that was a reasonable approach.

It was a bloody stupid decision and most people who understood what
DAO was realized it was stupid and continued to use DAO for
interacting with Jet data.

ADO is now the dead interface, and the Jet features for which a
programmatic interface is provided in ADO should have those
programmatic interfaces implemented in DAO, which is again (as it
should have been all along) the preferred interface for interacting
with Jet data.

 

[David W. Fenton]

Again though, to use DAO you would require to add it as a
reference before it is usable, which then put's it back into the
same category as ADOX where Microsoft could one day decide to
switch it off without warning, and in fact I figured that was the
plan when Microsoft introduced ADO anyway.

Nobody should be using ADOX with Jet data.

Nobody.

 

[David W. Fenton]

Better than one of michka's favorite sayings, "I'd sooner slide
down a giant razor blade into a vat of iodine than... ".

DAO is included in the default references in Access 2003 and 2007.
For "safety's sake", some move it up the list to before the ADO
reference, just in case they slip up and forget to qualify the use
of an object that has the same name in both DAO and ADO.

If you're going to use ADO or any of its ugly stepchildren, *those*
are the libraries that should be accessed via late binding. DAO
should always have a reference, because it's so essential a part of
Access.

Now, the exception to this would be if you need ADO for accessing a
non-Jet back end (in addition to using ODBC linked tables). But you
can still use late binding for that, and improve the efficiency by
caching your ADO object references (like we cache a reference to
CurrentDB() in order to save the overhead of repeatedly referencing
it).

Even if Microsoft introduced some new access method to replace
both ADO and DAO (called, say, Object Data Doohickey {ODD}), it's
likely it would be implemented so a reference was required.

Or late binding. ADO is definitely a candidate for late binding if
you're using it only for a few things (such as the handful of tasks
that cannot be done in DAO).

 

[James A. Fortune]

It was a bloody stupid decision and most people who understood what
DAO was realized it was stupid and continued to use DAO for
interacting with Jet data.

ADO is now the dead interface, and the Jet features for which a
programmatic interface is provided in ADO should have those
programmatic interfaces implemented in DAO, which is again (as it
should have been all along) the preferred interface for interacting
with Jet data.

Hmmm... If ADO was supposed to be like a superset of DAO that included
other technologies (and supposedly no loss in speed when using DAO -
har!), then why wasn't it designed so that the DAO part of ADO would
work exactly the same as DAO? My point is that it's only a stupid
decision in hindsight. The goal of ADO was a good one. Had they
accomplished DAO speed with JET (one of their stated goals) along with
the universality and security they hoped to accomplish, we'd all be
doing everything in ADO. Now that DAO is back, you are correct that it
should be retrofitted with some of the ADO capabilities. Maybe
a single connection object is not capable of handling all the necessary
methods for both data objects simultaneously.

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

Again though, to use DAO you would require to add it as a reference before
it is usable, which then put's it back into the same category as ADOX where
Microsoft could one day decide to switch it off without warning, and in fact
I figured that was the plan when Microsoft introduced ADO anyway.

FWIW though DAO and Jet 4.0 come with Windows 2000, Windows 2003
Server and Windows XP. And Jet 4.0 is used by those OSs. (Although
I haven't actually looked in Windows VIsta for this and don't feel
like starting up a Virtual PC session just to see.)

Also it seems to me that ADO has a lot of versions. Dunno how
troublesome that is though.

Minor points though.

Actually, this topic has become mundane, hasn't it. I'd rather eat prunes.

<chuckle> Yeah, but we like arguing this kind of arcane stuff.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/