Moving from DAO to ADO table-linking through code.

[Joel Wiseheart]

Although I have been programming in Access VBA for some
time, I am in the early stages of making the transition
from DAO to ADO programming. Apparently, everything I've
read implies that DAO is going away, so I have to learn
ADO sometime....why not now?

How would I do an ADO (or ADOX) equivalent of the code
below? I cannot find anything in my reference materials
that talks about how to use ADO(X) to determine where an
external table is linking FROM. Also, I don't see any
property of the ADOX "Table" object that matches the
DAO "TableDef" object "Connect" Property.

Thanks for your help!

Public Function DAOLinkedTables()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb

For Each tdf In db.TableDefs
With tdf
If (.Attributes And dbSystemObject) Then
'Do Nothing
Else
Debug.Print .SourceTableName _
& " : " _
& .Connect
End If
End With
Next

End Function

 

[Paul Overway]

I'm not sure where you got the idea that DAO is going away. In any case,
unless your app connects or will connect to data sources other than Jet,
there is little (if any) value in using ADO. There are some things you
cannot do in respect to Jet databases with ADO, and many things that are far
less efficient. Your current inquiry falls in that realm. Linked tables
have no purpose in ADO....that is what your connect string is for.

 

[Martin Seelhofer]

Hi Joel

Well, since ADO and ADOX are far more generalized (and
can therefore be used to control a whole bunch of different
database management systems) than DAO was (DAO was
basically the database functionality of Access and nothing
more), the object model does not reflect some of the
specialities of Access objects. One of these specialities - as
you by no doubt just ran into yourself - is the way you have
to deal with linked tables. This is a provider-dependent thing
and therefore is not reflected in the object model directly.
These provider specific things are usually handled with custom
properties of (in this case) ADOX Table-objects. In your case,
the properties "Jet OLEDB:Link Datasource" (contains the
path to the database) and "Jet OLEDB:Remote Table Name"
might help you out. A useful routine to get to know these provider
specific properties for Access tables is one like the following:

Sub EnumTablePropsADOX(tblname As String)
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim prp As ADOX.Property

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(tblname)
For Each prp In tbl.Properties
Debug.Print prp.Name & " -> " & prp.Value
Next
End Sub

Ah - er - and don't forget to add the reference to the ADOX-library ;-)


Cheers,

Martin

 

[Joel Wiseheart]

This is exactly what I was looking for! I can run with
the information from here. Thank you for the push in the
right direction!

Joel

 

[Joel Wiseheart]

Well, here's a few excerpts from my reference materials:

From "Using Access 2002 Special Edition" by Que
publishing:

"SQL Server 2000, designated as one of the .NET
enterprise servers, is Microsoft's strategic RDBMS.
Access 2002's Jet 4.0 is Microsoft's last upgrade to the
relational version of Jet. Jet isn't dead, but it's on
the way out."

"Microsoft's .NET framework is centered on SQL Server
and an eenhanced version of ADO called ADO.NET, and you
can expect major changes to Access when Microsoft gets
around to releasing [the next version of Office, which]
undoubtedly will toll the death knell for new Jet-based
applications and DAO."

"DAO 3.6 is Microsoft's last iteration of this
venerable object model. Microsoft is directing virtually
all of its data connectivity investment to improving and
expanding the capabilities of OLE DB, ADO, and the .NET
framework's ADO.NET"

From "Access 2002 VBA Handbook," by Sybex publishing:

"Earlier versions of Access relied on DAO as their sole
mediator. Beginning with Access 2000, however, Microsoft
introduced ADO - a much simpler, more versatile
interpreter language....Since ADO was first released, it
has largely replaced DAO as the developer's language of
choice."

From "Access 2002 Desktop Developer's Handbook," by Sybex
publishing:

"Although DAO is still present in Access 2002, it's no
longer the preferred method for retrieving data, and it's
not the best library to learn for new applications."

It sounds like all the experts are trying to send a
message here. This is where I got my idea that both DAO
and Jet are going away, and ADP and ADO are here to stay.

Hey, that rhymes.... : )

Thanks,
Joel

 

[Dirk Goldgar]

Well, here's a few excerpts from my reference materials:

From "Using Access 2002 Special Edition" by Que
publishing:

"SQL Server 2000, designated as one of the .NET
enterprise servers, is Microsoft's strategic RDBMS.
Access 2002's Jet 4.0 is Microsoft's last upgrade to the
relational version of Jet. Jet isn't dead, but it's on
the way out."

"Microsoft's .NET framework is centered on SQL Server
and an eenhanced version of ADO called ADO.NET, and you
can expect major changes to Access when Microsoft gets
around to releasing [the next version of Office, which]
undoubtedly will toll the death knell for new Jet-based
applications and DAO."

"DAO 3.6 is Microsoft's last iteration of this
venerable object model. Microsoft is directing virtually
all of its data connectivity investment to improving and
expanding the capabilities of OLE DB, ADO, and the .NET
framework's ADO.NET"

From "Access 2002 VBA Handbook," by Sybex publishing:

