Hi Everyone,

Just wanting some feedback or ideas on approaching a problem. What I
would like to do is to migrate an Access FE away from and Access BE
(MDB), and instead allow a user to specify which BE server type they
are using, provide their settings, then create the connections as
needed.

The FE would contain the SQL specific syntax for queries etc for the
different BE possibilities, then depending on the users chosen BE use
the appropriate syntax for the connection. It would also contain the
necessary SQl scripts to create the data structures in the various
BE's.

The reason I want to do this is to avoid a problem that will be coming
up later this year when a data warehouse will be 'upgraded', but I
dont know to what! It could be any from a series of possibilities,
currently using Oracle 9i.

I was thinking to have the app check for the installation of
appropriate drivers for the different DB's and also allowing for the
use of an ODBC connection (though this is not the preferred method).
If the correct driver is installed then the app can accept the
settings given by the user, store them, and off we go (so to speak).

I have not attempted a solution quite like this before, similar, but
not the same. I am wondering if anyone has done anything like this or
might have some advice to offer before I start pouring time into it.

Any thoughts are greatly appreciated.

Cheers

The Frog

On Tue, 2 Mar 2010 07:09:15 -0800 (PST), The Frog
<mr.frog.to.you (AT) googlemail (DOT) com> wrote:

That seems like a lot of work for little good. I can't think of a
single company that would invest in writing passthrough queries for
say 5 different sql flavors, and once that work is done they'll say
"we chose X" and 4/5th of your work would be useless.
Rather I would think management would want to first make a decision,
then let you run with it.
Are you talking to the right people?

-Tom.
Microsoft Access MVP

Hi Tom,

Unfortunately I am talking to the 'right' people. The decision makers
are not really technology proficient, and as such have a 'demand' that
whatever system they decide to switch to it must be 'instantly'
operational. I would have to agree with you that the approach is
rubbish, but thats what happens when you let accountants make business
and technology decisions they are ill qualified to make. Unfortunately
I have no say in the matter - except perhaps to say that the required
changes are not possible until the target db system is known. I dont
think they will buy that however as they have it on 'expert advice'
that any application can be made to work on any platform. Though to a
certain extent this may be true they dont realise the amount of work
required and are not interested in listening.

I just thought I'd ask to cover my bases, but it seems that it is as I
suspected, and exactly as you have pointed out. At this time the app
is working quite happily in Access and I think it might just be time
to say 'no' to this and put some other things on the priority list. If
it 'aint broke' then dont fix it........

Thanks Tom

Cheers

The Frog

The Frog wrote:
What Tom said, it sounds like a lot of work. Could you use something
like Tony Toews AutoFe to open the correct DB? The opening form asks
what FE to use and then it AutoFEs the correct file to start by using
something like ShellExecute to launch the correct desktop icon?

Or store in a table the SQL statement based on BE selected and on
opening do something like a relink after finding which BE to use where
you enumerate thru the existing queries, search for the query in the
table and if found, qdf.SQL = rst!SQL. I guess you'd have to check
form/report recordsets as well.

On Wed, 3 Mar 2010 00:14:49 -0800 (PST), The Frog
<mr.frog.to.you (AT) googlemail (DOT) com> wrote:

Of course, if performance is not important to you, you can use ODBC
attached tables and Access queries. That will indeed more-or-less work
instantly on any data source.
I say more or less because some ODBC drivers are better than others.
We regularly have posts in the newsgroup asking why some things don't
work.
Performance will be eh, sub-optimal, without passthrough queries, but
perhaps it makes sense to limp along and then quickly focus on the
slowest parts when the platform has been chosen.

-Tom.
Microsoft Access MVP

Thanks for the feedback guys, I appreciate it.

I was thinking along similar lines to Salads suggestion, but I fear
that it might have to go the ODBC path, and keep the SQL really simple
doing the more complex processing locally rather than depending on the
BE to handle it properly. Annoying but do-able.

Cheers and thanks

The Frog