Converting from ADP to MDB/ODBC linked tables

[Reese Watt]

Hello all,

Does anybody have any experience converting an ADP file to an MDB file using
linked SQL tables via ODBC?
(By the way, is linking via ODBC the same as using a pass-through query?)

For example, assume I took these steps:
1) Create new mdb file
2) Import Form1 into the newly created mdb file from my existing adp file,
which was bound to View1 (in SQL server)
3) Link to View1 via ODBC
4) Bind the Form1 in the new mdb file to the linked View1

Would my Form1 need to be modified to work under the new structure?
How difficult would it be to modify it?

Thanks,

Reese

 

[Baz]

I have no experience of this although I fear it is something lurking in my
future too!

However, I can tell you that linking tables using ODBC is not the same as
using passthrough queries. A linked table "looks" to Access as though it is
an Access table, so working with linked ODBC tables is pretty much the same
as working with a linked mdb back-end (OK, that's a bit of an
oversimplification, there are various pitfalls to beware of, but a bit of
Googling will probably put you wise to most of these). Basically you use
Access queries and, via ODBC, these get transparently translated into the
SQL syntax of your database engine (in your case, SQL Server). Where
passthough queries come in is that, occasionally, some aspect of of an
Access query cannot be rendered into a query which can be executed by the
server; in this case, Access (Jet) will retrieve lots of data from the
server in order that the Access query can be executed locally by Access.
This is obviously A Bad Thing performance-wise, and one possible solution is
to create a passthrough query, which is a query written directly in the
native SQL of the server and which Access/ODBC will simply "pass through" to
the server untouched, for the server to execute it and return a result set.

 

[Guest]

ODBC is not the same as pass-through.

ODBC is a restricted SQL, similar to SQL Server, but
much more restricted. In particular, ODBC SQL can't
have more than 1 outer join in a query.

Access/MDB/Jet has a SQL engine which translates
Jet SQL into ODBC SQL. Because ODBC SQL is
so restricted, sometimes the Jet SQL has to be broken up
into bits to meet the ODBC specification.

Because you use a view, perhaps your external SQL
is very simple, and there may not be much difference
between the SQL Server SQL used to call the view,
and the ODBC SQL used to call the view.

However, Access/Jet/mdb does not have good support for
parameterised views, so even if your pass-through query
looks exactly like an ODBC SQL query, it may be difficult
to represent it or use it as an Access/Jet/Mdb query with
an Access/mdb form.

ADO/OLEDB does not have a standard SQL, all queries
are pass-through, so there is no efficiency problem with
multiple outer joins. Also, ADO/OLEDB has better support
for parameterised queries.


The amount of difficulty you have depends on the complexity
of the problem. It might be easy.

(david)

 

[Vadim Rapp]

Hello Reese,
You wrote on Fri, 27 Jul 2007 10:09:59 -0500:

RW> Does anybody have any experience converting an ADP file to an MDB file
RW> using linked SQL tables via ODBC?
RW> (By the way, is linking via ODBC the same as using a pass-through
RW> query?)

RW> For example, assume I took these steps:
RW> 1) Create new mdb file
RW> 2) Import Form1 into the newly created mdb file from my existing adp
RW> file, which was bound to View1 (in SQL server)
RW> 3) Link to View1 via ODBC
RW> 4) Bind the Form1 in the new mdb file to the linked View1

RW> Would my Form1 need to be modified to work under the new structure?

If your form has some vba code, you will have to review it, and probably
rewrite. Without that, you will have to identify the primary key for the
view when linking, after that it will work.

Vadim Rapp

 

[Reese Watt]

Thanks for the responses.

I don't like what I read about the limits of ODBC queries. I think that
would be a deal-breaker for me as far as converting to and MDB/ODBC
combination.

 

[Baz]

It shouldn't be. ODBC linked tables worked fine for years before ADP's and
they still do (and it's a lot less buggy than ADP's). I have used them to
successfully build huge multi-user systems with hundreds of server objects
and hundreds of Access objects. There is always the option of using
passthrough queries for the (quite rare) occasions when you encounter some
limitation of ODBC linked tables; or, you can build a server view and link
to that.

If you abandon this approach without seriously trying it you are really
shooting yourself in the foot: either you battle on with ADP's and just pray
that MS doesn't pull the rug out from under you, or you change to VB.Net or
Delphi and increase your development costs by several multiples at least..

 

[DAVID]

All the ODBC stuff was only background.

It might actually be just easy.


(david)

 

[Reese Watt]

Baz,

Ok. Thanks for the rebuttal. Maybe I overreacted. I'll give it serious
consideration if I do actually come to the point where I need to convert.

I don't consider ADP's to be buggy, by the way. For me, it's been great.

Reese

 

[Baz]

Hi,

Wasn't meant to be a rebuttal, just some advice. I'd hate to see someone
abandon Access when it's still perfectly usable.

Don't get me wrong, I don't dislike ADP's, I've done some serious
applications using them. Bugginess is, I suppose, a relative concept. A
search of Google Groups will reveal lots of discussions about the many
problems with ADP's and the relative merits (and demerits) of ADP's and ODBC
linked tables. Just ignore anything from that idiot Kempf.