"Earlier versions of Access relied on DAO as their sole
mediator. Beginning with Access 2000, however, Microsoft
introduced ADO - a much simpler, more versatile
interpreter language....Since ADO was first released, it
has largely replaced DAO as the developer's language of
choice."

From "Access 2002 Desktop Developer's Handbook," by Sybex
publishing:

"Although DAO is still present in Access 2002, it's no
longer the preferred method for retrieving data, and it's
not the best library to learn for new applications."

It sounds like all the experts are trying to send a
message here. This is where I got my idea that both DAO
and Jet are going away, and ADP and ADO are here to stay.

I make no claims to know what Microsoft's plans are for Jet, at this
moment. I can only point out a few things:

1. The books you quote are spouting the MS party line as of the time
they were written.

2. Microsoft's attempt to migrate all Access users from DAO/Jet to
ADO/SQL Server has so far been a complete failure.

3. There is no dispute, from Microsoft or anywhere else, that DAO is
more efficient and more powerful for working with Jet databases than
ADO.

4. The built-in DAO reference has been restored to Access 2003.

5. ADO itself is on its way out. ADO.NET is now the strategic
data-access API, and it's significantly different from ADO.

It seems to me that Microsoft's plans for Access are in flux. They've
been attempting to force it into a particular mold, but it hasn't been
going where they want it to go. Their vision for the future has run up
against the real world, and has had to be adjusted. Right now, Access
works equally well as a desktop, file-server database and as a front-end
to a client-server database like SQL Server. A while ago, they gave the
strong impression that they wanted to phase out the Jet, file-server
side completely. Now they seem to have come to terms with the fact that
not all databases need to be client-server, that both types have their
place. It remains to be seen where they're going to go with it in the
coming years, whether they'll drop one side or the other. But for now,
Jet is back in the Microsoft worldview.

 

[Joel Wiseheart]

-----Original Message-----

Well, here's a few excerpts from my reference materials:

From "Using Access 2002 Special Edition" by Que
publishing:

"SQL Server 2000, designated as one of the .NET
enterprise servers, is Microsoft's strategic RDBMS.
Access 2002's Jet 4.0 is Microsoft's last upgrade to the
relational version of Jet. Jet isn't dead, but it's on
the way out."

"Microsoft's .NET framework is centered on SQL Server
and an eenhanced version of ADO called ADO.NET, and you
can expect major changes to Access when Microsoft gets
around to releasing [the next version of Office, which]
undoubtedly will toll the death knell for new Jet-based
applications and DAO."

"DAO 3.6 is Microsoft's last iteration of this
venerable object model. Microsoft is directing virtually
all of its data connectivity investment to improving and
expanding the capabilities of OLE DB, ADO, and the .NET
framework's ADO.NET"

From "Access 2002 VBA Handbook," by Sybex publishing:

"Earlier versions of Access relied on DAO as their sole
mediator. Beginning with Access 2000, however, Microsoft
introduced ADO - a much simpler, more versatile
interpreter language....Since ADO was first released, it
has largely replaced DAO as the developer's language of
choice."

From "Access 2002 Desktop Developer's Handbook," by Sybex
publishing:

"Although DAO is still present in Access 2002, it's no
longer the preferred method for retrieving data, and it's
not the best library to learn for new applications."

It sounds like all the experts are trying to send a
message here. This is where I got my idea that both DAO
and Jet are going away, and ADP and ADO are here to

stay.

I make no claims to know what Microsoft's plans are for Jet, at this
moment. I can only point out a few things:

1. The books you quote are spouting the MS party line as of the time
they were written.

2. Microsoft's attempt to migrate all Access users from DAO/Jet to
ADO/SQL Server has so far been a complete failure.

3. There is no dispute, from Microsoft or anywhere else, that DAO is
more efficient and more powerful for working with Jet databases than
ADO.

4. The built-in DAO reference has been restored to Access 2003.

5. ADO itself is on its way out. ADO.NET is now the strategic
data-access API, and it's significantly different from ADO.

It seems to me that Microsoft's plans for Access are in flux. They've
been attempting to force it into a particular mold, but it hasn't been
going where they want it to go. Their vision for the future has run up
against the real world, and has had to be adjusted. Right now, Access
works equally well as a desktop, file-server database and as a front-end
to a client-server database like SQL Server. A while ago, they gave the
strong impression that they wanted to phase out the Jet, file-server
side completely. Now they seem to have come to terms with the fact that
not all databases need to be client-server, that both types have their
place. It remains to be seen where they're going to go with it in the
coming years, whether they'll drop one side or the other. But for now,
Jet is back in the Microsoft worldview.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Dirk,
Well, that sounds encouraging for keeping me
employed! I've been groaning at the idea of needing to
learn ADO & ADP after finally getting to the point where
I'm getting really proficient with DAO and Jet!
Of course, I'll still have to learn to support both,
but this input certainly reduces the pressure to learn it
all RIGHT NOW. I can rest a little easier now. : )


Thanks for the up-to-date feedback,
Joel