If you are using linked tables - then this means odbc.  You cannot use ado
to create a linked table that would somehow not involve odbc.  Is that what
you were asking?
As to writing code, it very much depends on what sort of thing you are
trying to do.  Perhaps you could say what you would like to do and what
advantages you would hope to see over using linked tables?  Are you looking
at bound/unbound forms and does your application make use of stored
procedures.
If you are simply looking for an alternative to linked odbc tables, then you
could try creating an Access project (.adp format).  This connects directly
to the SQL Server tables and is more recent technology than odbc - although
I do not recommend them myself.

Personally speaking, I find adp's most useful when there is not much of a
front end - in other words, no complex forms, reports, modules etc.  They
allow a user with Access to easily change server objects such as tables,
stored procedures, etc.  The problems come in building finished applications
for end users who will never need to see any of the tables, etc.  The
problems are due to the differences in the way forms work within an adp
(which take time to learn if you are used to mdb's) coupled with the fact
that some of these features don't (or certainly didn't) work correctly.  I
can't remember the details of these irritations/bugs/features but when I
found I couldn't quickly get to develop working applications with them I
gave up.  Perhaps some people have persevered but I know many others also
lost patience - especially as some of the advertised benefits (such as the
lack of dependence on JET) were not really true.

The DAO/ADO debate has been written about an awful lot.  If you are using an
all-Access solution then you only need to know DAO and ADO brings very
little new and lacks a number of features.  If you are using SQL Server,
then probably it is handy to know both.  Although, with DAO and stored
procedures, you could achieve an awful lot without needing ADO.

My basic strategy is to use unbound forms very sparingly.  Bound forms do,
and always have, work very well with Access and I would have to be shown the
benefits before I would consider ignoring this feature.